DTS How to... Execute a DTS Package from...

November 28, 1999

2 How to Execute a DTS Package from...

This article primarily covers execution from Visual Basic. For more information on other methods including SQL see

  • How do you want to start your DTS Package today?
  • DTS Execution Methods

    To execute a package first you need to load the package into an object, then simply use object.Execute method. To use the DTS objects enable the reference to the Microsoft DTSPackage Object Library. Here is an example for a server package.

    Sub Main()
    	Dim oPKG As New DTS.Package
    	oPKG.LoadFromSQLServer "Server", , , 256, , , , "PackageName"
    	oPKG.Execute
    	oPKG.UnInitialize
    	Set oPKG = Nothing
    End Sub
    

    Error checking should also form a part of any execution routine. For this I'd recommend reading the Microsoft Knowledge Base Article:

  • Article ID: Q240221
  • INF: How To Handle Errors in DTS Package and Step Objects
  • http://support.microsoft.com/support/kb/articles/Q240/2/21.asp

    For those that want more control, you can declare the package object WithEvents. This gives you progress and error events as they happen. It also allows you to cancel execution in a similar method to that used by the Enterprise Manager execution interface. For more information about WithEvents and DTS, see the Microsoft Knowledge Base Article:

  • Article ID: Q221193
  • INF: How To Install DTS Event Handlers In Visual Basic
  • http://support.microsoft.com/support/kb/articles/Q221/1/93.asp

    An alternative to using WithEvents, but still getting similar functionality, is to use the Microsoft SQLNamespace Object Library to handle execution. You can see this used in my DTSBrowse program.

    A limitation of executing packages from Visual Basic is the need to ensure all steps execute in the main thread.

    Sub Main()
    	Dim oPKG As New DTS.Package
    	oPKG.LoadFromSQLServer "Server", , , 256, , , , "PackageName"
    
    	' Set ExecuteInMainThread for ALL Steps
    	Dim i As Integer
    	For i = 1 To oPKG.Steps.Count
    		oPKG.Steps(i).ExecuteInMainThread = True
    	Next i
    
    	oPKG.Execute
    	oPKG.UnInitialize
    	Set oPKG = Nothing
    End Sub
    








  • The Network for Technology Professionals

    Search:

    About Internet.com

    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers