Data Transformation Services

3 4

DTS, which is part of SQL Server Enterprise Manager, is designed to help you easily import data into the database and export data out of the database. DTS consists of two wizards: the Import Wizard and the Export Wizard. In this section, we'll look at how to use these wizards.

Import Wizard

You can use the Import Wizard to import data into the database from various data sources. Unlike BCP and the BULK INSERT T-SQL statement, the Import Wizard can import data from sources other than data files. To use the Import Wizard, follow these steps:

1. In Enterprise Manager, expand a server group and click the name of the server you want to import data into. From the Tools menu, choose Wizards. In the Select Wizard dialog box that appears, expand the Data Transformation Services folder, click DTS Import Wizard, and then click OK. Alternatively, right-click the name of the server, point to All Tasks, and then click Import Data. The Data Transformation Services Import/Export Wizard welcome screen appears, as shown in Figure 24-2.

   

   Figure 24-2. The Data Transformation Services Import/Export Wizard welcome screen.

2. Click Next to display the Choose A Data Source screen, shown in Figure 24-3.

   

   Figure 24-3. The Choose A Data Source screen.

   Here you select a data source from the Source drop-down list. Figure 24-3 shows Text File selected. You can choose from the following data source options:

   • dBase
   • Microsoft Access
   • Microsoft Data Link
   • Microsoft Excel
   • Microsoft Visual FoxPro
   • Other ODBC data source
   • Other OLE DB data source
   • Paradox
   • Data files

   These selections depend in part on the ODBC drivers you have installed on your system. For example, if you have an Oracle ODBC driver installed, OLE DB provider for Oracle will also be listed as an option. The Choose A Data Source screen will change based on the data source you select. Whichever source you choose, you will need to enter file and sometimes logon information.

3. Click Next to display the Select File Format screen, shown in Figure 24-4. (The Select File Format screen is shown only when Text File is chosen.) This screen allows you to select a file format. The options on the screen are described here:
   • The Delimited and Fixed Field option buttons allow you to choose the format of the input file, and a particular delimiter character or a fixed width.
   • The File Type drop-down list allows you to specify whether the input file is an ANSI-format, OEM-format, or Unicode-format file.
   • The Row Delimiter drop-down list lets you specify which character is used to terminate each row in the input file.
   • The Text Qualifier drop-down list can be used to specify text in a delimited file.
   • The Skip Rows spin box allows you to specify how many rows at the beginning of the input file to skip.
   • The First Row Has Column Names check box specifies that the first row is not data; it is a label and is skipped.

   

   Figure 24-4. The Select File Format screen.

4. Choose the delimited file format, {CR}{LF} for the delimiter, and no text qualifier. Then click Next to display the Specify Column Delimiter screen, shown in Figure 24-5. (If you click Fixed Field instead of Delimited, the Fixed Field Column Positions screen appears.) This screen provides a convenient way of specifying the column delimiter because you receive instant feedback based on your choice, which shows whether you have chosen the appropriate delimiter. You can use commas, tabs, semicolons, or any other delimiter. When you select the delimiter, rows appear in the Preview pane. This lets you see whether you have chosen the best delimiter based on the data in the file.

   

   Figure 24-5. The Specify Column Delimiter screen.

5. After you select a delimiter, click Next to display the Choose A Destination screen, shown in Figure 24-6. This screen lets you select a database to import the data into. You must specify the SQL Server ODBC destination (the ODBC alias for the database) as well as a server and a database. In this example, we'll specify the Northwind database. You must also choose an authentication type: Windows NT or SQL Server authentication. If you use SQL Server authentication, you must type a SQL Server username and password in the appropriate text boxes. (SQL Server security is described in detail in Chapter 34.) If you type an invalid username or password, you can retry by clicking Refresh. To modify additional properties, including security options and the connection time-out, click Advanced and make your selections in the screen that appears. These properties do not usually need to be modified.
6. Click Next to display the Select Source Tables And Views screen, shown in Figure 24-7. From this screen, you choose the table into which the data is to be loaded by selecting it from the drop-down list in the Destination column. You can preview the data by clicking Preview. And you can use the Select All and Deselect All buttons to easily select all or no tables from the list.

   

   Figure 24-6. The Choose A Destination screen.

   

   Figure 24-7. The Select Source Tables And Views screen.

7. From this same screen, you can access the transformation services. The transformation services allow you to transform the data (change columns and so forth) while you are performing the import. To transform data, first click the Transform button (the button showing three dots under the label Transform) to open the Column Mappings And Transformations dialog box, shown in Figure 24-8. On the Column Mappings tab, you can choose to create a new table or to delete rows from or append rows to an existing table. The Append Rows To Destination Table option is the default. If you choose to create a table, the Edit SQL button will allow you to view and modify the SQL statement that you used to create the table.

   

   Figure 24-8. The Column Mappings tab of the Column Mappings And Transformations dialog box.

8. Click the Transformations tab to view the transformation options, shown in Figure 24-9. On this tab, you can choose to copy directly into the columns or to transform information as it is copied. Transformation services such as precision conversion (16-bit to 32-bit, 32-bit to 16-bit) are specified here. Conversion of null values can also be performed (NOT NULL to NULL, NULL to NOT NULL).

   

   Figure 24-9. The Transformations tab of the Column Mappings And Transformations dialog box.

