Bart's Software Cookbook

Create a bug progress report from a Sharepoint 2003 bug list

2010-08-04T21:06:00.022+02:00

Sharepoint 2003 includes a list template for bug tracking. It provides very basic functionality like keeping bug statuses, description, comments, dates, assignments etc. For every update that is made to a bug report, Sharepoint just adds a new item to the list. It doesn't however include a bug progress report.

In order to create such a report, I will create a new view on the bug report list in Sharepoint in order to expose the necessary information. In MS Access I will created a linked table that allows us to perform queries on the data to put it in the correct format for use in a pivot table. This pivot table and pivot chart will contain the bug progress report.

Sharepoint bug reports: progress details

On the sharepoint bug list, create a new view containing the following fields:

Id
Issue Id
Status
Modified
Current

This will give you the entire history for every Issue but from this information you can't yet determine when a bug changed from one status to another.

MS Access linked tables

In order to create a bug progress report, we need to build a list of status changes for each of the issues. I have created four queries for the different situations that can occur.

The "StatusSequenceHistory" query determines the sequence of Ids of bug reports for each Issue Id. For each change it collects what Id was changed (FromId) and which new Id was created (ToId)

SELECT fr.[Issue Id] AS IssueId,
       fr.Id AS FromId,
       fr.Status AS FromStatus,
       min(to.Id) AS ToId
FROM [Bug Reports: Progress Details] AS fr
LEFT JOIN [Bug Reports: Progress Details] AS [to]
ON fr.[Issue Id]=to.[Issue Id]
WHERE fr.Id < to.Id
  AND to.Current=False
  AND fr.Current=False
GROUP BY fr.[Issue Id], fr.Id, fr.Status
ORDER BY fr.[Issue Id], fr.id;

Not all status will have a ToId and a FromId. The "StatusSequenceNew" query selects bug reports that have just been created but do not have a history yet.

SELECT [Issue Id] AS IssueId, min(Id) AS ToId
FROM [Bug Reports: Progress Details]
GROUP BY [Issue Id]
HAVING Count(Id) = 1;

The "StatusSequenceFirst" query selects the very first bug report that was created.

SELECT [Issue Id] AS IssueId, min(Id) AS ToId
FROM [Bug Reports: Progress Details]
WHERE Current=False
GROUP BY [Issue Id];

The "StatusSequenceLast" query selects the final bug report that was created which in fact is the current version of the bug report

SELECT fr.[Issue Id] AS IssueId,
       max(fr.Id) AS FromId,
       to.Status AS ToStatus,
       to.Id AS ToId
FROM [Bug Reports: Progress Details] AS fr
LEFT JOIN [Bug Reports: Progress Details] AS [to]
ON fr.[Issue Id]=to.[Issue Id]
WHERE fr.Id > to.Id
AND to.Current=True
AND fr.Current=False
GROUP BY fr.[Issue Id], to.id, to.Status
ORDER BY fr.[Issue Id], to.id;

The StatusSequence query

This query joins the complete history together and adds the status for each change

(
SELECT IssueId,FromId, FromStatus, ToId, det.Status as ToStatus
FROM StatusSequenceHistory old
LEFT JOIN [Bug Reports: Progress Details] det
ON old.ToId = det.Id
)
UNION (
SELECT IssueId,FromId, det.Status as FromStatus, ToId, ToStatus
FROM StatusSequenceLast cur
LEFT JOIN [Bug Reports: Progress Details] det
ON cur.FromId = det.Id
)
UNION (
SELECT IssueId, NULL as FromId, NULL as FromStatus, ToId, det.Status as ToStatus
FROM StatusSequenceFirst fir
LEFT JOIN [Bug Reports: Progress Details] det
ON fir.ToId =det.Id
)
UNION (
SELECT IssueId, NULL as FromId, NULL as FromStatus, ToId, det.Status as ToStatus
FROM StatusSequenceNew new
LEFT JOIN [Bug Reports: Progress Details] det
ON new.ToId =det.Id
);

