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

(Page 1 of 6 )

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.

Next: Creating an SQL Server connection >>
 

More MS SQL Server Articles
More By Jayaram Krishnaswamy

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

· This is a good article about using the DTS Designer to create DTS packages, but it...    · Everything about DTS is covered by Microsoft, including this one. While this covers...    · have read your articles regarding DTS, they are very useful, but still i have some...    · You seem to have two different servers, and looks like you need to write a trigger...    · Hi Jayaram,I am developer, i am not getting what you exactly saying?Please can...    · I have read your articles and they are very useful,but still i have some...    · The options I suggested are just possibilities. Can they work? I need to test them...    · Hi, I found your DTS lesson very helpful; I looked for a decent tutorial for DTS in...   >>> Post your comment now!