Using Data Transformation Services, part 2: Using the DTS Designer Objects

In the first part, “Using Data Transformation Services,” we discussed a step-by-step method of creating a DTS package. This part elaborates on the use of the DTS designer for creating packages. We will walk through populating an MS Excel worksheet with a filtered subset of data.

In the previous tutorial, a step-by-step method of creating a DTS package was discussed, using both the DTS Import Export Wizard as well as the DTS Designer. However, to keep the discussion simple, the built in task, copy SQL Server Objects task was used. For quick and simple data transformation activities it is best to use the DTS Import Export Wizard. Although this tool can create packages, it cannot modify or edit existing packages. Also, the DTS Import Export Wizard hides the complexities of the underlying objects, but the DTS Designer will give you the full benefit of fine tuning the package.

This tutorial elaborates on the use of the DTS designer for creating packages. The discussion will focus on using the collections, connections and tasks. The scenario chosen for the data transfer effectively populates an MS Excel worksheet with a filtered subset of data from three of the tables in the example database, Northwind in the SQL 2000 Server as shown here:

  • Create a dts package
  • Create an SQL Connection
  • Create an MS Access Connection
  • Create a Transform Data Task
      This task creates a query to populate an MS Access Table.
  • Configure the task
  • Create a MS Excel Connection
  • Create a Transform Data Task
    • Create a query to filter the MS Access table
       to populate the Excel worksheet.
  • Configure the task
  • Save the package and execute
  • Verify the results of transformation
Creating the package: Opening the Editor

Expand the SQL 2000 Server node, right click on Data Transformation Services, and click New Package to open the DTS Designer as shown. A DTS related menu as well as file operation menu items are available. Also available are the two collections: connections and tasks. Each item in the collection is identifiable by placing the mouse on the icon. The Package, Connection, Task, and Workflow menu items, when clicked, reveal the choices available by dropdown boxes as shown in the next couple of pictures.

{mospagebreak title=Creating an SQL Server connection}

We will first start with the SQL 2000 Server. Click on the Microsoft OLEDB Provider of SQL Server in the dropdown menu after clicking on the Connection menu item, or drag and drop the item from the connection collection on the left-hand side onto the design pane. This opens up the Connection properties window as shown. Since we have chosen to work with the Northwind example database, we set the database to Northwind.

It is assumed that the SQL Server is local, and Windows Authentication is going to be used. This adds the SQL Server Connection. If needed, a text annotation can be added and placed in close proximity to the object. The text annotation can be accessed from the menu. The font properties of any added text are configurable.

Creating an MS Access Connection

Create an MS Access database in your file system. It is called the SQLAccessNew.mdb and it is located on the C: drive (there will be no user defined tables). Now in the DTS designer, drag and drop a MS Access connection. Browse using the button to locate the SQLAccessNew.mdb file on your system. The connection object now appears as shown.

{mospagebreak title=Creating a Task to transfer query result to a table}

From the Task menu item, select the Transform Data Task. The Select Source Connection pop-up text gets attached to the cursor. Place it on the SQL Server connection, and it immediately changes to Select Destination Connection; now place it on the MS Access database. The two databases get connected by an arrow pointing from the SQL Server to the MS Access as shown. The above sequence is shown in the next three pictures. The arrow represents the task that is added, and it remains to be defined.

Configuring the Transform Data Task

A text annotation in green, Transform Data Task, has been added. Place the cursor on the arrow (Transform Data Task arrow), and when it changes to arrows pointing North-South, right click to show the drop down menu. Click on the properties item. The Transform Data Properties window opens up with the Table/View default as shown. From this a table or view can be chosen. If you choose a table or view, it can be previewed using the Preview… button. From this menu it is also possible to execute the task (step) as well configure workflow properties.

Choosing the SQL Query radio button, click on the Build Query… button. This opens up the Data Transformation Services Query Designer window as shown. The DTS Query builder is not the same as the Query Analyzer, or the Query Builder in Visual Studio, but is similar in functionality.

Drag and drop the tables, Orders, Order Details, and Products and choose columns from the three tables. There is no specific reason for this choice other than illustrating the method. The Where clause was added to the SQL text as shown to get a reduced set of rows.

When you click to close the window, you are returned to the Transform Data Task window as shown. Here you can click on Parse query.. to test whether it has the correct syntax, or Preview.. the data which shows up in a View Data table (not shown here).

