Stand-Alone DTS Designer on SQL Server 2000

This article describes how to open the DTS Designer for SQL Server 2000 programmatically. It eliminates the need to open the SQL Server Enterprise Manager if you just need to edit a DTS that was saved as a structured storage file. In this article I'm building a command line utility in VB.NET that takes the full path to a dts package as a parameter and opens it in the designer. You can find its VB.NET code here.

I recently started working on a project involving the creation and modification of DTS packages that were scattered around on many different systems and file shares. What really annoyed me was the fact that you are forced to go via the SQL Server Enterprise Manager and connect to a random SQL Server, even if you wanted to open a DTS package that was saved to the file system as a structured storage file.

After some searching on the internet for a DTS API, I came across the MSDN article on Creating DTS Packages in Visual Basic. No explanation however was given on how to open the designer programmatically although the library (dtspck.dll) included an object called CDTSLegacyDesigner, which clearly was the object I was after.

The following VB.NET code shows how to load the designer with a empty design surface. You will have to add a reference to the COM component dtspck.dll first, which is installed together with the SQL Server 2000 Client Tools (such as Enterprise Manager, Query Analyzer...)

Try
  designer = New DTS.CDTSLegacyDesigner
  designer.Initialize()
  designer.ShowDesigner()
Finally
  If Not designer Is Nothing Then
    designer.Dispose()
    Marshal.ReleaseComObject(designer)
    designer = Nothing
  End If
End Try

DTS packages are saved as a structured storage file. A single file can contain multiple DTS packages and multiple package versions. The following simplified code shows how to retrieve the necessary information about last package version stored in the DTS. You should loop over the packageInfoColl collection and allow users to select the version they wish to open.

Dim sPackageName As String  
Dim sPackageID As String  
Dim sPackageVersionID As String  
Dim sPackagePathName As String 'Full pathname to DTS package   
 
Try    
  Dim package As DTS.Package = Nothing  
  Dim packageInfoColl As DTS.SavedPackageInfos = Nothing  
  Dim packageInfo As DTS.SavedPackageInfo = Nothing  
  package = New DTS.Package 
  packageInfoColl = package.GetSavedPackageInfos(sPackagePathName) 
 
  '** Example: select the last saved version 
  packageInfo = packageInfoColl.Item(packageInfoColl.Count) 
  sPackageName = packageInfo.PackageName 
  sPackageID = packageInfo.PackageID 
  sPackageVersionID = packageInfo.VersionID
 
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 If
End Try

When you have retrieved the packageName, packageID and packageVersion you can pass these as properties to your instance of the CDTSLegacyDesigner class as shown in the following code. The location property indicates that the DTS package should be loaded from the file system.

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

Putting these code snippets together into a functional application still takes some work but you can find my attempt on my googlepage.