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.