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

Code

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]

ELSE

  [do something else]

END IF

'---------------------------

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

    .Open

    'process the stored procedure command with no records to return

    Set cmd = .Execute(StProc, , adExecuteNoRecords)

  End With

  cnn.Close

  Set cnn = Nothing

  Set cmd = Nothing

  'if successful return true

  RunPac = True

  Exit Function

Show_Err:

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

  'if it fails return false

  RunPac = False

  cnn.Close

  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

Categories

Visual Basic 6

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.