DTS package in runtime

[Pipe2Path]

I have my VB app running a DTS package to load some data.

When I compile the VB app, and run it on a client workstation, the app errors out saying that it can't create
an ActiveX object.

I tried registering the dtspkg.dll on the client's machine, but regsvr32 won't work on this file.

I guess my question is, how do you register a DTS package in runtime, or do you even need to.

Thanks.

 

[paulbent]

You need to install the SQL Server client components on the workstations in order to install and register the Microsoft DTSPackage Object Library which I'm guessing your project is referencing.

An alternative is to use the xp_cmdshell stored procedure:

strShell = "dtsrun /S ServerName /U UserID /P Password /N PackageName"
strSQL = "{CALL xp_cmdshell('" & strShell & "', 'no_output')}"
cnSQLServer.Execute strSQL, , adCmdText + adExecuteNoRecords

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[Pipe2Path]

Paul,

Thanks for the response. Is xp_cmdshell a system stored proc in SQL server?

 

[paulbent]

Yes it's a system proc in the General Extended Procedure category in both SQL Server 7 & 2000; I don't know about 6.

Look it up in BOL for more info about the permissions needed by the user.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[Pipe2Path]

Hi Paul,

I tried executing the package like you said, but the user
who wouldn't ordinarily have the SQL client components
installed on her machine, cannot run "dtsrun" from her
machine. I get a "File not found" error. I checked her machine, and it didn't have the dtsrun.exe.

Have you had any success running this at all? Could you let me know what you did?

Thanks

Kevin

 

[paulbent]

Dtsrun is on the server. The code snippet I posted is using an ADO connection to execute the stored procedure. The stored procedure is shelling dtsrun with the specified command line. Of course, it may be as much effort to distribute the ADO dependencies to the workstations as the SQL Server client components!

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk