DTS package fails when scheduled as a Job. 1

[shu]

Hi, thanks for taking the time to view my post.

I am using SQL SVR 2000 and I have built a DTS package which connects to an Access DB residing on another server and imports the data into a SQL table.

- First, on the SQL machine, I went thru the ODBC Data Source Admin to create a System DSN using the MS Access Driver and linked to my .mdb file. Fine.

- Then I created a DTS package that connects to that access db dsn using the 'Other (ODBC Data Source)' connection and set another connection which connects to the SQL Svr db. I transformed the data from the access db to my sql table. Easy.

Oh yah, I have sys admin rights on the SQL machine.

Now, when I'm on the SQL machine and manually execute the DTS package in SQL Svr, it runs fine. BUT when I try and schedule it as a job, it fails. WHY?!?!

Job history gives me this error:
------------
Executed as user: DOMAIN\SQL_ADMIN. ...t: 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 Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1023 (3FF) Error string: [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSR... Process Exit Code 1. The step failed.
------------

I just don't get it. Why would it work when I manually execute it but not as a job? I don't know why I get the, "[Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path.", I mean it must not be unknown when execute it manually.

Any help would be greatly appreciated!
Thx in adv.
- Shu

 

[mrdenny]

The package must not be reading the DSN correctly. Instead of using a DSN setup a DTS connection object to go directly to the Access database.

Also be sure that the DOMAIN\SQL_ADMIN account has access to the folder that the Access file is in.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[shu745]

thx mrdenny. How would I go about doing that?

- Shu

 

[shu745]

NM.. Dumb question.. I got it.
Thanks MrDenny.