Clicking now on the Destination tab pops up the Create Destination Table window as shown. The results from the query are going to be loaded into a table with the default name New Table as shown here.

You may change the name of the table into something meaningful by changing the string “New Table” to something else. Here the default name is accepted. This table structure will be created in the SQLAccessNew.mdb database when the package is executed. At this point, the entire query output is going into the Access table. When you click OK to this Create Destination Table window, you will see the following picture.

The next tab to work on is Transform. When you click on this, the following window shows up with default mappings. The six column mappings can be changed by selectively clicking on the connecting line from source to destination. We will look at transformations in another tutorial; for now, accept the default by clicking OK. We also give a name to the Transform: MapSQLACC. Now if this package were to be executed, the table will be filled with the results of the query.

Using the file menu, the package can be saved with a name. This package is saved as MSA. To save to local server, you may need to provide authentication information. The default shown here should be sufficient for this example.

{mospagebreak title=Creating a connection to MS Excel}

Create an MS Excel file (called MSA.xls in this example) in your file system. Drag and drop an Microsoft Excel 97-2000 connection from the Menu item, or from the Connection Collection on the left of the designer. This brings up the following window. Using the ellipsis button locate the MSA.XLS file. This of course assumes a New Connection.

Adding another Transform Data Task

Very similar to what we did earlier, we add another Transform Data Task. This time we start with the MS Access connection to the MS Excel connection. Each individual step is not shown, as the process is exactly the same. Now the three data connections with the two transform data tasks will appear like this in the DTS Designer.

{mospagebreak title=Configuring the Transform Data Task}

Place cursor on the Transform Data Task and when it turns into an arrow pointing East<->West, right click the Transform Data Task and select properties. This brings up the following window with several tabs, starting with the Source tab. You may add a description to the Task.

Since further filtering will be carried out instead of a Table/View choice, the SQL query choice is made. This will require you to build a query. Click on the Build Query… button. This brings up the Data Transformation Services Query Builder Window. The list of tables on the left includes not only the New Table created for the MS Access connection, but also the various system tables. Drag and drop the New Table into the Query Builder design pane. Make the choices. A where clause has been added to the SQL statement, which shows up as a filter in the design pane. Also, note that fewer columns from the MS Access table has been specified by this query.

Click OK to this query Builder. This adds the SQL statement to the Source window as shown here. You may test Parse query… to verify the correctness of the query.

Click on the Destination tab, which will opens the window pertinent to the third connection, namely the MS Excel Connection. In the Table Name it comes up with the first worksheet, Sheet1$, and just one field, F1. You may also switch to other sheets, or create a new sheet (sheet is synonymous with table). We assume the default Sheet1$. Now click on the Create… button, which brings up the Create Destination Table window as shown. You can see all the choices you made in the query. If you click OK to this, you will be adding a new work sheet called New Table to your MSA.xls file.

Click OK to the above window, which now shows the meta data that is going into the Excel file as shown here.

Click on Transformations, which takes you to the next window with default transformations. This shows a one to one mapping of the source query to the Excel sheet. Click OK to this to complete the task definition. Now go to menu item Package and save. This will be saved under the same name as the previous save, namely, MSA.

{mospagebreak title=Executing the package and verifying the transfer}

As mentioned in Part 1, there are several ways to execute this package. Click Package in the DTS Designer and, from the drop down, click on Execute. This initiates the execution of the defined package in the DTS editor. In this case, the package MSA will be executed and the window processing the steps (tasks) shows up as follows, with another window showing up either success or failure. The first task has processed some 164 rows and the second step has processed some 17 rows of data.

Locate the SQLAccessNew.mdb file and open it. The New Table table should be present. When the table is opened the imported files from the SQL Server may be seen as follows:

Similarly, locate the MS Excel file, MSA.xls, and open it to reveal the contents shown in the next picture. You may also verify that dts created the table name you gave and populated the filtered data that you chose.

It is possible to log errors to a file as well as monitor completion status in the event log by appropriately configuring the properties window of the package as shown.

Here is an example of what is logged into the event viewer after execution of a package.

The execution of the following DTS Package succeeded: 

Package Name: MSA
Package Description: (null)
Package ID: {9D446B7C-0E22-4B93-8CA8-18B6B7FB2305}
Package Version: {836733BE-482F-4517-9EED-A34FF8979427}
Package Execution Lineage: {4D72E66A-B655-4C2F-A947-F332C1E0324D}
Executed On: XPHTEK
Executed By: computer user
Execution Started: 3/27/2005 7:28:04 PM
Execution Completed: 3/27/2005 7:28:12 PM
Total Execution Time: 7.25 seconds

