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.
Loading Comments ...
Comments
No comments have been added for this post.
You must be logged in to make a comment.