What you need: Visual Basic 6.0 Microsoft SQL Server 7.0

The company recognized BCP's limitations as a data-transfer tool and re-engineered it in SQL Server 7.0. The result is the SQL Server Data Transformation Service (DTS), one of SQL Server 7.0's significant new features.

DTS provides a simple, graphical interface with the ability to migrate data from one environment to another. You can use DTS to import, export, and transform data in and out of SQL Server databases, and migrate data between any Open Database Connectivity (ODBC) or OLE DB data source. I've provided a summary of the different data source and destination types DTS supports (see Table 1).

In addition to basic import and export functionality, DTS can validate, summarize, and scrub data, making it a valuable tool for data warehousing. DTS also performs data migration outside the SQL Server environment. You can use the DTS object library to develop custom transformation objects that you can integrate into third-party products. You can use this library within any development environment that supports automation.

Microsoft didn't design DTS to replace BCP, but rather to work with it. DTS uses BCP as its default method for migrating data. Microsoft recognizes that BCP is good at avoiding much of the overhead usually associated with this process. DTS uses the best features provided by BCP, enhances them, and presents them in an accessible Windows environment that gives you the best of both worlds.

However, you need to be aware of certain limitations when using DTS. When dealing with heterogeneous data sources, DTS can move only schema information and data. It doesn't support the transfer of rules, stored procedures, triggers, constraints, or user-defined datatypes between heterogeneous data sources. Additionally, despite its versatility, DTS isn't a miracle worker—you can't expect it to overcome the limitations of any given data source or destination. For example, DTS will fail if you attempt to export datatypes such as binary large objects (BLOBs) into a data source where this special datatype isn't supported.

The Wonderful World of Wizards
You can handle data importing and exporting through DTS's Import and Export wizards. The Import and Export wizards are surprisingly robust and can implement both simple and complex data transformations. The flexibility and options provided by these wizards should satisfy users of all skill levels. You can find both the Import and Export wizards under the Data Transformation Services node in the treeview control shown in the Select Wizard dialog.

I'll show you how to use the Import Wizard by appending the list of names and addresses found in the Publishers table in Access' Biblio database to the list of names and addresses found in the Customers table in SQL Server's Northwind database. Although I'll cover only the Import Wizard here, the Export Wizard works in much the same way.

	Figure 1. Data Import and Export Made Easy. Click here.

The Import Wizard's first two screens allow you to enter details on the type of data source you'll be importing, as well as where you'll place your data (see Figure 1). Options for the types of data sources and destinations include SQL Server, Access, Excel, Visual FoxPro, and text. The specific list of remaining options on these two screens will vary, depending on the type of data source and destination you select, but they all allow you to specify the exact whereabouts of your data source and destination.

On the Import Wizard's first screen, I selected Microsoft Access as my data source and entered the specific path and filename for the Biblio.mdb database in the File Name textbox. On the second screen, I selected Microsoft OLE DB Provider for SQL Server as my destination type, specified the name of my local server, entered the authentication information for the server, and selected Northwind as the destination database. The first time you use the wizards in any given session, you might have to click on the Refresh command button next to the list of databases to force all the databases on the selected server to be displayed.

The third screen asks you to further refine your data selection. You can do this by selecting tables to be imported in their entirety or by creating a Structured Query Language (SQL) query. If you've specified that you'll be using Microsoft's OLE DB provider for SQL Server to access both your data source and destination, you'll see an option on this screen that allows you to indicate that you're transferring data between SQL Server 7.0 databases. You can transfer objects other than schema and data only when you're transferring data between SQL Server 7.0 data sources. If you select this option, you'll be asked to specify the objects you wish to transfer. This functionality replaces the Database/Object Transfer functionality found in SQL Server 6.5 Enterprise Manager.

If you specify that you'll be using a query to transfer your data, you can use an existing external file containing the SQL for your query or write your query from scratch. If you write your query from scratch and you're new to SQL (or just feel like getting a little help), you can access DTS's Query Builder tool, which allows you to create complex queries.

You can also define a query as a stored procedure, which gives you access to the full capabilities of Transact-SQL (T-SQL). If you define a query as a stored procedure, the stored procedure will be created in your source database when you run the DTS package. In this example, I did a direct table copy.

Figure 2. Simplify Data Transfer Without Limiting Your Options. Click here.

Next, you'll be asked to confirm the data entities to be transferred, as well as their destination. If you're using a query to transfer your data, the table sources and destinations will be prepopulated based on your query. For this example, I selected to copy only the contents of the Publishers table. I selected the Customers table in the Northwind database as my destination. Regardless of whether you're using a query or doing a straight table copy, you can modify the column mappings and the transformation options using the Column Mappings and Transformations dialog box (see Figure 2).

Once again, the options on this screen will vary slightly, depending on the specific data source, destination, and transfer type. The Column Mappings tab lets you not only see and modify details for column mappings, but also modify details such as datatypes, nullability, and field names. I had to modify several of the column mappings in order to match the fields in the Publishers table with the corresponding fields in the Customers table. The Transformation tab of the Column Mappings and Transformations dialog box allows you to implement simple data transformations.

