DTS job fails when scheduled - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS job fails when scheduled

Author

Topic

ws5926
Yak Posting Veteran

88 Posts

Posted - 2005-05-04 : 11:37:54

I have a DTS job setup that runs fine when you right click on it and the click Execute, but when I schedule the job, it fails with the following output:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_2
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_2
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, 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 OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.

Thanks in advance for any responses to this post.

Will

Live to Throw
Throw to Live

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-04 : 15:23:48

Well you are referring to something that doesn't exist on the database server. You've probably been running the DTS package from your client machine and assumed that it would work when run from the database server. You should always test on the actual database server before scheduling it as a job. To test, you'll need to log into the database server using the account that the MSSQLSERVER service logs on with as this is the account that is used when a job is run via the scheduler. Once logged in, execute the package via Enterprise Manager, do not execute the job. You should be able to replicate your problem this way.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources