Stand-Alone DTS Designer

How to launch the SQL Server 2000 DTS Designer from command line without SQL Server Enterprise manager.

Read the article

Option Strict On
Option Explicit On
 
Imports System.Windows.Forms
Imports System.Runtime.InteropServices

Module DTSRunner80
''' <summary> 
''' Launches the DTS Designer of SQL Server 2000 for creating  
''' a new DTS Package or editing an existing one 
''' </summary>
''' <param name="args">Name of the DTS package to edit</param> 
''' <returns>returns 0 in case of success and -1 in case of error</returns> 
''' <remarks>If no parameters are passed, the DTS Designer will show an empty 
''' sheet</remarks>
 
Public Function Main(ByVal args As String()) As Integer 
 
 Try
   Dim packagePath As String = String.Empty
 
 '** The full path to the DTS package should be the first command line option 
 If args.Length > 0 Then 
 If (New IO.FileInfo(args(0))).Exists Then
packagePath = args(0)  
 Else
 Dim openFiledlg As OpenFileDialog = New OpenFileDialog
With openFiledlg
.CheckFileExists = True
.Filter = "DTS Packages(*.dts)|*.dts|All Files(*.*)|*.*"
  End With
 
 If openFiledlg.ShowDialog() = DialogResult.OK Then
packagePath = openFiledlg.FileName
End If
  End If
   End If

ShowPkgDesigner(packagePath)
Return
 
 Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "DTSDesigner80 Error")
Return -1
 
 End Try
 
End Function
 
''' <summary> 
''' Show the DTS Designer window 
''' </summary>
''' <param name="packagePathName">Full path name of the DTS package</param>
''' <remarks>If packagePathName is an empty string, 
''' the DTS Designer will show an empty sheet</remarks> 
Private Sub ShowPkgDesigner(ByVal packagePathName AsString)
Dim designer As DTS.CDTSLegacyDesigner = Nothing 
 Dim packageName AsString = String.Empty
Dim packageID AsString = String.Empty
Dim packageVersionID AsString = String.Empty


'** Remove quotes from packagePath
packagePathName = packagePathName.Replace("""", "")


If sPackagePathName = String.Empty OrElse _
ShowPkgBrowser(packageName, packageID, packageVersionID, packagePathName) Then 
 
   Try
designer = New DTS.CDTSLegacyDesigner
With designer
.Initialize()
.Location = 2
.PackageID = packageID
.PackageName = packageName
.ServerName = packagePathName
.VersionID = packageVersionID
End With
 
   '** Launch designer
designer.ShowDesigner()


Finally 
 If Not designer Is Nothing Then
designer.Dispose()
Marshal.ReleaseComObject(designer)
designer = Nothing 
 End If
   End Try
  End If 
End Sub 
 
''' <summary>
''' Show the Package browser window that allows to select the specific version 
''' of the DTS stored inside a single file
''' </summary>
''' <param name="sPackageName">Returns the selected package name</param>
''' <param name="sPackageID">Returns the selected package ID</param> 
''' <param name="sPackageVersionID">Returns the selected Version ID</param>
''' <param name="sPackagePathName">Full path name of the DTS package</param> 
''' <returns>True if a package version was selected. 
''' False if the selection was cancelled</returns> 
''' <remarks></remarks> 
Private Function ShowPkgBrowser(ByRef sPackageName As String, _
 ByRef sPackageID As String, _
 ByRef sPackageVersionID As String, _
 ByVal sPackagePathName As String) As Boolean
 
 Dim package As DTS.Package = Nothing
 Dim packageInfoColl As DTS.SavedPackageInfos = Nothing
 Dim packageInfo As DTS.SavedPackageInfo = Nothing 
 Dim selectedPackage As Integer = 0 
 
 Try
package = New DTS.Package
packageInfoColl = package.GetSavedPackageInfos(sPackagePathName)
 If packageInfoColl.Count = 1 Then
selectedPackage = 1
  Else 
 'PackageBrowser is a custom form with a listbox to select a DTS version
 Dim pckBrowser As New PackageBrowser

For i As Integer = 1 To packageInfoColl.Count
packageInfo = packageInfoColl.Item(i)
pckBrowser.AddPackageVersion(packageInfo.VersionSaveDate.ToString, _
 packageInfo.PackageName)
Next 
 
 If pckBrowser.ShowDialog() = DialogResult.OK Then
selectedPackage = pckBrowser.SelectedPackage
End If
   End If
 
   If selectedPackage > 0 Then
packageInfo = packageInfoColl.Item(selectedPackage)
sPackageName = packageInfo.PackageName
sPackageID = packageInfo.PackageID
sPackageVersionID = packageInfo.VersionID
Return True 
  Else  
 Return False
  End If
 
   Finally 
 If Not package Is Nothing Then Marshal.ReleaseComObject(package)
If Not packageInfo Is Nothing Then Marshal.ReleaseComObject(packageInfo)
If Not packageInfoColl Is Nothing Then Marshal.ReleaseComObject(packageInfoColl) 
 End Try
 
 End Function 
 
End Module