Execute a SQL*Server 2000 DTS from a remote access

Question

Execute a SQL*Server 2000 DTS from a remote access

chaumeil

Newbie

Points: 3
More actions
January 10, 2007 at 4:02 am

#171771

I work on an application witch use 2 server :
* A : Sql*Server 2000 with our database and DTS package
* B : IIS (ASP and VBScript)
I need to execute a DTS package (with an ODBC connection) from a screen (with a VB script)
I use the dtsrun command but it doesn't work :
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
How can i solve my problem ?
* Create the ODBC connection on the 2 server ?
* Use some tools (with VB command) to execute my DTS from a remote access ?
* Something else ?
Thanks for your help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

On whatever computer you are running dtsrun, you will need to have the DSN. The package knows that the connection is identified with the DSN X. DTSRun is going to look on the computer it is running on to find that DSN.

Russel Loski, MCSE Business Intelligence, Data Platform

chaumeil Newbie Points: 3 More actions · Answer 2

Thanks Russel,

In fact, I investigate more and my requirements not allows me to install DSN on my main computer.

I think I should find a way to launch my script on the Server A and that the DTS will execute on the Server B.

chaumeil Newbie Points: 3 More actions · Answer 3

I found the solution.

I created a non scheduled job for my DTS Package.

Then I created a Stored procedure witch launch the job and scan the executon status (with msdb sp) and the job name in parameter.

On the main server, i wrote a VB script witch launch the job with the right job name.

It's work fine.

Thanks