DTS failing as scheduled job but runs directly

[NickyJay]

Hi All,

Please can someone advise, I have a searched the net and these posts and have nothing that helps!
I have a DTS package saved as SSIS on MSSQL 2005 and have run it successfully. The SQL statment basically extracts just 10 or so fields from a much larger table on another server. It then inserts them to another database just for us to be able to connect via web - our intranet system.

Running the DTS package directly, I am able to carry out the above tasks, however, a scheduled job i created to run this export/import nightly fails everytime with no specific error - i have checked the logs and it just says it failed???

Any ideas? The DTS has been created using sa as user, and the scheduled job is owned by sa. Will this be the problem? Any help is appreciated. Please be gentle too, am just returning to MSSQL programming after a year's maternity leave so treat me as a beginner!

Many thanks
Nicola

 

[RiverGuy]

The schedule SQL Agent job will run under a different account than your own account when you run it directly. Are you using Windows Authentication to connect to the other (I assume SQL Server) server? You might switch this to a user name and password.

 

[NickyJay]

Hi, many thanks for replying, this is doing my head!

Im really not sure how to do this - again, bear in mind i've been off work for 11months with a little one and my brain has gone to mush

I have logged into enterprise manager using the SQL Server Authentication - using the sa account - and also tried Windows Authentication using an Administrator account. I am running EM directly on the server SQL Server is on, not through a remote connection or anything. How do i log the Agent in as a different user?

Nicola

 

[RiverGuy]

Wait, I'm a bit confused now, rereading your first post. Is this a DTS package or an SSIS package? And, is the package saved on the source server or the destination server?

 

[NickyJay]

Hiya,

As you can see, i have a lot to learn!

I created a DTS package, saved it as SSIS so i could then run it via scheduled job and this is where i am stuck. Created the dts and job both using the sa account.

Everything is running on the destination server.

Nicola

 

[RiverGuy]

Well, if this is an SSIS package, there could be several things going on. Take a look at this article:

http://support.microsoft.com/kb/918760/