Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Execute a package from Visual Basic (VB)
By Darren Green
Page 1 | Page 2 | Page 3

To execute a package from Visual Basic is a relatively simple task using the DTS object model. Before you start using the object model you must add a the appropriate reference to your project. From the Project menu select References and check the "Microsoft DTSPackage Object Library".

Here is a very simple example that uses integrated security to load the package MyPackage and then execute it:

Private Sub SimpleExecutePackage()
	Dim oPKG As New DTS.Package
	oPKG.LoadFromSQLServer "MyServer", , , _
		DTSSQLStgFlag_UseTrustedConnection, , , , "MyPackage"
	oPKG.Execute
	oPKG.UnInitialize
	Set oPKG = Nothing
End Sub

Before going any further the one drawback to using Visual Basic is that it is apartment threaded and DTS is free threaded. This can cause exception access violations. The simple way top overcome this is to ensure all steps execute on the main package thread. To do this set the ExecuteInMainThread property as illustrated below:

    ' Set Exec on Main Thread
    For Each oStep In oPKG.Steps
        oStep.ExecuteInMainThread = True
    Next

Whilst the simple example above will do the job of executing your package, you will probably want some more information about the status of execution and any errors that occurred. The first option is to check the execution result (ExecutionResult property) for each step. If this indicates failure (DTSStepExecResult_Failure) then you can go on to retrieve the full error number, source and description using the GetExecutionErrorInfo method for the step. A full example of this is shown below:

Page 1 | Page 2 | Page 3