DTS Designer

DTS Designer is extremely easy to use and allows a user the flexibility of editing and manipulating any of the package properties in any order that is needed, as opposed to the strict sequential order of the DTS Wizard. You will find that after you have mastered all of the package concepts, you will be spending most of your time using DTS Designer instead of the wizard.

Because you have already created a DTS Package using the wizard, you will just open this package with DTS Designer to demonstrate its capabilities (see Figure 20.24).

Figure 20.24. DTS Package ”Design package option.

As you can see with DTS Designer, a main workspace pane and a palette of icons are to the left of Connection Properties and of Tasks (see Figure 20.25). Across the top are many of the same pull-down menu items along with the Workflow menu item. This is truly a point, click, and drag workspace. For anything in the workspace, you simply click on the icon, such as Connection 1 or Connection 2, to see its properties, or click on the solid line between Connection 1 and Connection 2 to see the transformation that is defined between them. The Delete from Table icon shows that a delete of rows for the destination table will be initiated after the connections are established.

Figure 20.25. DTS Designer workbench.

Figure 20.26 shows the properties of Connections and of Workflows.

Figure 20.26. Connection and Workflow properties.

When you click on the Transformation line between Connection 1 and Connection 2, you see some familiar properties that were originally set up with the DTS Wizard. Specifically, you can see the Source SQL Query that is pulling data out of the source Connection 1.

Because you are doing a transformation, the package cannot be saved without first defining this transformation with an ActiveX script. This is easy to do with DTS Designer and the ActiveX scripting capabilities. As you can see in Figure 20.27, you can simply name the ActiveX script in creating it for the first time or edit the one you need. It will be based on the source/destination picture on the lower portion of this dialog box.

Figure 20.27. SQL Query and Transformation Data Task properties.

The following ActiveX script depicts the source to destination copying for the transformation. All you do is specify your preference of what the script should be written in, and the script will be generated for you. It is now possible to modify the script as you see fit.

 '**************** VBcustomermap.txt ********************************  '  Visual Basic Transformation Script '  Copy each source column to the '  destination column '***************************************************************** Function Main()        DTSDestination("CustomerID") = DTSSource("CustomerID")        DTSDestination("CompanyName") = DTSSource("CompanyName")        DTSDestination("ContactName") = DTSSource("ContactName")        DTSDestination("ContactTitle") = DTSSource("ContactTitle")        DTSDestination("Address") = DTSSource("Address")        DTSDestination("City") = DTSSource("City")        DTSDestination("Region") = DTSSource("Region")        DTSDestination("PostalCode") = DTSSource("PostalCode")        DTSDestination("Country") = DTSSource("Country")        DTSDestination("Phone") = DTSSource("Phone")        DTSDestination("Fax") = DTSSource("Fax")        DTSDestination("YTDBusiness") = DTSSource("YTDBusiness")        Main = DTSTransformStat_OK End Function 

When you are at the Source tab of the Transform Data Task Properties, you can see that you can choose from several options with the SQL Query option. You can have DTS parse the query to make sure it has the correct syntax, you can preview its execution like you saw with DTS Wizard, or you can invoke the Build Query (Query Designer) option. Figure 20.28 shows you the graphical representation of the query that has been defined. You will find that this is a great facility to build up and visualize the SQL queries that you will be using in complex transformations. It is dynamic, in that when you make a change to the graphics or the SQL code, each is changed automatically.

Figure 20.28. DTS Query Designer.

Figure 20.29 shows the final version of DTS Package: Hot Customers Plus that has been saved into the SQL Server Metadata Services. A Send Mail task was added on successful completion, following the Transformation execution to complete the business requirement. Then it was executed as a test. The Send Mail Task had an error on it because the mail profile was not completed yet; however, you can see that the prior tasks of deleting from the destination table first, and then copying the result rows to the destination table, executed successfully. Mission accomplished!

Figure 20.29. DTS Designer and Package execution.

The choice of saving this Package in the Metadata Services automatically makes it available to more than just SQL Server. As you can see from Figure 20.30, the package is now part of the overall Data Warehousing Framework/Contents portion of the Metadata Services. Most of the Package properties can be edited directly though Enterprise Manager now.

Figure 20.30. Metadata Services storage of packages.

Saving to the Metadata Repository also allows you to audit all data transformations, including the user information and any errors in package execution. This auditing, referred to as data lineage, provides a powerful tool for monitoring data loads. The Metadata Services Repository provides the most versatile long- term method of storing DTS packages.

The Data Pump

The DTS data pump is an OLE DB provider that provides the interfaces and methods to import, export, and transform data from an OLE DB datasource to an OLE DB destination. The DTS data pump is the engine of the Transform Data task, Data Driven Query task, and the Parallel Data Pump task. The data pump's main goal is to move and transform data from one source to another.

You can use the DTS Designer to create a simple transformation using the data pump. In DTS Designer, you create two connection objects, select them both, and choose Add Transform from the Workflow menu. You can see the additional data pump phases depicted in the Transformations tab of the Transform Data Task Properties dialog box (see Figure 20.31). This is called Multiphase Data Pump Functionality. Advanced users can add the programs that customize the data pump at various phases of its operation. By this customization, a vast range of functionality can be added to a package. This is essentially giving the user a detailed chance to manipulate the process during the data transformation step.

Figure 20.31. Transform Data Task Properties.

Data-Driven Queries

In real life, loading things like data warehouse tables tends to be more complex than selecting data, transforming it, and then inserting it into the tables. Data-driven queries (DDQs) provide more flexibility than the Transform task but are row-based , whereas an insert-based data pump task is implemented as one insert statement or as a bulk copy. A DDQ loops through a resultset, taking actions as determined by conditions defined in an ActiveX script. DDQs can have up to four queries defined. These are defined as one insert query, one delete query, one update query, and one user query. One query is executed per row in the resultset, depending on the ActiveX script associated with the DDQ.

NOTE

Although the queries are named insert, update, delete, and user, these queries are not required. You can use any SQL statement. It is preferable to perform inserts with the insert query, but you could actually have three user-defined queries called from the ActiveX script as update, delete, or user-defined queries.

When you need more complex business logic to transform data, a DDQ is also a good candidate. In addition to the four queries that can be executed from the ActiveX script, you can write the row to an error log, skip the row in the insert, or abort the entire task. The action taken for the row by the data pump is determined by the DTSTransform_Stat constant.