How do I call a SQL Server 2000 DTS package in VB.net

Question

How do I call a DTS from a VB.net application?

Answer 1

If you are referring to Data Transformation Services that is part of sql server 2000 (not available in later version as it was replaced with integration services) then there are a few ways.

You can run it from DTSRun if your app is on the server using process.start

process.start("dtsrun /S ""server name"" /U sa /N ""d:\task.dts""")

If it is not on the server you can still useDTSRun but inside a stored procedure that you call using master.dbo.xp_cmdshell, Then call the SP in your code. (Although if you use xp_cmdshell you must be aware of the security risks of opening up).

Alternatively you could look at this article it explains how to do it from vb script but the code could easily be converted.:

http://articles.techrepublic.com.com/5100-10878_11-5164094.html

Answer 2

This is a pretty crude set of classes that should do what you want if you want programmatic control over a DTS package from VB.Net. I make no guarantees over it's correctness or reliability but it should give you enough pointers to get started.

Just add a reference to the DTSPackage Object (COM) Library to your Visual Studio project

Option Explicit On

Option Strict On

Imports DTS Imports System.Runtime.InteropServices

Namespace Test Public Class DTSRunner Private cpPoint As ComTypes.IConnectionPoint Private cpContainer As ComTypes.IConnectionPointContainer Private intCookie As Integer Private PES As PackageEventsSink

 Public Sub RunPackage(ByVal packageName As String)

  Dim pkg As New DTS.PackageClass
  pkg.LoadFromSQLServer("MySQLServer", String.Empty, String.Empty, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, String.Empty, String.Empty, String.Empty, PackageName, System.Reflection.Missing.Value)

  cpContainer = CType(pkg, ComTypes.IConnectionPointContainer)
  cpContainer.FindConnectionPoint(New Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5"), cpPoint)
  PES = New PackageEventsSink
  cpPoint.Advise(PES, intCookie)
  AddHandler PES.OnPackageOnError, AddressOf PackageErroredHandler
  AddHandler PES.OnPackageOnQueryCancel, AddressOf PackageQueryCancelHandler
  pkg.Execute()

  If cpPoint IsNot Nothing Then
   cpPoint.Unadvise(intCookie)
  End If
  If Not pkg Is Nothing Then
   pkg.UnInitialize()
  End If
  If PES IsNot Nothing Then
   RemoveHandler PES.OnPackageOnError, AddressOf PackageErroredHandler
   RemoveHandler PES.OnPackageOnQueryCancel, AddressOf PackageQueryCancelHandler
  End If
  PES = Nothing
  cpContainer = Nothing
  cpPoint = Nothing
  pkg = Nothing
 End Sub

 Private Sub PackageErroredHandler(ByVal sender As Object, ByVal e As DTSEventArgs)
  If Not packageErrored Then
   TraceEvent(TraceEventType.Error, -1, _
   "Error In DTS Package : {1} - {2} - {3} - {4}", _
   .EventSource, e.ErrorCode, e.Source, e.Description)
   packageErrored = True
  End If
  e.Cancel = False
 End Sub

 Private Sub PackageQueryCancelHandler(ByVal sender As Object, ByVal e As DTSEventArgs)
  e.Cancel = False
 End Sub


 Private Class DTSEventArgs
  Inherits EventArgs

  Public EventSource As String
  Public ErrorCode As Integer
  Public Source As String
  Public Description As String
  Public Cancel As Boolean
  Public PercentComplete As Integer = 0

  Public Sub New(ByVal EventSource As String, ByVal Source As String, ByVal Description As String, ByVal ErrorCode As Integer, ByVal Cancel As Boolean)
   Me.EventSource = EventSource
   Me.Source = Source
   Me.Description = Description
   Me.ErrorCode = ErrorCode
   Me.Cancel = Cancel
  End Sub

 End Class

 Private Delegate Sub PackageOnErrorEventHandler(ByVal sender As Object, ByVal e As DTSEventArgs)
 Private Delegate Sub PackageOnFinishEventHandler(ByVal sender As Object, ByVal e As DTSEventArgs)
 Private Delegate Sub PackageOnStartEventHandler(ByVal sender As Object, ByVal e As DTSEventArgs)
 Private Delegate Sub PackageOnProgressEventHandler(ByVal sender As Object, ByVal e As DTSEventArgs)
 Private Delegate Sub PackageOnQueryCancelEventHandler(ByVal sender As Object, ByVal e As DTSEventArgs)

 Private Class PackageEventsSink
  Implements DTS.PackageEvents

  Public Event OnPackageOnError As PackageOnErrorEventHandler
  Public Event OnPackageOnFinish As PackageOnFinishEventHandler
  Public Event OnPackageOnStart As PackageOnStartEventHandler
  Public Event OnPackageOnProgress As PackageOnProgressEventHandler
  Public Event OnPackageOnQueryCancel As PackageOnQueryCancelEventHandler

  Overridable Overloads Sub OnError(ByVal EventSource As String, _
  ByVal ErrorCode As Integer, ByVal Source As String, _
  ByVal Description As String, ByVal HelpFile As String, _
  ByVal HelpContext As Integer, ByVal IDofInterfaceWithError As String, _
  ByRef pbCancel As Boolean) Implements DTS.PackageEvents.OnError

   Dim e As New DTSEventArgs(EventSource, Source, Description, ErrorCode, pbCancel)
   TraceEvent(TraceEventType.Verbose, 0, String.Format(" OnError in {0}; ErrorCode = {1}, Source = {2}, Description = {3}, HelpFile = {4}, HelpContext = {5}, IDofInterfaceWithError = {6}", EventSource, ErrorCode, Source, Description, HelpFile, HelpContext, IDofInterfaceWithError))
   RaiseEvent OnPackageOnError(Me, e)
   pbCancel = e.Cancel

  End Sub

  Overridable Overloads Sub OnFinish(ByVal EventSource As String) Implements DTS.PackageEvents.OnFinish

   Dim e As New DTSEventArgs(EventSource, String.Empty, "Execution Finished", 0, False)
   TraceEvent(TraceEventType.Verbose, 0, String.Format(" OnFinish in {0}", EventSource))
   RaiseEvent OnPackageOnFinish(Me, e)

  End Sub

  Overridable Overloads Sub OnProgress(ByVal EventSource As String, _
  ByVal ProgressDescription As String, ByVal PercentComplete As Integer, _
  ByVal ProgressCountLow As Integer, ByVal ProgressCountHigh As Integer) _
  Implements DTS.PackageEvents.OnProgress

   Dim e As New DTSEventArgs(EventSource, String.Empty, ProgressDescription, 0, False)
   e.PercentComplete = PercentComplete
   TraceEvent(TraceEventType.Verbose, 0, String.Format(" OnProgress in {0}; ProgressDescription = {1}, PercentComplete = {2}, ProgressCountLow = {3}, ProgressCountHigh = {4}", EventSource, ProgressDescription, PercentComplete, ProgressCountLow, ProgressCountHigh))
   RaiseEvent OnPackageOnProgress(Me, e)

  End Sub


  Overridable Overloads Sub OnQueryCancel(ByVal EventSource As String, ByRef pbCancel As Boolean) Implements DTS.PackageEvents.OnQueryCancel

   Dim e As New DTSEventArgs(EventSource, String.Empty, String.Empty, 0, pbCancel)
   TraceEvent(TraceEventType.Verbose, 0, String.Format(" OnQueryCancel in {0}; pbCancel = {1}", EventSource, pbCancel))
   RaiseEvent OnPackageOnQueryCancel(Me, e)
   pbCancel = e.Cancel

  End Sub

  Overridable Overloads Sub OnStart(ByVal EventSource As String) Implements DTS.PackageEvents.OnStart

   Dim e As New DTSEventArgs(EventSource, String.Empty, "Execution Started", 0, False)
   TraceEvent(TraceEventType.Verbose, 0, String.Format(" OnStart in {0}", EventSource))
   RaiseEvent OnPackageOnStart(Me, e)

  End Sub

 End Class

        #End Region

End Class

End Namespace