See Also: Main_Page - Code Management - Loaders - Data Transformation Services (DTS)

This example looks at the DTS Import / Export Wizard in action. You can start the wizard by choosing Start / Programs / Microsoft SQL Server / Import and Export Data. The initial screen informs you that you can transform data from a variety of sources using the DTS. Next, you are asked to select a data source.

The data source dropdown box lets you select the driver for your data source. The available options include MS SQL Server, Oracle, FoxPro, Dbase, Paradox ODBC and OLE DB drivers as well as spreadsheet files (in Excel format), text files and OLE DB provider for Analysis Services (which was referred to as OLAP Services in SQL Server 7.0). Alternatively, you could select "other (ODBC data source)" from the data source dropdown and specify a DSN you would like to use for connecting to your data source.

The security options you provide on this screen depend on the type of data source you are using. If your data source is SQL Server you'll have an option to connect using the Windows authentication or SQL Server authentication. If you use Windows authentication (available with Windows NT and Windows 2000 only), all you have to do is provide the name of SQL Server that you'd like to query. If you choose to connect through the SQL Server security, you will have to specify the server name, a valid user name on that server and password. Once you provide these security parameters you can select from the list of databases that are available for querying (depending on the user name that you provided). If you've used the SQL Server authentication and found that the user name you entered does not have access to the database you wanted to query, you can enter another user name and password combination and hit the REFRESH button. This will display the databases available to the new user name you provided. The Advanced button lets you view and modify additional connection parameters. You will not use this button 99% of the time you use DTS.

If you use desktop database systems such as MS Access as your data source, you will have to provide the full path to the database file, as well as the user name and password. If you use a spreadsheet or text file as your data source you'll have to provide the full path to the file, as well as some additional formatting parameters. The parameters you specify with a DTS text file data source are very similar to BCP and BULK INSERT parameters.

The next screen is for the destination data store. It is almost identical to the data source selection screen. The available destination data stores include the same database, spreadsheet and text files as with the data source selection. Therefore, you can use DTS even if you don't need to import or export data to and from SQL Server. For instance you could use a DTS package to import Oracle data into a MS Access database or load a text file data into an Excel spreadsheet. The following screen shows the summary information for the transformation you are about to execute.

If we were transferring data from one database to another, then this screen would have multiple tables and views under the SOURCE column. That's when the SELECT ALL and DESELECT ALL buttons are useful. However, for this example we are only transferring a text file, which appears as a single view in the SOURCE column. By default the wizard chose to populate the categories table in the pubs database on our SQL Server. We can leave this option as is, or click inside the DESTINATION column and select a different table. To preview the data that will be transferred select the row you wish to preview and click the PREVIEW button. Clicking the TRANSFORM button brings up a mapping screen.

The COLUMN MAPPINGS tab on this screen lets us specify the order in which we wish the columns to appear in the newly created (dbo.categories) table, as well as data types, null ability and size for each column. For appropriate data types we could also specify the precision and scale. The EDIT SQL button lets me modify the CREATE TABLE statement that will be used to create the destination table. This could be useful if you wish to modify the column names or collation settings for individual columns. However, for this example we will not modify the CREATE TABLE statement. We also have an option to drop the destination table if it exists before re-creating and populating it through the DTS package.

The TRANSFORMATIONS tab allows us to edit the transformation script in VBScript, or Jscript language. The BROWSE button lets uw import a pre-written transformation script from my hard drive or a network drive.

The next screen lets us execute the DTS package right away and / or schedule it for repeated execution at a later time(s). This screen is also where we specify the file format we wish to use for saving the DTS package. Notice that this screen also allows us to publish destination data by using replication.

If you click on the ellipsis button (…) next to the scheduling option, you will get a familiar screen that is used for scheduling all SQL Server jobs. You can schedule the DTS package to execute daily, weekly or monthly at a specific time of day, and provide the start and end dates for the package's execution.

If we choose to save the package, clicking NEXT will take us to the screen where we can provide the owner and user passwords for the package, it's name, and the SQL Server where it should be stored - if we select to store the package within SQL Server. Had we chosen to save the package as a Visual Basic (VB) file or a Structured Storage file, then we would only have to specify the full path to the file location. Notice that if we choose to save the file as a VB module, we cannot schedule the package. Also notice that only SQL Server allows specifying the user and owner passwords for a DTS package.

Clicking NEXT after saving the package takes us to a summary screen that repeats the options we have chosen for data source and destination, package name and execution schedule. Clicking the FINISH button on this screen executes the package and shows the execution status as. Along with the list of package steps, the wizard shows the step start time, end time and the total time it took to execute each step.