May 12, 2005 at 6:49 am
I have created a dts package that copies a few tables from a Pervasive2000 database on a different server and brings the data into sql server.
It runs fine if I execute it manually, but fails when scheduled.
The error log shows the following error:
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
Thanks for any info that might help me troubleshoot this problem.
Paul
May 12, 2005 at 9:56 am
Paul,
I assume you're running the package manually from your workstation where you have the correct ODBC DSN defined. When you schedule a package, it runs on the database server which probably doesn't have the correct ODBC DSN. Add the DSN on the server and you should be fine.
Other things to be aware of when scheduling DTS packages:
Scheduled jobs run under the security context of the SQL Server Agent login account. If DTS is importing or exporting from another server, make sure the SQL Server Agent login is a domain account and that it has permissions to access the other server.
Use UNC file paths rather than mapped drives.
Greg
Greg
May 14, 2005 at 5:01 pm
you might also want to check, if your DSN is on the server, that it is configured as a System DSN rather than a User DSN, to ensure that the SQL Agent login has access to it.
May 16, 2005 at 7:25 am
May 16, 2005 at 1:21 pm
yes... here's another summary.
http://www.7hertz.com/archives/2005/01/dts_sql_agent_ownership_problem.html
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply