by Chris Kesler (7 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 3.0
Difficulty: Beginner
Date Added: Wed 3rd February 2021
Rating: 
(3 Votes)
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.
VBcodersFirst: 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:\MSSQL7\BINN\DTSRun.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 [MSSQL7\BINN] 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.