SQL Server Performance Forum – Threads Archive

DTS job fails when scheduled

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

I believe when you right click on a DTS package and click execute the package runs on your workstation. When you schedule it it runs on the server. So perhaps on your workstation you have the source defined (via Client Network Utility) but the server isn’t aware of the name used by the source. Either change the source to an IP address or define the name using Server Network Utility.

I have my local server, the destination server setup to use the Local System Account to log into windows. The DTS package uses an anonymous connection to log into the source server, which doesn’t require a password. The SQL Agent wasn’t using the Local System Account. I changed it to the Local System Account and rebooted the server and it works now. Thanks for the replies.
Live to Throw
Throw to Live

KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;269074&sd=tech for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>