The ProgressReport query

This query adds the number of status changes for each change and for each day

(
SELECT
Int(d.Modified) AS ModifiedDate, s.FromStatus AS Status, -1 * COUNT(s.FromStatus) as Diff
FROM StatusSequence s
RIGHT JOIN [Bug Reports: Progress Details] d
ON s.ToId = d.Id
WHERE s.FromStatus <> ''
GROUP BY Int(d.Modified), s.FromStatus
)
UNION (
SELECT
Int(d.Modified) AS ModifiedDate, s.ToStatus AS Status, 1 * COUNT(s.ToStatus) as Diff
FROM StatusSequence s
LEFT JOIN [Bug Reports: Progress Details] d
ON s.ToId = d.Id
GROUP BY Int(d.Modified), s.ToStatus
);

Microsoft Excel: Bug progress report

In Excel you can now create a link to the MS Access database as an external source. Choose the PivotTable/PivotChart option when connecting. Put the Modified Date in the left column of the PivotTable, the Status in the upper row and the Issue Id as in the data fields. Configure the Ids in order to show as a running total

Windows Activation: No records found for given DNS query.

2010-01-16T08:49:00.010+01:00

I recently installed Windows 7 Enterprise Edition on one of my PC. Eager to get my PC up and running with all software I daily use, I skipped activation. When I tried to activate a few days later, I was greeted by this error message: Searching for this message, you might end up on this Microsoft support page telling you to check you DNS settings. In my case this didn't apply at all.

In the end it turns out that I ot this message because I hadn't entered my product key yet because I skipped activation during setup. To solve the issue, I:

went to "Control Panel\System and Security\System"
clicked "change product key"
entered a valid product key

Windows automatically activated.

Injecting a version number into WiX via TeamBuild

2009-07-11T17:58:00.033+02:00

I have found several ways of injecting a version number into Wix via TeamBuild, but most of them require way too much work. After a few days of experimenting I ended up with this solution. My TFSBuild.proj file contains the Major and Minor versions and a target "GenerateBuildNumber" to generate build and revision numbers. I use these to create a 'Versionnumber' property and add that to the <custompropertiesforbuild> property.

<propertygroup>
  <assemblymajorversion>2</assemblymajorversion>
  <assemblyminorversion>7</assemblyminorversion>
  <custompropertiesforbuild>
    SignAssembly=true;DelaySign=false;AssemblyOriginatorKeyFile=$(PrivateKeyLocation)
  </custompropertiesforbuild>
</propertygroup>

<target name="BuildNumberOverrideTarget" dependsontargets="GenerateBuildNumber">
  <createproperty value="$(AssemblyMajorVersion).$(AssemblyMinorVersion).$(GeneratedBuildNumber).$(GeneratedRevisionNumber)">
    <output taskparameter="Value" propertyname="Versionnumber" />
  </createproperty>

  <!-- Add the dynamically generated Versionnumber to the custom build properties for use in the WIX project -->
  <createproperty value="$(CustomPropertiesForBuild);Versionnumber=$(Versionnumber)">
    <output taskparameter="Value" propertyname="CustomPropertiesForBuild" />
  </createproperty>
</target>

For setting the version number in a WiX project, you need to get the value in the Product.wxs file. My predecessor had defined PRODUCTVERSION to contain a placeholder value "_versionnumber_". Note: The official way would have been to use the <defineconstants> in the WiX project file but in my case, other builds were already depending on the placeholder so I didn't want to change this.

<?define PRODUCTVERSION="_versionnumber_"?>

In my WiX project file I have overriden the "BeforeBuild" target to replace the "_versionnumber_" placeholder with the actual value of $(Versionnumber). I'm using the 'File.Replace' task from the SDC Tasks library.

