Now you've done all the work that's required to create the DTS package and transfer a file from an AS/400 DB2 UDB for iSeries database to a SQL Server database. Clicking Next on the Select Source Tables and Views screen displays additional dialog boxes that let you save your DTS package, then either run the DTS package immediately or schedule it to run at a predetermined time. Choosing to run the package immediately starts the database transfer and displays the Executing DTS Package dialog box.
That's all you need to do to transfer data from the AS/400 to SQL Server. The Client Access ODBC Driver takes care of all the required data-conversion details and handles common data values with no problems. For example, it automatically handles the data-type mapping and conversion of EBCDIC (the AS/400's native data type) to ASCII. You can run into problems, however, in areas where the two systems don't support the same data types. Table 1 shows the automatic data-type conversions the IBM ODBC driver supports for DB2 UDB for iSeries—to—SQL Server data transfers. In cases where automatic data conversion isn't supported because of data-type incompatibilities, you need to either omit the columns with data-type incompatibilities or manipulate the data as it's being transferred by using an ActiveX script in conjunction with DTS's data-transformation capabilities. (For information about creating transformations in DTS, see Don Awalt and Brian Lawton, "Creating DTS Custom Transformations," October 1999, InstantDoc ID 6110.)
Another area where you might experience difficulties is in dealing with the AS/400's multiple-member files. Unlike SQL Server, the AS/400 supports a database concept known as members, which are something like sub-tables. A table can contain multiple members, where each member shares the same schema but has a unique name and unique data contents. The sidebar "Handling Multiple-Member Files," page 32, tells how to work with AS/400 multiple-member files.
Transferring Data from SQL Server to the AS/400
Using DTS to transfer data from SQL Server to the AS/400 is a lot more work than importing AS/400 data to SQL Server, and you can't use the same simple point-and-click techniques. Fortunately, DTS has the flexibility to make all the necessary adjustments for uploading SQL Server data to the AS/400. Like importing data from the AS/400 to SQL Server, to export data to the AS/400, you need to build a DTS package by starting the Import/Export Wizard.
In the data-source selection screen that Figure 6 shows, you use the Data Source drop-down box to select the Microsoft OLE DB Provider for SQL Server to connect to the SQL Server system. In the Server field, enter the name of the SQL Server system that contains the source database (in Figure 6, this is TECA4). Next, select the authentication method. This example uses Windows authentication to connect to the SQL Server. The Database drop-down box shows that the SQL Server Northwind database contains the tables to transfer.
After you specify the data source, clicking Next displays the Choose a destination dialog box that Figure 7 shows. In the Destination drop-down box, I've chosen the Client Access ODBC Driver (32-bit) to connect to the AS/400; DTS will use the data source name S1030438 to obtain the specific connection properties, including the name of the AS/400 to connect to. In the Username and Password fields, supply the user ID and password that DTS will use to connect to the AS/400. Again, if you set your DSN to use the Windows login information, you can omit these parameters. Clicking Next displays the Specify Table Copy or Query dialog box, which lets you select whether to transfer by using a query or by copying an entire table. Choose to copy a table, and click Next to display the screen that Figure 8 shows.
On this screen, I selected the Customers table from the SQL Server sample Northwind database. So far, this setup has been similar to the earlier example of transferring the data into SQL Server. However, the similarities end here. Recall that the AS/400 used a three-part naming scheme to identify files. In Figure 8, although DTS has generated a three-part name for the table, it consists of the DSN, the user name (instead of the library name), and the target table. This type of naming scheme won't work for transfers to the AS/400 unless the target library happens to have the same name as the user. That's typically not the case, so you need to change the middle section of the name to the correct AS/400 library.
In addition, the destination name here is initially enclosed in quotation marks. The SQL Server OLE DB provider automatically strips off the quotes, but the IBM Client Access ODBC Driver doesn't. If you used these default values, the transferred tables would have quote characters included as part of their AS/400 object names. Fortunately, the DTS Import/Export Wizard lets you manually edit the generated destination table names by simply positioning your cursor on the name and typing over the existing value. The first step to customizing this DTS package to work with the AS/400 is to remove all the double-quote characters, then insert the correct three-part object name the Client Access ODBC Driver uses. Figure 8 shows these changes made in the Destination column: I removed the double quotes and used the three-part name for all the tables. In this example, DTS will copy the Customers table to the AS/400 system identified by the S1030438 DSN and into a library named QGPL.
The next step is to adjust the data types and column names for each table that you want to transfer. Clicking the Transform button to the right of the Destination column lets you customize the data types that DTS will use when it builds the target table. Figure 9, page 36, shows the Column Mappings and Transformations dialog box that's displayed when you click the Transform button.
On the Column Mappings tab, you can change the data types for each column. To change the destination data type on the Column Mappings tab, click the value displayed in the Type column. An edit box opens that lets you modify the destination data type. Table 2, page 36, shows how SQL Server data types map to AS/400 data types. Next, if you're creating a new table on the target AS/400, click the Edit SQL button to display the Create Table SQL Statement dialog box that Figure 10, page 36, shows. Here, you can change the SQL statement that the Import/Export Wizard generated to match the SQL syntax that the AS/400 expects to see.
The Import/Export Wizard initially generates the CREATE TABLE SQL statement that DTS will send to the AS/400. The wizard works with the ODBC driver to determine the correct data types, but in my experience, it's often wrong. However, you can freely edit this SQL statement. To make the DTS export to the AS/400 work, remove the double-quote characters that enclose the qualified table name and each column name. Then, delete any parentheses that the Import/Export Wizard appends to numeric data types. If you don't remove the double quotes, DTS will create the columns in the AS/400 destination file with the double-quote characters as part of the field name. You must remove the parentheses after the numeric data types to avoid generating the SQL syntax error message SQL0104 — Token was not Valid when the DTS package executes.
You also need to make sure that you're using the correct data types. If the wizard didn't generate the correct types or sizes, you need to manually adjust them in the CREATE TABLE statement. Figure 10 shows the SQL statement in the process of being changed. The first six columns have had the double quotes removed; the Region, PostalCode, and Country columns contain their original values.
After making the adjustments to the destination table names and data types, click Next to display the two Import/Export Wizard dialog boxes that let you save the DTS package and schedule its execution. Selecting the option to run the DTS package immediately starts the transfer to the AS/400. A progress bar indicates how much of the transfer has completed; the check boxes in the lower part of the dialog box tell which steps in the DTS package have completed.
When running multiple-file DTS transfers, you'll quickly notice that DTS doesn't execute the package steps one at a time. Instead, DTS is multithreaded, which means that, by default, it simultaneously launches four threads. Although this usually works, I've occasionally experienced errors where one or more transfer steps fail when I'm running multithreaded DTS transfers to the AS/400. If you intend to run the DTS package unattended, I recommend altering the DTS package properties and setting the default DTS threading to one, thereby limiting the package to executing only one SQL statement at a time.
It Works Both Ways
Like the classic story of Dr. Jekyll and Mr. Hyde, using DTS to transfer data between SQL Server and the AS/400 has two faces. Transferring data from the AS/400 to SQL Server is an easy process, but that's certainly not the case when you turn around and try to transfer data from SQL Server to the AS/400. Although transferring data from SQL Server to the AS/400 can be difficult to set up the first time, once you've learned how, you can set up the SQL Server—to—AS/400 connection to work flawlessly. DTS's power and flexibility let it handle platform incompatibilities and make its database-transfer mechanism vastly superior to performing multistep flat-file transfers.