T-SQL/ Query Analyser to Execute DTS Package

[2122002]

Dear Expert,

I have created a DTS package that export data through ODBC (DNS, with user name and password ) connection from customer server to my SQL Server 2000 machine.

Source: ODBC Connection (DNS) with username and password permanently saved.

Destination: SQL Server (local)

This works fine whenever I run the DTS manually through Data Transfrmation Services. However, when I schedule it to be running, I get below errors message...
------------
Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0)
---------------------

I need to load base from ODBC to MySQL Server, Can someone please help with programmatically connection parameters, login and password of connection,

for example: DNS/ODBC user name = ABC, password = 123.

Thank you for looking into this.


Dan

 

[AlexCuse]

It sounds like your SQL Server Agent account cannot find your DSN file. Where do you have it saved?

Good Luck,

alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[2122002]

I have it saved on my local machine.

Actually, it was installed under:

Start->Admin Tools > ODBC Data Source, then UserDNS.

SQL Server is runninig in different machine (SQL_SERVER).

Please help.

Thanx.



Dan

 

[AlexCuse]

Well, to start install the DSN on your server (or use a DSN-less connection)

If you don't have access to the server, email the DSN to someone who does and have them set it up.

I don't really work with DSN's that much except for in Access, sorry I can't be of more help.

What type of server are you trying to connect to? Perhaps a DSN-less connection would be desirable, since there is one less place for something to go wrong?

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[djj55]

If the task is running on a different machine that machine will need its own dns setup.