<Target name="BeforeBuild">
     <microsoft.sdc.tasks.file.replace
       Path="Product.wxs"
       OldValue="%22_versionnumber_%22"
       NewValue="%22$(Versionnumber)%22"
       Force="true"
       IgnoreCase="true">
   </Target>

As a final step, I have overriden the "AfterBuild" target of the WiX project to rename the output msi in order to contain the versionnumber in the filename as well.

<Target name="AfterBuild">  
 <Createproperty value="$(TargetDir)%(CultureGroup.OutputFolder)">  
   <Output taskparameter="Value" propertyname="TargetDir">  
 </CreateProperty>  
 
 <Copy sourcefiles="$(TargetDir)$(TargetName)$(TargetExt)"  
       destinationfiles="$(TargetDir)$(TargetName).$(Versionnumber)$(TargetExt)">  
   
 <Delete files="$(TargetDir)$(TargetName)$(TargetExt)">  
</Target>

Making MFC controls available for WinForms through MFC subclassing

2008-11-24T15:10:00.019+01:00

When trying to figure out how to use some of my MFC controls in a WinForms application, I came across this article by Rama Krishna Vavilala. As his article was targetting the .NET 1.1 framework, I decided to rework it for .NET 2.0. The main difference is the switch from using Managed Extension for C++ to using C++/CLI.

Static Win32 library for C3DMeterCtrl

As is the case in Rama's article, I will also use Mark C. Malburg's Analog Meter Control. So first, create a Win32 static library project with support for MFC and precompiled headers called "ControlS" (S stands for static). This will contain the MFC code for the existing 3DMeterCtrl control. Place the files 3DMeterCtrl.cpp, 3DMeterCtrl.h and MemDC.h in the "ControlS" project. Modify the 3DMeterCtrl.cpp file to remove the line #include "MeterTestForm.h".

The .NET designer and runtime will call functions that try to talk to your MFC control even before its window handle is created. In case of the C3DMeterCtrl, I needed to add this function call at the beginning of the "UpdateNeedle" and "ReconstructControl" functions:

if (!GetSafeHwnd())
return;

MFC library for the managed 'ThreeDMeter' control

To bridge the gap between MFC and .NET I'm going to use C++/CLI. This allows me to create a managed wrapper object around the MFC control.

Add an "MFC DLL" project, called "control". Go to the project properties and enable the common language runtime support (/clr). Using the "Add Class" wizard add a new control and call it "ThreeDMeter". Make these changes to the ThreeDMeter.h file:

#include the header file of the MFC control "..\ControlS\3DMeterCtrl.h"
Change the inheritance of the control to public System::Windows::Forms::Control
Add a private instance of C3DMeterCtrl to the class. Create it in the constructor and delete it in the finalizer. In "OnHandleCreated", call its "SubclassWindow" method using the .NET controls window handle.

The file "ThreeDMeter.h" should now look like this:

#pragma once

using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System::Data;
using namespace System::Drawing;

#include "..\ControlS\3DMeterCtrl.h"

