Executing DTS Packages in Visual Basic

After you have created the necessary Data Transformation Services (DTS) objects, set their properties and added them to the appropriate collections, use the Execute method of the Package2 object to run the package. For more information about handling errors raised by the Execute method, see DTS Error Handlers in Visual Basic.

If the Package2 object is to be used again (for example, for saving or running), or if the application is to perform significant processing outside of DTS after the DTS package is run, it is recommended that you call the Package2 UnInitialize method. UnInitialize performs various clean-ups, for example, re-initializing global variables, closing user-opened connections, closing the log, releasing threads, and terminating event connection points.

Before calling UnInitialize, it is strongly recommended that you release references to all DTS objects, except the Package2 object, through which you are going to run Uninitialize. This includes additional Package or Package2 object variables that you have declared WithEvents in order to handle package events.

You can release references by either setting the appropriate object variables to Nothing or arranging your code so that they go out of scope. If you fail to do this, resources such as computer memory will not be released by Uninitialize, giving the appearance of a memory leak.

Example

The following code example shows a DTS package using an ExecutePackageTask, through the Execute and UnInitialize methods:

Private WithEvents mobjPkgEvents As DTS.Package
. . .
Private Sub RunPackage()
'Run the package stored in file C:\DTS_UE\TestPkg\VarPubsFields.dts.
Dim objPackage      As DTS.Package2
Dim objStep         As DTS.Step
Dim objTask         As DTS.Task
Dim objExecPkg      As DTS.ExecutePackageTask

On Error GoTo PackageError
Set objPackage = New DTS.Package
Set mobjPkgEvents = objPackage
objPackage.FailOnError = True

'Create the step and task. Specify the package to be run, and link the step to the task.
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSExecutePackageTask")
Set objExecPkg = objTask.CustomTask
With objExecPkg
    .PackagePassword = "user"
    .FileName = "C:\DTS_UE\TestPkg\VarPubsFields.dts"
    .Name = "ExecPkgTask"
End With
With objStep
    .TaskName = objExecPkg.Name
    .Name = "ExecPkgStep"
    .ExecuteInMainThread = True
End With
objPackage.Steps.Add objStep
objPackage.Tasks.Add objTask

'Run the package and release references.
objPackage.Execute

Set objExecPkg = Nothing
Set objTask = Nothing
Set objStep = Nothing
Set mobjPkgEvents = Nothing

objPackage.UnInitialize
End Sub