Execute a package from Visual Basic (VB)

By Darren Green

Page 1 | Page 2 | Page 3

One of the benefits of using Visual Basic in this way to execute your packages is the control over the package it allows. You do anything from building an entire package to changing a filename all at run-time from within the application.

A simple example of this is allowing a user to supply a value which can be used to update a package global variable prior to calling the execute e method:

    ' Set Global Variable
    oPKG.GlobalVariables("MyGlobalVariable").Value = txtGlobalVariable.Text

Another common requirement is to change the filename of an exported file:

    ' Set Filename
    Set oConn = oPKG.Connections("TextFile")
    oConn.DataSource = txtFilename.Text
    Set oConn = Nothing

This final example allows the user to filter the rows exported via a DataPump task. The re-builds the SQL statement each time using the supplied value:

    ' Set Filter for SQL Statement
    Set oDataPump = oPKG.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
    sSQL = "SELECT * FROM Orders WHERE OrderDate >= '" & txtFilter.Text& "'"
    oDataPump.SourceSQLStatement = sSQL
    Set oDataPump = Nothing

Another very powerful method of capturing error information is to use the package event handlers. This also gives you greater control when executing a the package from a client application such as progress information and the ability to cancel execution part way through. These are documented in INF: How To Install DTS Event Handlers In Visual Basic (221193), and are included in the sample application available for download.

The download to accompany this article contains the source for a sample VB application that demonstrates all methods shown here. Also included is a sample package required for the dynamic examples. The package has a password of "sqldts". This ensures it is compatible across all versions of SQL Server and all service packs levels.

Before using the sample save the package as a local server package (without the password). In addition you will need to replace all occurrences of "MyServer", "MyUsername" and "MyPassword" in the Visual Basic source with appropriate values.

Page 1 | Page 2 | Page 3