namespace Control {

public ref class ThreeDMeter : public System::Windows::Forms::Control
{
public:
ThreeDMeter(void)
{
  InitializeComponent();
  m_pCtrl = new C3DMeterCtrl();
}

protected:
//Finalizer
!ThreeDMeter()
{
  if (m_pCtrl != NULL)
  {
    delete m_pCtrl;
    m_pCtrl = NULL;
  }
}

//Destructor
~ThreeDMeter()
{
  if (components)
  {
    delete components;
  }

  //call finalizer to release unmanaged resources.
  this->!ThreeDMeter();
}

virtual void OnHandleCreated(EventArgs^ e) override
{
  System::Diagnostics::Debug::Assert(m_pCtrl->GetSafeHwnd() == NULL);

  m_pCtrl->SubclassWindow((HWND)Handle.ToPointer());

  Control::OnHandleCreated(e);
}

private:
System::ComponentModel::Container ^components;
C3DMeterCtrl* m_pCtrl;
};

In order to expose the properties of the MFC control in .NET, you need to implement them yourself. Add following code in the public section of the ThreeDMeter wrapper class.

event EventHandler^ OnValueChanged;

[property: System::ComponentModel::CategoryAttribute("Meter")]
property Color NeedleColor
{
  Color get()
  {
    if( m_pCtrl == NULL )
      throw gcnew ObjectDisposedException(ThreeDMeter::GetType()->ToString());
  
    return System::Drawing::ColorTranslator::FromWin32(m_pCtrl->m_colorNeedle);
  }
  
  void set(Color clr)
  {
    if (!m_pCtrl)
      throw gcnew ObjectDisposedException(ThreeDMeter::GetType()->ToString());
  
    AFX_MANAGE_STATE(AfxGetStaticModuleState());
  
    m_pCtrl->SetNeedleColor(ColorTranslator::ToWin32(clr));
  }
}

[property: System::ComponentModel::CategoryAttribute("Meter")]
property String^ Units
{
  void set(String^ units)
  {
    if (!m_pCtrl)
      throw gcnew ObjectDisposedException(ThreeDMeter::GetType()->ToString());
  
    AFX_MANAGE_STATE(AfxGetStaticModuleState());
  
    CString strUnits(units);
  
    m_pCtrl->SetUnits(strUnits);
  }
  
  String^ get()
  {
    if (!m_pCtrl)
      throw gcnew ObjectDisposedException(ThreeDMeter::GetType()->ToString());
  
    LPCTSTR szUnits = (m_pCtrl->m_strUnits);
  
    return gcnew String(szUnits);
  }
}

[property: System::ComponentModel::CategoryAttribute("Meter")]
property double Value
{
  double get()
  {
    if (!m_pCtrl)
      throw gcnew ObjectDisposedException(ThreeDMeter::GetType()->ToString());
  
    return m_pCtrl->m_dCurrentValue;
  }
  
  void set(double d)
  {
    if (!m_pCtrl)
      throw gcnew ObjectDisposedException(ThreeDMeter::GetType()->ToString());
  
    AFX_MANAGE_STATE(AfxGetStaticModuleState());
  
    m_pCtrl->UpdateNeedle(d);
  
    OnValueChanged(this, EventArgs::Empty);
  }
}

.NET Test application

In order to test the managed ThreeDMeter control, add a .NET "Windows application" project to the solution in your favorite language. Put the Three3Meter control on the form and add a timer control

Public Class Form1
  Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As system.EventArgs) Handles Timer1.Tick

    If Me.ThreeDMeter1.Value <= 4 Then
      Me.ThreeDMeter1.Value += (Me.Timer1.Interval / 1000)
    Else
      Me.ThreeDMeter1.Value = -5
    End If
  End Sub
End Class

This will make the meter move from left to right.

Deleting absent, updating existing and inserting new rows in a table

2008-11-19T13:39:00.027+01:00

Synchronizing a table based on a flat files is one of the more common tasks when dealing with databases. It should be a straight forward task to delete removed records, update changed records and insert new records but I often encounter horrible ways of achieving this goal.

I recently came across an implementation using a DTS package with a data-driven task in SQL Server 2000. For each line in the file it would launch several queries to detect if it was a new, updated or unchanged record (all glued together with VBScript) and then launch the appropriate query to update the database.

Searching a bit on the internet I came across this simple solution. The best way of doing this fast and reliably is by using the JOIN and LEFT JOIN clauses in your UPDATE, INSERT and DELETE commands to determine the status of the record. I would like to show my implementation of slightly more complicated requirements.

Preparation

First start by bulk inserting your data file into a staging table that has no constraints or indexes. This is the fastest way to get your data into the database. For the synchronisation queries (see below), you might want to consider using transactions if there is a risk of data corruption. You could also gain some speed by disabling the indexes while running the insert query.

In the following examples I will try to synchronize the accounts table using the tmp_accounts table as the staging table.

Deleting removed rows

DELETE accounts 
FROM accounts acc LEFT JOIN tmp_accounts tmp
ON  acc.category = tmp.category
AND acc.code     = tmp.code
WHERE acc.code IS NULL
   OR acc.category IS NULL

Fields code and category together uniquely define an account.
Using the LEFT JOIN with the FROM clause will ensure that all records from the accounts table are selected, even if they don't exist in tmp_accounts. The records that don't exist in tmp_accounts will have a NULL value for every field. So all records that exist in the accounts table but have NULL values in their tmp_accounts fields, need to be deleted.

Updating changed rows

UPDATE accounts SET
  description = tmp.description,
  type        = tmp.type,
  section     = tmp.section,
  audit_date  = tmp.audit_date,
  audit_user  = tmp.audit_user
FROM accounts     acc
JOIN tmp_accounts tmp
ON  acc.category = tmp.category
AND acc.code     = tmp.code
WHERE acc.description <> tmp.description
   OR acc.type        <> tmp.type
   OR acc.section     <> tmp.section

By using the JOIN clause, we are sure to work only on records that exist in both the accounts table and the tmp_accounts table.
Accounts need to be update in case their description, type or section has changed. Audit_user and audit_date are automatically filled in the tmp_accounts table using a DEFAULT and are always kept up-to-date in the accounts table.

Inserting new rows

INSERT INTO accounts
( category,
  code,
  description, 
  type,
  section,
  title_only, 
  division,
  override,
  allow_manual_entry, 
  audit_date, 
  audit_user
)
SELECT
  tmp.category,
  tmp.code, 
  tmp.description,
  tmp.type,
  tmp.section,
  tmp.title_only, 
  tmp.division,
  tmp.override,
  tmp.allow_manual_entry, 
  tmp.audit_date,
  tmp.audit_user
FROM tmp_accounts  tmp
LEFT JOIN accounts acc
ON  tmp.category = acc.category
AND tmp.code     = acc.code
WHERE acc.code IS NULL
   OR acc.category IS NULL

Using a LEFT JOIN makes sure we are working with all records in the tmp_accounts table, even if they don't exist in the accounts table. Records that don't exist in the accounts table will have NULL values for all of their fields. So all records that exist in tmp_accounts and have NULL values for the accounts fields, have to be inserted.

Audit_user and audit_date are automatically filled in the tmp_accounts table using a DEFAULT and are always kept up-to-date in the accounts table

Scripting with SQL Server Management Objects

2008-09-05T17:22:00.011+02:00

When I needed to move about 28500 stored procedures from a SQL Server 2000 to a SQL Server 2005, I turned to the SQL Server Management Studio. It didn't offer any option to move the stored procedures but it did allow to script them to a text file. I launched the "Generate Scripts" task and noticed that after 8 hours of processing only 3500 stored procedures had been scripted. The process had also eaten all the RAM on my workstation.

It would takes ages at this rate so I cancelled the task and turned to the SQL Server Management Objects that are installed together with SQL Server Management Studio. According to MSDN, SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

The following code shows the C# application that I wrote to quickly generate scripts for all stored procedures in one database. In order to make it compile, you will need to add reference to the assemblies:

Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.ConnectionInfo

What the application does in general, is:

connect to the database
loops over the collection of all stored procedures
script it to a text file, if it is not a system stored procedure

using System;
using System.IO;
using System.Text;
using System.Collections.Specialized;

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SProcScripter
{
  class Program
  {
    static void Main(string[] args)
    {
        int success = 0;
        int failure = 0;

        StreamWriter sw = new StreamWriter("d:\\SProcs.sql");

        // Connect to server and database
        Server srv = new Server("myServerName");
        Database db = srv.Databases["myDatabaseName"];
        Scripter scrp = new Scripter(srv);

        scrp.Options.ScriptDrops = false;
        scrp.Options.WithDependencies = false;

        foreach (StoredProcedure sp in db.StoredProcedures)
        {
            try
            {
                Urn[] smoObjects;
                smoObjects = new Urn[1];
                smoObjects[0] = sp.Urn;

                if (!sp.IsSystemObject)
                {
                    Console.Write("Scripting " + sp.Name);

                    StringCollection sc = scrp.Script(smoObjects);
                    StringBuilder sb = new StringBuilder();

                    foreach (String st in sc)
                    {
                        sb.AppendLine(st);
                        sb.AppendLine("GO");
                    }

                    sw.WriteLine(sb.ToString());

                    success++;
                    Console.WriteLine(" done.");
                }
            }
            catch (Exception ex)
            {
                failure++;
                Console.WriteLine(" failed with exception: " + ex.Message);
            } // try - catch
        } // foreach stored procedure

        sw.Close();

        Console.WriteLine("Success: " + success.ToString());
        Console.WriteLine("Failure: " + failure.ToString());
        Console.WriteLine("Press any key");
        Console.ReadKey();

    } // void Main
  }
}

All stored procedures were generated within 30 minutes! I've tried to make this work faster using a ThreadPool but the SMO components are not thread-safe and all sort of unexpected errors and race conditions popped up. I needed to introduce several locks to keep the object out of each others way. I succeeded in making it work but due to these locks it wasn't really faster than the single threaded version above.

Stand-Alone DTS Designer on SQL Server 2005

2008-09-04T00:44:00.007+02:00

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 PC's 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 filder using the command prompt:

Click Start
Click Run...
Type 'cmd.exe' and press <enter>. The command prompt appears
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 use a custom form that only contains a listbox. In the for loop, all versions and DTS package 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

2008-08-13T21:25:00.001+02:00

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...)

        1: Try

       2:   designer = New DTS.CDTSLegacyDesigner

       3:   designer.Initialize()

       4:   designer.ShowDesigner()

       5: Finally     

       6:   If Not designer Is Nothing Then

       7:     designer.Dispose()

       8:     Marshal.ReleaseComObject(designer)

       9:     designer = Nothing

      10:   End If

      11: 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.

       1: Dim sPackageName As String  

       2: Dim sPackageID As String  

       3: Dim sPackageVersionID As String  

       4: Dim sPackagePathName As String 'Full pathname to DTS package   

       5:  

       6: Try    

       7:   Dim package As DTS.Package = Nothing  

       8:   Dim packageInfoColl As DTS.SavedPackageInfos = Nothing  

       9:   Dim packageInfo As DTS.SavedPackageInfo = Nothing  

      10:   package = New DTS.Package 

      11:   packageInfoColl = package.GetSavedPackageInfos(sPackagePathName) 

      12:  

      13:   '** Example: select the last saved version 

      14:   packageInfo = packageInfoColl.Item(packageInfoColl.Count) 

      15:   sPackageName = packageInfo.PackageName 

      16:   sPackageID = packageInfo.PackageID 

      17:   sPackageVersionID = packageInfo.VersionID

      18:  

      19: Finally 

      20:   If Not package Is Nothing Then Marshal.ReleaseComObject(package) 

      21:   If Not packageInfo Is Nothing Then Marshal.ReleaseComObject(packageInfo) 

      22:   If Not packageInfoColl Is Nothing Then Marshal.ReleaseComObject(packageInfoColl) 

      23: 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.

       1: Try

       2:   designer = New DTS.CDTSLegacyDesigner

       3:   With designer

       4:     .Initialize()

       5:     .Location = 2

       6:     .PackageID = packageID

       7:     .PackageName = packageName

       8:     .ServerName = packagePathName

       9:     .VersionID = packageVersionID

      10:   End With

      11:  

      12:   '** Launch designer

      13:   designer.ShowDesigner()

      14:  

      15: Finally  

      16:   If Not designer Is Nothing Then    

      17:     designer.Dispose()

      18:     Marshal.ReleaseComObject(designer)

      19:     designer = Nothing 

      20:   End If

      21: End Try

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