9. Click OK to close this dialog box, and click Next to display the Save, Schedule, And Replicate Package screen, as shown in Figure 24-10. This screen lets you run the import now or schedule it for a later time. You can also save the DTS package so that you can run this import again later. To do this, select the Save DTS Package check box, which appears in the Save area at the bottom of the screen. This will save the transformation services settings you made.

   

   Figure 24-10. The Save, Schedule, And Replicate Package screen.

10. Click Next to display the Completing The DTS Import/Export Wizard screen, shown in Figure 24-11. Click Finish to run the import.

    

    Figure 24-11. The Completing The DTS Import/Export Wizard screen.

11. After you click Finish, you will see the Executing Package screen, shown in Figure 24-12. A message box then appears informing you that the data copy is completed or that an error occurred.

    

    Figure 24-12. The Executing Package screen.

As you can see, the DTS Import Wizard makes performing an import of data easy. However, if you were performing this task repeatedly, creating a script would be more effective because it could be reused quickly and easily. You create a script file by saving the BULK INSERT statement to an .sql file.

Export Wizard

You can use the Export Wizard to export data from the database into external data destinations. Unlike BCP, the Export Wizard can export data to destinations other than data files. To use the Export Wizard, follow these steps:

1. In Enterprise Manager, expand a server group and click the name of the server you want to export data from. From the Tools menu, choose Wizards. In the Select Wizard dialog box that appears, expand the Data Transformation Services folder, click DTS Export Wizard, and then click OK. Alternatively, right-click the name of the server, point to All Tasks, and then click Export Data. The Data Transformation Services Import/Export Wizard welcome screen appears, as shown in Figure 24-13.

   

   Figure 24-13. The Data Transformation Services Import/Export Wizard welcome screen.

2. Click Next to display the Choose A Data Source screen, shown in Figure 24-14. This screen lets you specify a data source. You can keep the default setting, Microsoft OLE DB Provider For SQL Server, or you can select Microsoft ODBC Driver For SQL Server. Either option will connect to SQL Server. Other options can be used to export data from other database products. Next select a database—in this case, the Northwind database. You can also set advanced options, such as the connection timeout, network address, net-libraries, and workstation ID, in the dialog box that appears when you click Advanced. These options typically do not need to be modified, however.

   

   Figure 24-14. The Choose A Data Source screen.

3. Click Next to display the Choose A Destination screen, shown in Figure 24-15. The screen options will vary depending on the data type of the destination you choose; however, in most cases, you will be required to enter logon and file information. In this case, we'll select Text File as the destination, which does not require any logon information, so that we can save the database table in text form. Type the name of the destination file in the File Name textbox.

   

   Figure 24-15. The Choose A Destination screen.

4. Click Next to display the Specify Table Copy Or Query screen, shown in Figure 24-16. This screen simply lets you specify whether the entire table will be exported or whether the export will be performed by using a query. If you had chosen another SQL Server database as the output destination, a third option—Copy Objects And Data Between SQL Server Databases—would be available.

   

   Figure 24-16. The Specify Table Copy Or Query screen.

   If you click Use A Query To Specify The Data To Transfer and click Next, the Type SQL Statement screen will appear, as shown in Figure 24-17. Here you can type the SQL statement that will select the data you want to export. This query can select subsets of columns or rows, or it can select the entire table, as shown in this example.

   

   Figure 24-17. The Type SQL Statement screen.

5. Click Next to display the Select Destination File Format screen, shown in Figure 24-18. (The Source drop-down list does not appear when you access this screen from the Type SQL Statement screen.) Here you can specify several formatting options for the destination file, including whether the file should be a delimited or a fixed-field file. When you are finished selecting the formatting options, click Next.

   If you click Copy Table(s) And View(s) From The Source Database in the Specify Table Copy Or Query screen and click Next, the Select Destination File Format screen will appear (Figure 24-18). (In this case, the Source drop-down list appears.) In this screen, you select the source table and you select formatting options for the destination file. When you are finished making your selections, click Next.

   

   Figure 24-18. The Select Destination File Format screen.

6. After you click Next in the Select Destination File Format screen, the Save, Schedule, And Replicate Package screen appears, as shown in Figure 24-19. Here you select when you want to run the job and whether you want to save a DTS package for future use. This screen is similar to its counterpart in the Import Wizard.

   

   Figure 24-19. The Save, Schedule, And Replicate Package screen.

7. Click Next to display the Completing The DTS Import/Export Wizard screen, as shown in Figure 24-20. Click Finish to run the export.

   

   Figure 24-20. The Completing The DTS Import/Export Wizard screen.

8. Once you click Finish in the Export Wizard, the data export process begins. The Executing Package screen appears (Figure 24-21), as it does in the Import Wizard. A message box then appears telling you whether the job has succeeded or failed.

Both the Import Wizard and the Export Wizard are easy to use and configure and can make a sometimes difficult job easier. But remember that if you will be performing these operations repeatedly, it is worth the extra effort to script them. You can create a script containing a BULK INSERT T-SQL statement to perform an import operation, and you can use a SELECT statement, with the output redirected to a data file, to handle an export operation.

NOTE

---

While we used a text file-to-database table transfer in the preceding Import Wizard example and a table-to-text file transfer in the Export Wizard example, these wizards support many other types of data transfers. The wizards are especially useful for transferring data between databases or other entities.

Figure 24-21. The Executing Package screen.