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.