DTS package Fails when run as Scheduled Job

Question

DTS package Fails when run as Scheduled Job

Kevin Mitchell

Old Hand

Points: 304
More actions
April 3, 2006 at 5:23 am

#190060

Hi,
I have a simple DTS package which copies data from an AS400 file to SQLServer table.
This package works fine when run through Enterprise manager. But fails if run through a scheduled job.
When run as a package the following error is returned :
Executed as user: SIGLTD\SV-SQLMailerF. ...: Copy Data from AS101PF to [DataStagingArea].[dbo].[AS101PF] Step DTSRun OnStart: Copy Data from AS110PF to [DataStagingArea].[dbo].[AS110PF] Step DTSRun OnError: Copy Data from AS101PF to [DataStagingArea].[dbo].[AS101PF] Step, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnError: Copy Data from AS110PF to [DataStagingArea].[dbo].[AS110PF] Step, Error = -2147467259 (80004005) Error string: [Microsoft][... Process Exit Code 2. The step failed.

Any ideas on how to solve this problem??
Thanks,
Kevin

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

There are a couple of things that could be causing this problem.

When you run the DTS package using EM, the package runs as the account that you are logged in with. When the package runs as a job, it runs as the SQL Server Agent service account. So, does the service account have the correct rights?

Are you remote controlling the server when you run the DTS package manually or from your workstation? When running from your workstation, you are using the workstation's resources. Try remote controlling the server using your account and run it from there to see what happens.

Are you using a predefined ODBC connection? Is it possible that the SQL Server Agent service account can not see the ODBC connection because it is either on your workstation and not on the server OR it is a user DSN instead of a system DSN?

If you can do it, remote control the server using the SQL Server Agent start up account. That way you can troubleshoot the package how it actually runs from a job.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Kevin Mitchell Old Hand Points: 304 More actions · Answer 2

Kathi,

Thanks for your response. Hopefully I will get round to trying your suggestions next week.