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: