Explore SQL Server 2000 DTS Packages Using .NET and the DTS Class Library

SQL Server developers and administrators are no strangers to Data Transformation Services (DTS) packages. They likely develop DTS packages to perform everything from simple database operations to data aggregation. As such, when they upgrade some or all of their servers to SQL Server 2005, they must decide what to do with all of their DTS packages.

If you find yourself in this scenario, it can get hectic. The number of DTS packages you’ve created is probably not trivial, and as a DBA or developer in a perpetually changing environment that demands more with less, every minute counts. You must move quickly but remain thorough. With DTS literally everywhere, how do you assess the scope of a database change on your DTS packages? Short of opening and examining each package, what are the other options? Fortunately, there is a solution: the DTS class library.

At my company, we used the DTS class library and .NET Interop to build a utility that collected everything we needed to assess the scope of a change on a DTS package. This article introduces some of those DTS classes and explains how we applied the classes to build a utility that produces a simple DTS contents report in HTML.

The DTS Class Library

Before examining the DTS class hierarchy, let’s review how a DTS package is built. When you create a typical DTS package, you start by adding the various connection icons to the DTS workspace. Connections consist of things such as SQL Server databases, Excel spreadsheets, and ASCII text files. After adding the connections to the workspace, you add various task icons. Tasks can be things like SQL statements, data transformations, and data transfers. Next, you join the task objects by specifying the workflow options.

The DTS object hierarchy closely mirrors the composition of a DTS package. Connections and tasks can be accessed using the collections of the same name. Workflow access requires two classes. Figure 1 shows Steps and PrecedenceConstraints Key objects in the DTS hierarchy.

Figure 1: Steps and PrecedenceConstraints Key Objects in DTS Hierarchy

Each of the major objects in the DTS class library contains a Properties collection. Normally, a Properties collection works in conjunction with the development tool to provide auto-complete functionality, but this example has other uses for the Properties collection, which an upcoming section will discuss.

.NET COM Interop

The DTS class library objects are COM/ActiveX based, so accessing the objects using Visual Studio .NET requires .NET COM Interop. For the most part, working through COM Interop is straightforward. However, one of the COM Interop quirks is the use of a proxy assembly through which .NET accesses the COM component. When the Interop assembly is built, COM classes are given the “class” label. Thus, Package2 is accessed via the Package2class object in the Interop assembly.

Putting It All Together

With the proper background on the DTS classes, you can move on to the design of the DTSExplorer program. Figure 2 shows the DTS Explorer class hierarchy.

Figure 2: DTS Explorer Class Hierarchy

DTSPackageMediator and DTSCollectionItem collect the information from the DTS packages. DTSContext opens the DTS package and instantiates the DTSPackageMediator object. DTSPackageInfoCollectionMediator gathers the DTS packages together and instantiates the DTSContext object.

With an understanding of the class relationships, look at how integral pieces of the classes are implemented. As mentioned before, DTSPackageInfoCollectionMediator gathers the DTS packages together. DTS packages can be stored in two different places, the file system and SQL Server, and in two different formats, DTS Binary format and as Visual Basic code. In my company, we store our DTS packages as Binary DTS files on the file system, mostly because we store DTS packages along with the SQL database scripts in a Version control system. Thus, the Init function in the DTSPackageInfoCollectionMediator class accepts a directory name, and the MoveNext function recursively traverses the directory structure using a class called FileCollectionBridge.

Once a DTS package is found, work begins to open the DTS package and mine the data contained in the package. The heart of the application is the DTSPackageMediator class. The CollectInfo function in the DTSPackageInfoCollectionMediator class utilizes the Task, Connection, and Step functions on the DTSPackageMediator class to extract the DTS data and invoke functions to write the 5data.

Task, Step, and Connection each work in a similar way: They access the appropriate DTS collection and iterate through the items in the collection. DTSCollectionItem is responsible for iterating through the Properties collection when the GetProperties function is invoked and saving property information to an ArrayList. DTSCollectionItem contains three overloaded constructors. It performs the data collection depending on the constructor invoked. The following is the GetProperties function:

public void GetProperties ( ArrayList values)
   {
      switch(_itm)
      {
         case DTSItem.Tasks:
            for (int i=1;i<=_task.Properties.Count; ++i)
            {
               values.Add(GetSafeString(_itm.ToString(),
                          _task.Properties.Item(i).Name.ToString(),
                          _task.Properties.Item(i).Value));
            }

            break;

         case DTSItem.Connections:
            for (int i=1;i<=_connection.Properties.Count; ++i)
            {
               values.Add(GetSafeString(_itm.ToString(),
                          _connection.Properties.Item(i).Name.
                          ToString(),
                          _connection.Properties.Item(i).Value));
            }
            break;
         case DTSItem.Steps:
            for (int i=1;i<=_step.Properties.Count; ++i)
            {
               values.Add(GetSafeString(_itm.ToString(),
                          _step.Properties.Item(i).Name.ToString(),
                          _step.Properties.Item(i).Value));
            }
            break;
         case DTSItem.PrecedenceConstraints:
            for (int i=1;i<=_stepConstraint.Properties.Count; ++i)
            {
               values.Add(GetSafeString(_itm.ToString(),
                          _stepConstraint.Properties.Item(i).Name.
                          ToString(),
                          _stepConstraint.Properties.Item(i).Value));
            }
            break;
      }
   }

Extending the Example Application

Now you’re ready to learn how to extend the program. To gather additional information, you must invoke other classes in the DTS class library. To supplement the SQL Server documentation, save a DTS package as VB code and browse the code. Also, you can use the program to make changes to groups of DTS packages. So, for instance, you can use the Connection objects to change a server name in a group of DTS packages. Finally, instead of writing the results to HTML, you can write the collection results to a database. Once you have the data in a database, you can build reports or queries to examine the results.

With SQL Server 2005 just around the corner, your SQL DBA or developer world will be full of many changes. With preparation and the proper tools, you can alleviate the uncertainties that accompany any big change. The program in this article will help address the changes coming to your DTS packages.

About the Author

Jeffrey Juday is a software developer with Crowe Chizek in South Bend, Indiana. He has been developing software with Microsoft tools for more than 12 years in a variety of industries. Jeff currently builds solutions using BizTalk 2004, ASP.NET, SharePoint, and SQL Server 2000. You can reach Jeff at jjuday@crowechizek.com.

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read