DTS fails when scheduled 2

[Will192]

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.


I have reinstalled MDAC 2.8 and also reinstalled the drivers for the other system that I am pulling from.

Thanks in advance for any responses to this post.

Will

 

[JR4VB]

From the descriptions of the error this doesn't seem like it would have anything to do with it but I thought I might mention it....
When right clicking and running a DTS package is using the credentials you are using to make your connection to the database.
When the package runs under a scheduled interval it is using the Account that is setup to run the SQLServerAgent Service.

I don't know what your DTS package does...but make sure that account has security to both source and destination.

JR

 

[JamesLean]

What connections have you got in your package? Remember when you run it manually it will use your local machine resources/drive mappings. When you schedule it, it will use the server's resources.

--James

 

[Will192]

I have my local server, the destination server setup to use the Local System Account to log into windows. I also have SQL Agent use the Local System Account.

The DTS package uses an anonymous connection to log into the source server, which doesn't require a password.

 

[Will192]

Actually, 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.