Most organizations have the lofty objective of maintaining all corporate data in a centralized database, but this goal is often hard to obtain with disparate systems scattered throughout the enterprise. Microsoft includes Data Transformation Services (DTS) with SQL Server 2000. DTS provides a set of tools to collect and transform data from the many sources. DTS is tightly integrated with the Microsoft line of products, so its functionality is available to programmers as well. Let’s look at how to programmatically control DTS import and export functions by way of VB.NET.

Learn more about DTS

Click here to learn more about the graphical DTS interface provided with the SQL Server 2000 Enterprise Manager client.

The environment
Accessing DTS objects programmatically requires installation of the following products:

  •         Microsoft SQL Server client tools on development machine
  •         Microsoft SQL Server client tools on machines where applications will be executed

An additional requirement is the installation of SQL Service 2000 Service Pack 2 or later. When developing a custom task or just manipulating the DTS objects through .NET without the installation of the service pack, you may receive the following error:
 
QueryInterface for interface DTS.CustomTask failed

This error has been fixed in SQL Server 2000 Service Pack 2. Make sure you apply the service pack to the SQL Server machine, and any client machines that will be running the .NET code, since DTS is a client-side tool. I tested the code in this article without the service pack, and I did receive the aforementioned error.

Packaging the data
A brief introduction to the aspects of DTS is necessary before diving into the code. First, a basic DTS element is a package. It is an organized collection of connections, DTS tasks, and DTS transformations. A package is created with the SQL Server Enterprise Manager client or programmatically.

The package contains one or more tasks to be executed sequentially or in parallel when the package is executed. A task is a discrete set of functionality, executed as a single step in a package. Each task defines a job to be completed as part of the overall process. Here is a brief list of common DTS tasks:

  •         Importing/exporting data to/from SQL Server
  •         Transforming data
  •         Copying database objects

All of these tasks rely on a connection to the data source and destination.

Establishing a connection
Currently, the DTS programming model relies upon the OLE Database architecture. Native OLE Database drivers or an ODBC connection may be utilized to interact with database systems such as Oracle, Access, SQL Server, and so forth. OLE Database is an older Microsoft technology that relies upon COM technology to provide programmatic access to DTS objects. With that in mind, my sample VB.NET code takes advantage of COM Interoperability included with .NET to use the COM objects. I expect the forthcoming release of SQL Server .NET to update DTS to conform to the .NET architecture.

Necessary files
The DTS COM objects are located in three files installed with the SQL Server client tools:

  •         Dtspkg.dll—Microsoft DTSPackage Object Library
  •         Dtspump.dll—Microsoft DTSDataPump Scripting Object Library
  •         Custtask.dll—Microsoft DTS Custom Tasks Object Library

The code developed in this article includes basic functionality, so the only necessary file is the DTSPackage Object Library. Figure A shows these files as they are added (as references) to our VB.NET application. You can access the dialog box by going to Project | Add Reference and choosing the COM tab.

Figure A
Adding DTS COM objects to a VB.NET application

Start the coding
At this point, you’re ready to begin coding. Listing A contains VB.NET code to perform a bulk insert of data into the Authors table within the Pubs database. The code utilizes the following DTS objects:

  •         Package
  •         Connection
  •         Step
  •         Task
  •         BulkInsertTask

The Package holds everything together, and the Step is a rung within the DTS process. These rungs include the individual tasks. Notice in Listing A that the Package and Step objects are declared with a 2 appended to the class name. There are two versions of the classes with the first version (no number) working with earlier SQL Server versions. You can take advantage of the newer version because you’re using SQL Server 2000.

In the code, the Connection object is assigned the necessary properties to connect to the server (e.g., username, password, datasource, etc.). Once established, the connection is added to the package. Next, the Step object is created along with the Task objects. Finally, the objects are added to the package, and it is processed via the execute method.

Basically, the code imports data contained in a text file into the pubs database. The format for the text file follows:
 
666-66-6|Tester|Chester|214 243-6666|1 Main Street.|Somewhere|PA|15003|1

The code is enclosed in a Try/Catch/Finally block to handle any unexpected errors. The objects are properly disposed in the Finally block.

The code is simple, and performing such inserts is a common task in most organizations. The code could easily be created in a Windows Service and scheduled to perform the insert on a nightly basis.

Listing B provides a glimpse at another piece of functionality exposed by the DTS objects. It automates the process of copying a database schema and data from one database to another. Please note: The destination database should be created before running the routine. The database uses the same objects as in Listing A with the exception of the TransferObjectsTask2, which is used to perform the actual database copy. The methods of this class are used to set the destination and source database along with other options.

Automation is good
Moving data from one system to another is a common programming chore, and the availability of the DTS services to the programming community is a welcome option for those working with SQL Server. I look forward to the next SQL Server version that provides DTS support within the .NET architecture.