Scheduled DTS Package fails

[multiplex77]

Hi,

I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.

However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."

Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!

 

[multiplex77]

I found out that it's probably due to the Package Scheduling and Security Issues of SQL Server Agent. It seems SQL Server Agent doesn't have permissions to access that package. BUT I can't figure out how to make my package accessible so the Agent can access it. I haven't given it any special owner or user passwords.

I've read thru the article at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_pkgmng_sec_09dl.asp

...many times but it doesn't give me a solution. Could someone help me please? Would really appreciate it.

Thanks.

 

[multiplex77]

Okay, nevermind. I've solved the problem:

It was from the KB article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q269074

"Frequently, a developer creates and tests the DTS package interactively on their workstation through the DTS Designer in Enterprise Manager. After the DTS package is debugged, the package is then scheduled as a job. This changes the location of the package from the developer's workstation to the server. If the package was loading text data into SQL Server, the package fails unless the text file and the path to the file exist on the server. If the package was connecting to another server, the package fails if the security context of the job does not support the connection."

So what I did to fix my problem was to share the folder of the database I was importing from, and that did the trick.