Stand-Alone DTS Designer on SQL Server 2005

This article describes how to open the DTS Designer for SQL Server 2005 programmatically. It eliminates the need to open the SQL Server Management Studio if you just need to edit a DTS that was saved as a structured storage file. I’m building a command line utility in C# 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.

In my previous post, I already created a similar utility in VB.NET that opened the DTS Designer on PCs with SQL Server Enterprise Manager installed, which is the client application for SQL Server 2000. The source code is exactly the same. The only differences are that you'll need to install the DTS Designer Components from the Feature Pack for Microsoft SQL Server 2005 and that your project will reference the Microsoft.SQLServer.Dts80.dll assembly from the GAC

Visual Studio 2005 doesn't allow you to reference this assembly directly from the GAC. If you would browse to the GAC's folder (by default in the %windir%\assembly folder), the Assembly Cache Viewer (SHFusion.dll) shell extension will be actived which doesn't have an option to copy assemblies. You could disable this shell extension but it is just as easy to copy the assembly to a diffent folder using the command prompt:

1. Click Start
2. Click Run...
3. Type 'cmd.exe' and press <enter>. The command prompt appears
4. Type the following command:

copy %windir%\assembly\GAC_MSIL\Microsoft.SqlServer.Dts80\9.0.242.0__89845dcd8080cc91\Microsoft.SqlServer.Dts80.dll c:\data\

This will put a copy of the Microsoft.SqlServer.Dts80.dll to c:\data\ from where you can reference it in Visual Studio 2005. As soon as the project is finished and compiles you don't need this file anymore as your executable will use the one in the GAC.

In order to get started, open Visual Studio and create a new Windows application in C#. Put a reference to the Microsoft.SqlServer.Dts80.dll that you have copied to c:\data\

Create a ShowPkgDesigner function that takes the full pathname of an existing DTS package:

private static void ShowPkgDesigner(string sPackagePathName)
{
  DTS.CDTSLegacyDesigner designer = null;
  String packageName = String.Empty;
  String packageID = String.Empty;
  String packageVersionID = String.Empty;

  //* Remove quotes from packagePath
  sPackagePathName = sPackagePathName.Replace("\"", "");

  if (sPackagePathName == String.Empty ||
      ShowPkgBrowser(ref packageName, ref packageID,
                     ref packageVersionID, sPackagePathName))
{
  try
  {
    designer = new DTS.CDTSLegacyDesigner();

    designer.Initialize();
    designer.Location = 2;
    designer.PackageID = packageID;
    designer.PackageName = packageName;
    designer.ServerName = sPackagePathName;
    designer.VersionID = packageVersionID;

    //* Launch designer
    designer.ShowDesigner();
  }
  finally
  {
    if (designer != null)
    {
      designer.Dispose();
      designer = null;
    }
  }
}
}

Multiple DTS packages and versions can be safed in one structured storage file. The ShowPckBrowser function allows a user to select a specific version of a DTS package:

private static bool ShowPkgBrowser(ref String sPackageName,
                                  ref String sPackageID,
                                  ref String sPackageVersionID,
                                  String sPackagePathName)
{
 DTS.Package package = null;
 DTS.SavedPackageInfos packageInfoColl = null;
 DTS.SavedPackageInfo packageInfo = null;

 int selectedPackage = 0;

 package = new DTS.Package();
 packageInfoColl = package.GetSavedPackageInfos(sPackagePathName);

 if (packageInfoColl.Count == 1)
   selectedPackage = 1;
 else
 {
   PackageBrowser pckBrowser = new PackageBrowser();
  
   for (int i = 1; i <= packageInfoColl.Count; i++)
   {
     packageInfo = packageInfoColl.Item(i);
     pckBrowser.AddPackageVersion(
     packageInfo.VersionSaveDate.ToString(),
     packageInfo.PackageName);
   }
  
   if (pckBrowser.ShowDialog() == DialogResult.OK)
     selectedPackage = pckBrowser.SelectedPackage;
 }
  
 if (selectedPackage > 0)
 {
   packageInfo = packageInfoColl.Item(selectedPackage);
   sPackageName = packageInfo.PackageName;
   sPackageID = packageInfo.PackageID;
   sPackageVersionID = packageInfo.VersionID;
  
   return true;
 }
 else
 {
   return false;
 }
}

The code above uses a custom form that only contains a listbox. In the for loop, all versions and package names are added to that listbox. As soon as a user double-clicks on the desired version, the 'PackageBrowser.SelectedPackage' contains the index of that version. This is then used to retrieve the package name, id and versionId. I'll leave it up to the reader to implement the PackageBrowser component.

All that's left to do is to call the ShowPkgDesigner function from the main function. I want my executable to take the full path to the DTS package so that I can link it to "*.dts" files in Windows Explorers' "Open With..." functionality:

static int Main(string[] args)
{
  try
  {
    String packagePath = String.Empty;
    
    // Full path to the DTS package is the first command line option
    if (args.Length > 0)
    {
      if ((new System.IO.FileInfo(args[0])).Exists)
        packagePath = args[0];
      else
      {
        OpenFileDialog openFiledlg = new OpenFileDialog();
        
        openFiledlg.CheckFileExists = true;
        openFiledlg.Filter =
        "DTS Packages(*.dts)*.dtsAll Files(*.*)*.*";
        
        if (openFiledlg.ShowDialog() == DialogResult.OK)
        packagePath = openFiledlg.FileName;
      }
    }
    
    ShowPkgDesigner(packagePath);
    
    return 0;
  }
  
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message, "DTSDesigner90 Error",
    MessageBoxButtons.OK, MessageBoxIcon.Error);
    return -1;
  }
}

If no path is provided to the command line, an empty designer sheet is opened. If an invalid pathname is passed, the user will be prompted to browse to an existing file.

Having this command line utility has really improved my productivity when I was constantly editing and comparing DTS packages that were scattered all over the companies network. Being able to open a package by just double-clicking it is just that little bit more efficient than opening them through the SQL Server Management Studio.

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 function application still takes some work but you can find my try on my googlepage.