Package Steps execution information:


Step ‘DTSStep_DTSDataPumpTask_1’ succeeded
Step Execution Started: 3/27/2005 7:28:05 PM
Step Execution Completed: 3/27/2005 7:28:08 PM
Total Step Execution Time: 2.969 seconds
Progress count in Step: 164

Step ‘DTSStep_DTSDataPumpTask_2’ succeeded
Step Execution Started: 3/27/2005 7:28:08 PM
Step Execution Completed: 3/27/2005 7:28:10 PM
Total Step Execution Time: 2.75 seconds
Progress count in Step: 34 

For more information, see Help and Support Center at 
http://go.microsoft.com/fwlink/events.asp.
Summary

The DTS designer is mostly wizard driven and easy to work with. The query builder, although it looks similar to the Query Analyzer, is not the same. Several items in the Data Transformation Services designer have not been discussed. These will be considered in a future tutorial.

No errors were encountered in this step-by-step procedure, but this is not always the case. The errors can be written to a log file. On the completion of the execution of the package, a completion status report can be written to the Event Viewer. It should be noted that a second time execution of the package appends the results and care should be taken to consider this possibility. Text annotation should help in documenting the package details.

9 thoughts on “Using Data Transformation Services, part 2: Using the DTS Designer Objects

  1. This is a good article about using the DTS Designer to create DTS packages, but it only covers the absolute basics. DTS is an extremely powerful tool and this article overlooks that. It is essentially a copy of the Microsoft Help file about DTS.

  2. Everything about DTS is covered by Microsoft, including this one. While this covers the absolute basics, there are many other DTS articles at ASPFREE, in fact over a dozen of them, which covers aspects of DTS which are not so basic. Thanks for the comments.

  3. have read your articles regarding DTS, they are very useful, but still i have some queries.

    My Problem.

    I Have User Table on server1 and User Table on server2. (Both the table have different fields only some fields are common.)

    Now our client used server1 primarly for their intranet, so every night we have to update user table on server2.
    If new records insert on server1 we have to insert new , if update existing we have to update that and if delete we have to delete.

    Please let me know how to do that?

    Awaited for your early reply.

    Thanks & Regards,
    Dhawal Mehta
    Sr. Software Engineer

  4. You seem to have two different servers, and looks like you need to write a trigger assisted, distributed query. Another way would be: Since it is event driven from Server1 to Server 2, you may have to set up some triggers and a job on Server1 which populates a temp table on Server2 where you may have a DTS doing the population of the table on Server2. I am not too sure that this is the best way of doing it. Are you a developer or a dba?

  5. I have read your articles and they are very useful,but still i have some queries.

    My Problem.
    I have multiple inbounds text file which contains different columns like 45,50,57 etc.
    My problem
    Is when i am mapping columns source to destination for first time it is working perfectly.But next i am changing the source file in run time which may contain 40 or 50 columns, Suppose first i mapped 45 columns and after that i changed the my source file which contains 50 columns,then i am executing the package at that time i got error like column not found.But in the destination table have more column then the source.

    Please let me know how to maps source column into destination column run time ?

    Awaited for your early reply.

    Thanks & Regards,
    Jagannath Murty
    Software Engineer

  6. The options I suggested are just possibilities. Can they work? I need to test them myself before I can give you the details. If I find time to test, I will and let you know.
    Triggers respond to data changes, for example. Since two servers are involved, you need a distributed query. These are general ideas and they have to be combined to get a solution for your problem.

  7. Hi, I found your DTS lesson very helpful; I looked for a decent tutorial for DTS in the SQL Server on-line books, but found nothing; perhaps I overlooked something. I found plenty of documentation on different parts of DTS, but nothing for a beginner to get off the ground quickly. Did I miss something or did Microsoft blow it on this one? Also, same applies to SQL Server 2005 and SSIS; I found NO TUTORIAL, just a bunch of doc about the different SSIS components! Very annoying!
    Having done your Access/SQL Server lesson, I need to keep learning more, quickly, about DTS and/or SSIS; What do you suggest?
    Thanks.

  8. It is a very good stuff related to Data transformation servcies.
    It gives you the basic idea of creating DTS packages and configuring them.

[gp-comments width="770" linklove="off" ]