- DTS Overview
- DTS Packages
- Developing DTS Packages
- References
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Developing DTS Packages
To illustrate the process of developing a DTS package, we'll use the sample Northwind database included with SQL Server 2000.
Suppose a Northwind customer in France asks the Northwind database administrator (DBA) to provide the following:
He wants a catalog of all products with all the prices in Euros.
He needs the product ID and the product description fields to be merged.
He wants to load the catalog into a proprietary database system, using a flat text file.
The Northwind database administrator will use a DTS package to extract, transform, and export the desired data into the desired format.
Assuming that you are the Northwind DBA, here's how you'd do all that.
Creating the Flat Text File
You'll begin by creating the flat text file, into which you'll load the transformed data. To create the flat text file, you need to use a source Connection object to read data from the SQL Server database, a destination Connection object to write the data to a text file, and a Transformation object to read only the needed fields and make the required calculations:
To create the package, open SQL Server Enterprise Manager, expand Data Transformation Services under the desired server, and select Local Packages.
To create a new package, open the Action menu and select New Package. The result is an empty DTS Designer.
Every DTS package needs source and destination Connection objects. The destination connection can use the same database as the source connection; a different database; a different location (text files, spreadsheets, third-party databases, and so on); or a Microsoft Analysis Services OLAP database. For this example, in the left pane under Connection, click the Microsoft OLE DB Provider for SQL Server icon.
In the Connection Properties dialog box, select New Connection and enter the name Products Catalog in the text box. Then select Windows Authentication, select the Northwind database from the database list, and click OK.
Now that you have a connection to the Northwind database, you need to create a connection to a text file. In the left pane under Connection, click the Text File (Destination) icon. Select New Connection and enter the name Text Catalog in the text box. Enter the filename c:\catalog.txt. (This is the file to which the DTS package will export the data.)
-
The next step is to create a Transform Data Task to read the desired data from the Northwind database, make the necessary changes, and send the data to the destination Connection object. To create a Transform Data Task between the two connections, select both connection objects; in the left pane under Task, select Transform Data Task. A task arrow appears between the source connection and the text file connection (see Figure 2).
-
Now you need to edit the Transform Data Task properties to define the desired fields from the source database, the fields for the destination text file, and the mappings between them. For this example, because you're using a single table from the source database, you can simply tell the Transform Data Task which table to use. (If you need to use several tables, you can enter a T-SQL statement.) Right-click the task arrow and select Properties to display the Transform Data Task properties (see Figure 3).
In the Description text box, enter Export Catalog. In the Table/View list, select the table [Northwind].[dbo].[Products].
Click the Destination tab. The Define Columns dialog box appears, in which you can specify the datatypes for each column or allow SQL Server to create the mapping for you. To allow SQL Server to do the mapping, click the Populate From Source button.
Click the SupplierID field, click the down-arrow button to the right of the field name, and click Ignore. The Northwind client doesn't need this information. Repeat this step for the CategoryID, UnitsInStock, UnitsOnOrder, ReorderLevel, and Discontinued fields.
Click Execute.
The Data Transformation Services are common to all the databases stored in the server. This means that a single DTS package can access the data from one or more databases. When you select Local Packages, you get a list of all the packages available on this server.
NOTE
The Properties button opens a dialog box in which you can specify the format options of the text file. If you don't click the Properties button, the text file properties dialog box opens automatically when you click OK.
You should now have two connection objects in the DTS Designer window. You can arrange the objects by dragging them to any position.
)
Figure
2 The DTS package.
)
Figure
3 The Transform Data Task Properties dialog box.
Merging Fields from the Database
Because the Northwind customer requested a field that's not already defined in the Northwind database (a merged product ID and product description), you need to modify the field mappings created by the Transform Data Task. You have to merge the ProductID and ProductName fields from the Northwind database and then copy this information to the ProductID field in the destination text file.
To accomplish this task, you can use an ActiveX script. ActiveX scripting is very similar to VBScript; the main difference is that it incorporates statements to reference the different objects of the DTS package. For this example, you'll use DTSSource, DTSDestination, and DTSTransformStat_OK to reference the source fields, destination fields, and the Transform Data Task object, respectively. In the Transform Data Task Properties dialog box, click the Transformations tab and follow these steps:
Select the Transformation arrow that goes from ProductID (source) to ProductID (destination) and click the Delete button.
Click the New button. The Create New Transformation dialog box appears.
Select ActiveX Script and click OK. The Transformation Options dialog opens.
In the Name text box, enter Merge_ID_Name.
Click the Source Columns tab. You need to tell the ActiveX transformation which fields are available. In the left pane, double-click ProductName. Now the field should be in the right pane. Click OK.
On the General tab, click the Properties button.
In the right pane of the dialog box, change the VBScript function Main() with the following code:
Click the Test button to check that you entered the code correctly.
Click OK when the test is complete. Click Done and then OK to go back to the Transformations tab.
Function Main()
DTSDestination("ProductID") = DTSSource("ProductID") & "_" &
[ccc]DTSSource("ProductName")
Main = DTSTransformStat_OK
End Function
Converting the Unit Price
The customer's wants you to convert the unit price of the data from U.S. dollars to Euros. You can do this with an ActiveX transformation:
Select the Transformation arrow that goes from UnitPrice (source) to UnitPrice (destination) and click the Delete button.
Create a new ActiveX transformation with the name Euro_Conversion and change the VBScript function Main() with the following code:
Click the Test button to check that you entered the code correctly. Click Done and then OK until you get back to the DTS Designer editor.
From the Package menu, select Save. For Package Name, enter Export Product Catalog; then click OK.
Close the package.
-
To execute the Export Product Catalog package, right-click the package and select Execute Package (see Figure 4).
Function Main()
DTSDestination("UnitPrice") = Round(DTSSource("UnitPrice") / 1.18119, 2)
Main = DTSTransformStat_OK
End Function
)
Figure
4 Package execution.
After the successful execution of the package, you'll be able to locate the file c:\catalog.txt and review its contents.