Search Tools Links Login

Remotely Run a SQL Server 7.0 DTS Package from VB

Have you ever wondered how to remotely fire a DTS Package in SQL Server 7 from a Visual Basic Application? Me too... So through rigorous research and aggrevation I figured out a very simple way to do this.

Original Author: Chris Kesler


First: You create your DTS Package in SQL Server to do the job you need it to do.

Second: You create a Stored Procedure similar to the one I have provided:

CREATE PROC sp_SampleShell AS

EXEC master..xp_cmdshell 'C:MSSQL7BINNDTSRun.exe /S [SERVERNAME] /N [DTSNAME] /E'

this will execute the DTS Package via the xp_cmdshell provided by SQL. The DTSRun.exe will be found in your [MSSQL7BINN] directory.

Third: In your VB Program you create an ADO connection to your Database and use the following information in your program:


IF RunPac(sp_SampleShell) = TRUE THEN

  [do something]


  [do something else]



Private Function RunPac(StProc As String) As Boolean

  Dim cnn As ADODB.Connection

  Dim cmd As ADODB.Command

  On Error GoTo Show_Err

  Set cnn = New ADODB.Connection

  Set cmd = New ADODB.Command

  'set our connection constraints

  With cnn

    .ConnectionString = "DATA SOURCE=[DSN]"

    .CursorLocation = adUseClient


    'process the stored procedure command with no records to return

    Set cmd = .Execute(StProc, , adExecuteNoRecords)

  End With


  Set cnn = Nothing

  Set cmd = Nothing

  'if successful return true

  RunPac = True

  Exit Function


  Debug.Print Err.Number & " - " & Err.Description

  'if it fails return false

  RunPac = False


  Set cnn = Nothing

End Function

And voila!!! You've just created a remote process for a DTS Package...

I hope this helps someone else out as well.

A very good point was made that there may be an easier way of doing this using the reference to DTS.dll. I tried using that method and had some issues with my environment so I needed to develop something that didn't care about the development environment. Also, this method is used more for those who not only develop their own VB Applications but also develop their own Stored Procedures as well.

I did not do another search in the past month or so regarding this so if this replicates anyone else I'm sorry, but this information did not exist when I originally needed it.

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 136 times


Visual Basic 6


No attachments for this post

Loading Comments ...


No comments have been added for this post.

You must be logged in to make a comment.