You can gain some additional control over data conversions by using the predefined options. These options include converting 16-bit numbers to 32-bit numbers and requiring an exact match (datatype, size, precision, scale, and nullablility) between the source and destination. If the predefined options don't provide you with enough flexibility, you can directly modify the ActiveX script DTS uses to perform the data transformation. By providing this option, DTS gives you access to the full capabilities of your scripting language to manipulate the transformation script. You can write your script in either VBScript or JScript.

Finally, the last screen in the Import Wizard lets you save and execute your data transformation. You can choose to run it immediately or schedule it to execute later by saving it as a DTS file. DTS files are COM structured storage files that let you save multiple packages and versions to the same file. You can save the Import and Export wizards as regular DTS files or DTS packages for replication. You can execute a DTS package for replication from a remote location. You can download the data transformation created in the example, as well as all sample code referenced in this article, here.

The DTS Package
A DTS package is a series of tasks performed in an order you specify and control. When you save an import or export data transformation, it becomes a DTS package. To view saved transformations as DTS packages or to create new packages from scratch, open the Data Transmission Services folder under the appropriate server in the SQL Server 7.0 Enterprise Manager. Open an existing package by double-clicking on it in the treeview, or open a new package by right-clicking on the Data Transmission Services folder and selecting New Package.

	Figure 3. Move Beyond Basic Importing and Exporting. Click here.

You can create and modify DTS packages within the Enterprise Manager using the DTS Designer (see Figure 3). The DTS Designer is a graphical interface that lets you define the tasks and order of operations within the DTS package. A package normally includes source and destination data objects. Additionally, a DTS package can include task objects and workflow scenarios. A task object represents a unit of work performed as part of the transformation process. Task objects can execute a T-SQL statement or a script, or transfer SQL Server 7.0 objects. I've compiled a complete list of the predefined task objects available in the DTS Designer (see Table 2).

The data transformation created in the earlier example contains an Access data object as the source, and a SQL Server data object as the destination (see top screen in Figure 3). The two data sources are connected by a data-transformation object. The data-transformation object migrates information between data objects or connections. You can view and modify the properties for all three of the objects in the transformation by right-clicking on the object.

To illustrate some of the DTS Designer's capabilities, I modified the transformation into a more complex DTS package. You can view the Import Wizard in the DTS Designer with the enhancements made (see bottom screen in Figure 3). The three types of precedence constraints are the On Success constraint, which executes when a referenced task is completed successfully; the On Failure constraint, which executes when a referenced task is completed unsuccessfully; and the On Completion constraint, which executes when a referenced task is completed either successfully or unsuccessfully.

If the transfer from the Access database is completed successfully, you perform a second data transfer with SQL Server as the destination. This time, I imported a list of names and addresses from an Excel spreadsheet. If the transfer from the Access database isn't completed successfully, you can add a Send Mail task object to the package that will notify you by e-mail of the failure. In order to improve performance, steps not dependent on any other precedence constraints can be set up to execute in parallel on different threads.

Figure 4. Use DTS in Custom Applications. Click here.

Beyond Enterprise Manager
The DTS Designer provides you with a robust and easy-to-use interface for creating and modifying DTS packages. Microsoft hasn't confined this functionality to interactive use—you can accomplish most tasks in the DTS Designer using any development language that supports automation, such as Visual Basic 5.0 or 6.0. This means you can use the DTS packages from custom applications, and you can embed the functionality of the DTS packages into custom applications. I created a simple form in VB that contains a grid displaying the contents of the Customers table in the Northwind database (see Figure 4). Although I developed this application using VB6, the code that references the DTS object library also works in VB5.

Before running this application locally, you'll have to do a little preparatory work. The text in the Connection String textbox at the top of the form contains the connection string you'll use to connect to the Northwind database. Change this text if necessary to point to your SQL Server 7.0 database server. The properties for Access, SQL Server, and Excel data objects in the DTSDemo.dts file must be modified in the DTS Designer to reference the file path and location of the corresponding files on your computer or network. Do this by right-clicking on the objects one at a time and selecting Properties from the context menu.

After you change the properties for each of the data objects, you'll be asked if you want to reset the transformations that have been defined using the connection being altered. Answer “No” to this question. Otherwise, the DTS package won't run properly. Finally, you might wish to alter the properties of the Send Mail object in the DTS Designer so it contains your own e-mail address.

When you start the application and click on the Refresh Grid command button, the contents of the Northwind database are displayed in the grid. When you click on the Execute Import command button, a new DTS.Package object is created (see Listing 1). Set the Package object to point to DTSDemo.dts, the DTS package consisting of the enhanced transformation created earlier. Execute the package using the Package object's Execute method. That's all there is to running an existing DTS package.

By clicking on the Refresh Grid command button, you can see that the contents of the Northwind database have changed. If you try to execute the import a second time, data won't change, because attempting to import the records again violates the unique index on the CustomerID column. This causes the DTS package to fail.

The Delete Imports command button creates a new DTS package consisting of a SQL task. This task executes a SQL statement that deletes the previously imported records (see Listing 1). Once you create the new DTS package object, you must create a connection to the SQL Server database and add it to the package's Connections collection. A new SQL task object consisting of the desired SQL is then created and added to the package's Tasks collection. The task is then executed using its Execute method.