SQL Server Data Transformation Services (DTS) is designed to move and transform data between heterogeneous data sources. For the scenario described in this chapter, our goal is to move and transform data from an IBM DB2 database on an AS/400 system to data stored in SQL Server 2000 on a Windows 2000 system.
The DTS Import/Export Wizard provided with SQL Server is used to create DTS packages that control this process. DTS packages are sets of tasks for importing, transforming, and exporting data that can be reused and scheduled to operate as needed. DTS packages contain the following information:
The connection information specifies the data provider to use for connecting to the source and the data provider to use for connecting to the destination. In our example, this would be the Microsoft OLE DB Provider for DB2 as the source and the Microsoft OLE DB Data Provider for SQL Server for the destination. Connection information also includes the database server or servers to connect to, logon and authentication information, and the specific database to access for the import/export operations.
The DTS process can be based on SQL queries against a database, scripts, or external commands as well as simple copies of data tables. So DTS tasks can consist of ActiveX scripts, SQL scripts, SQL queries, commands to transfer SQL Server objects, data-driven queries, bulk insert commands, and external processes to execute. The scenario described in this chapter is a simple task that copies and transforms some data tables in DB2 on the AS/400 to similar data tables in SQL Server on Windows 2000.
DTS packages can be stored in a variety of locations and formats including the following:
A DTS package can be created on one computer and stored on a different computer running SQL Server or in one of the above external formats. The DTS package does not need to be stored on the SQL Server that will execute the package, but can be stored remotely on other computers running SQL Server.
The DTS Import/Export Wizard simplifies the complex task of initially creating a DTS package. The DTS Import/Export Wizard is usually started from Microsoft SQL Server folder off the Programs folder from the Windows Start button. The DTS Wizard is called Import and Export Data on the Microsoft SQL Server list of applications. The DTS Wizard can also be started from the SQL Enterprise Manager application used to configure and manage SQL Server 2000. Below each SQL Server instance in the Enterprise Manager treeview is a Data Transformation Services folder. Expanding the Data Transformation Services folder exposes the possible locations where DTS packages can be saved in SQL Server:
Right-clicking the Data Transformation Services folder brings up a menu with various options. The Import option or Export option off the All Tasks entry will start the DTS Import/Export Wizard.
The DTS Import/Export Wizard walks the user through the following steps:
Recent versions of IBM DB2 Universal Database include a SAMPLE database containing a number of data tables. The following illustrations are from the configuration of a DTS package to copy the SALES data table from the DB2 SAMPLE database to SQL Server. We start with the selection of the data source.
In our scenario, the Microsoft OLE DB Provider for DB2 was selected from the drop-down list of possible data sources. Once this provider was selected, the dialog was modified with the addition of a text field for the UDL file name to use for accessing the appropriate database and schema. This UDL file would have been previously created using the NewSNADS application included with the OLE DB Provider for DB2 on Host Integration Server 2000. The UDL file specifies the information required for accessing the appropriate DB2 database and tables on the AS/400 system. The button to the right of the text field for the UDL file name with three dots can be used to browse the file system for UDL files. It is useful to recall that data links are by default created in the following folder:
C:\Programs Files\Common Files\System\Ole DB\data links
Selecting the destination for the DTS operation is simpler, since the target is SQL Server on the local system. The database to be targeted must have already been created in SQL Server using Enterprise Manager or an SQL script. The specific database to target is selected from the drop-down list. Windows authentication is preferred since a User name and Password do not need to be specified and saved in the DTS package.
DTS supports copying data tables and views from a source database or using an SQL query to specify the data to transfer. For DTS operations between different instances of SQL Server, simple copying of objects and data is also supported. A DTS table copy supports transferring a subset of the columns in a table when the entire data table is not needed.
For the proposed scenario, a copy of tables is selected for this option. In a real data integration deployment, an SQL query may be preferred over a simple table copy if only a subset of the rows in a data table are of interest.
Once the data source, destination, and operation type are specified, the tables and views to be copied are selected from a list of data tables in the remote DB2 database. DTS uses the Microsoft OLE DB Provider for DB2 to query the remote DB2 system and return the data tables in the specified database. All of the tables in a remote DB2 database and schema can be specified, but it is more efficient to select only those tables that are needed. In the accompanying screen snapshot only the SALES table from the SAMPLE database has been selected for transfer.
Destination tables in SQL Server can be specified as well as any transformations that are to be applied to the columns in a data table. To the right of the Destination field in the grid is a Transform field that allows configuring specific transformations and mappings between columns in the source and destination data tables. Clicking the button with three dots in the Transform field launches the dialog for Column Mappings and Transformations. This dialog allows the user to view and modify how source data types are mapped and transformed to destination data types.
Using the DB2 SALES table in the SAMPLE database, the DTS wizard defaulted to map the DB2 "DATE" type used for SALES_DATE to the SQL Server "smalldatetime" type. Using column mapping, this conversion was changed to a "datetime" type in SQL Server. Any DB2 data types in the tables to be copied that are not supported by SQL Server would need to be mapped to some appropriate SQL Server data type.
The "Drop and recreate destination table" option should be checked if the copy operation is to occur repeatedly and replace the contents of the destination table on each copy. In the scenario for this chapter, this option should be selected for all tables to be copied.
The DTS Import/Export Wizard allows the user to run the created DTS package immediately or schedule the package for later execution by the scheduler service (SQL Server Agent) in SQL Server. When initially creating a DTS package, the options to schedule or run the DTS package are better left unselected. The DTS package should be saved for later use first, and scheduled for execution after testing.
A DTS package can be saved in a number of locations and formats including the following:
It is a good idea to initially save the DTS package to SQL Server or SQL Server Meta Data Services for use in preliminary testing. If the DTS package is saved to one of these SQL Server locations, it can be password protected to prevent unauthorized users from viewing or executing the package. After the DTS package has been tested and is operating properly, it also makes sense to save the package as a structured storage file so the package can be easily copied to other computers. The structured storage file format can contain multiple DTS packages and different versions of the same DTS package.
Once a DTS package is saved to one of the SQL Server formats or as a structured storage file, it is simple to open the package using the DTS Designer from Enterprise Manager and save the DTS package in other locations and formats.
The DTS package needs to be saved to a Visual Basic file if it will be used as part of an application program developed in Visual Basic. Note that the Visual Basic file that is created usually requires some minor changes before use (values needed for User name and Password to connect to a database, for example). The Visual Basic file also needs to be compiled using Visual Basic with the SQL DTS COM object classes in order to be used.