DTS package runs fine, but the job fails.

[XopherS]

Details:

- I'm on a WinXP Pro SP2 machine.

- I'm using MS SQL Server 2000 (8.00.2039 - SP4).

- I'm connecting to a MySQL database, which is installed on the same machine as MSSQL.

- I have the MySQL database driver installed (v.3.51).

- The services Messenger and SQLSERVERAGENT are set to Automatic and are running.

- I used the same login to create the package and to run the job. The login is part of the sysadmin group.


Problem:

I've created a package that connects to the MySQL db and moves the data from there to an MSSQL table.  The package runs beautifully when I execute it manually, but it fails when I schedule it to run regularly.

When I view the job history and check "Show step details", I see this error message:

Executed as user: PRGMSERVER1\SYSTEM.
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Copy Data from Results to [WebImports].[dbo].[Customers] Step
DTSRun OnError: Copy Data from Results to [WebImports].[dbo].[Customers] 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 OnFinish: Copy Data from Results to [WebImports].[dbo].[Customers] Step
DTSRun: Package execution complete. Process Exit Code 1. The step failed.

So:

a) What the hell?

b) Why is it saying it's running as user SYSTEM, when I used a different user to set it up and run it?

 

[philhege]

Jobs run in the context of the SQL Server Agent. Make sure the agent's account has all the necesary rights.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.