What you need: Visual Basic 6.0 SQL Server 2000

A graphical interface called DTS Designer lets you create and manipulate complex DTS packages within SQL Server Enterprise Manager. You can also use a programming language that supports COM, such as Visual Basic or C++, to access the DTS object model directly. I'll show you how to use DTS Designer to generate VB code that creates and executes a DTS package using the DTS object model.

The latest version of DTS (DTS 2000) adds the ability to create DTS packages in SQL Server using DTS Designer, then save the packages as VB code. For this example, I use DTS Designer to create a simple DTS package that takes a list of names and addresses from the Publishers table in Access' Biblio database and imports it into the Customers table in SQL Server's Northwind database. The package imports a second list of names into the SQL Server database from an Excel spreadsheet if it completes the first task successfully. The DTS code sends you an e-mail automatically using the Send Mail task if the package doesn't import from Access successfully.

Select the package, then select the Save As menu option in DTS Designer to invoke the Save DTS Package dialog box (see Figure 1). Here you have the option to specify a name for the package, as well as passwords you want to use to restrict the viewing of the package to certain users. In addition, you can specify one of four locations where DTS saves the package: a SQL Server MSDB database, meta data services, a structured storage file, or a VB file. The additional saving options vary depending on which location you select. I selected Visual Basic File (VBF), entered the filename, and clicked on OK. As a result, I received the creation of a VB code module, or BAS file, that contains all the code needed to create and execute the DTS package from scratch.

You can use this BAS file to create a new VB application, or you can add it to an existing application or ActiveX DLL. Writing code with the DTS object model can be difficult, and Microsoft has provided little documentation historically. The ability to generate VB code from DTS Designer eliminates some of the headaches that writing code from scratch causes. It allows you to create a package using a graphical user interface, which can be quicker and easier—especially for programmers new to DTS or VB.

	Figure 1 Save DTS Packages as VB Code. Click here.

Review the Code in VB
You need to integrate the newly created BAS file into a new VB project to review the code. To do this, first open a new VB project and add the DTS module to the project by selecting Add Module from the Project menu. You also need to add a reference to the DTS object library before you can run the project. Do this by selecting References from the Project menu and checking the Microsoft DTSPackage Object Library box on the Reference dialog window. The SQL program installs DTS-Package Object Library when you install SQL Server 2000. Be sure you select the most recent version of the DTSPackage object model if you still have SQL Server 7.0 on your machine.

Next, it's a good idea to review the DTS object library using the object browser in VB. This helps familiarize you with all the properties and methods available in the object model, including the new items Microsoft added to it for DTS 2000.

The DTS package generates a module that contains almost 1,500 lines of code, even though the package in this example is relatively simple. This number might seem overwhelming at first glance, but it becomes clear on closer examination that the code is relatively easy to understand. DTS Designer follows logical steps to create the code module when it creates a DTS package in VB (see Figure 2).

The logic DTS Designer applies to create and run DTS packages programmatically using the DTS 2000 object model is the same logic it applied to create and run DTS packages programmatically using the DTS object model that shipped with SQL Server 7.0. In fact, the object models for the two versions of DTS are similar. However, the DTS 2000 object model enhances several of the objects within it and provides them with additional functionality. DTS 2000 denotes the enhanced objects with the number 2, which follows the object name (such as Package2, Connection2, Step2, and so on). The original objects (Package, Connection, Step, and so forth) still exist in the object model for backward compatibility. You must use the original objects if you build an application that you run against SQL Server version 7.0 or earlier. You'll receive a "type mismatch" error if you attempt to run a DTS application that uses any of the extended objects against an earlier version of SQL Server. DTS Designer generates a code that uses the new, extended objects.

The first step towards programming a DTS package is to create a new version of the Package object and set its properties (see Listing 1). One reason DTS Designer creates such lengthy code is because the code sets most (and in some cases, all) of the properties for an object explicitly. You can shorten the code by removing some of the lines that set property values, and by using the default values the DTS object model assigns when you create the object in your VB code. You must determine which properties the code should set explicitly and which properties can use the default values on a case-by-case basis.

Figure 2 Understand the Process for Creating DTS Packages Click here.

Connect in Three Ways
The next step in creating your DTS package is to create all the necessary connection objects and append them to the Package object's Connections collection. DTS Designer's auto-generated code creates three connections in this example: the first one to represent the SQL Server destination database, the second to represent the Access data source, and the third to represent the Excel data source.

After you create the connections and append them to the Package object, you need to create the steps and tasks for the package and append them to the package's Steps and Tasks collections. You can think of a step in the DTS object model (such as migrating data from Access to SQL Server, migrating data from Excel to SQL Server, or sending an automatic e-mail notification) as an operation the package performs. A task is the object that actually performs the operation.

DTS Designer places the code responsible for the creation of tasks in separate subroutines entitled Task_Sub1, Task_ Sub2, and Task_Sub3. It generates additional code to set up the transformations for each task if a task involves migrating data from a source to a destination, such as importing data from Access and Excel into SQL Server. The transformations map each data column in the Source object to the appropriate data column in the Destination object.

DTS Designer sets up the transformations by creating a new instance of a Transformation object, setting its properties, and appending it to the Transformations collection of a task. In this example, DTS Designer places the code that creates each transformation associated with a task in its own separate subroutine; this accounts for the 20 additional subroutines in the code module.

The sample DTS package contains precedence constraints, which dictate that the Excel data should migrate only after the Access data has migrated successfully. The precedence constraints also instruct the DTS package to execute the Send Mail task instead of the Excel data migration if the Access migration doesn't execute successfully. DTS Designer must create and append these precedence constraints to the PrecedenceConstraints collection of the corresponding step. You're ready to execute the package by calling the Package object's Execute method after DTS Designer has written this code.

The steps I outline for creating a DTS package might vary programmatically—as will the code generated by DTS Designer—depending on the functionality of your specific package. Using DTS Designer to create VB code can save you a lot of time, because you can customize and integrate the code DTS Designer generates into your own VB applications.


Mary Hooke is the president of Distinctive Development, a software consulting firm located in the New York City area. Reach Mary by e-mail at mhooke@distinctivedevelopment.com.