How to Schedule DTS package ? 2

[snr]

I have created a DTS package , which runs perfectly , when executed manually. I want to schedule the package , so that it will run daily at specified time.

Please Help .......
Thanks

 

[RickCole]

Right click on the name of the appropriate package from the Local Package’s window and then click on Schedule Package.

You can then set up a scheduled job to run from within SQL Server Agent.

Rick.

 

[snr]

I tried doing both the things , but there is some problem..
Even though I schedule it , it doesn't run ....
What might be the problem ?

 

[RickCole]

Have you checked the job within SQL Server agent to see if the job is enabled.

Also from SQL server Agent right click on the job and onto Start job. If this works successfully it sounds more like a problem on how the schedule running time is set.

Rick.

 

[snr]

No it didn't start when I click Start job too.
In STEP , I have written ...
dtsrun /N package_name

is it OK ? or is there any problem in specifying the run command ?

I will again check the scheduling time , but I think it is OK

 

[RickCole]

Do you have any other scheduled jobs which run without any problems??

I'm assuming you not getting any error messages but try looking at the SQL Server Error log and NT Event log to see if anything is shown there.

One thing a lot of people fall foul of is what account the DTS package runs under. If you execute the DTS package from your PC under your login it will run under that account name. If the DTS package is run from the scheduler it will run under the account the SQL Server Service is running under.

To check this is not causing a problem: Lgon onto the server itself using the SQL Server Service login account. Execute the DTS package from here and see if it is still successful.

Rick.

 

[tlbroadbent]

The DTS package actually runs under the SQL Agent account which can be the same as the SQL Server account. Check faq183-280 and the Microsoft article, "How to Run a DTS Package as a Scheduled Job."

http://support.microsoft.com/support/kb/articles/Q269/0/74.ASP

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[RickCole]

Thanks for the correction Terry I rushed off that reply, somewhat.

The DTS package does indeed run under the SQL Agent account. As Terry mentioned they can be the same and at most sites I'm aware of they are. But it's an important difference and can cause a lot of grief if it's something your not aware of.

On a similar note and if you see this Terry are you aware of anyone actually lay using different accounts for these services. I always have a think about this one whenever I set up a SQL Server and SQL Server Agent and can never think of a reason for them to have different logons.

Rick.

 

[tlbroadbent]

Rick,

We use the same account for SQL Server and the Agent, also. We haven't found a reason to use different accounts though there may be reasons to do so. I mentioned that fact in the prior post just to make sure that SNR looked in the right place for the account because SQL Server and SQL Agent are setup separately. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[snr]

aaaaaaa thanks guys ,
Its running properly .... Great !!!!!!!!!!
But still i am unable to run that package through package schedular. I am running that package jobs from SQL Server Agent . Doesn't mattar, as it is running !

I have one more question ...
I am creating a text file in a particular format from sql server table by this package. But while creating the file , after writing each row in the file , it writes blank spaces ... till end of line . That is increasing the size of file tremendously .
How can I avoid that ?

Thanks

 

[RickCole]

From within the DTS package are you using an SQL Query to transfer the data to the text file?? If so does the last field in the row have trailing spaces?? Again if so use the RTRIM function within the SQL.

Rick.

 

[snr]

No Rick , I am creating a string from the sql query , no no chance of having space after the string ...

Sorry to bother you again but again I faced the problem-
I have moved that package from my local server to production server. On production server when I scheduled the job using Sql Server Agent - JOB
when I click "start job " , whithout doing anything , it says "succeeded (11/1/2001 12:01:00 PM). I think it is not executing ...

 

[RickCole]

Have you tried putting some Send mail tasks within your package so can track progress. I normally have one connected to every task which uses the failure workflow. Might be also useful to have the first step as A Send mail task to say the DTS package is starting at least that way you know it is beginning to execute.

Have you tried defining your string in the query as a fixed length field say char(50) or something like that just to see what effect that has.

Sorry but I've now finished for the day so can't be of any more assistance hope you resolve the problems.

Rick.

 

[snr]

Thanks Rick.
Your assistance was a great help to me.
bye

 

[tlbroadbent]

You can have the Job create a history file. Open the Job. Go to the Steps tab. Open the properties of the first step. Select the Advanced tab. Near the bottom, you'll see Output File:. Enter a file name for a history log. You can do the same for all the steps in the job if there are more than one. The steps after step 1 can append to the history or write to a new file. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[snr]

Good , i did that , and found out the problem .... It was related to owner .....
Good it is running perfectly now.
thanks again !

Is it possible to append the file in the same step , by two different sqls , and delete all the data before starting the step1 ?