Using Data Transformation Services, part 3: Using the DTS Designer Objects (continued)
(Page 1 of 9 )
In this third article covering Data Transformation Services, we discuss the transformation of data from an SQL server to text files. We will add work flow to improve the staging of the conversion. As with the other articles in this series, the process is described in a step-by-step fashion.
Introduction This tutorial elaborates on the use of the DTS designer for creating packages. The discussion will focus on using the collections, connections tasks, and workflow. The scenario chosen for the previous tutorial on using DTS designer objects effectively populated 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.
In this tutorial, the transformation of data from an SQL Server to text files will be described. The text file so created will be accessed by the ActiveX Script Task to copy the file to a destination file. The destination file becomes the source file for a text file-to-text file conversion scheme with a simple transformation. Adding work flow improves the staging of the conversion. The following steps need to to be taken to accomplish this task:
- Create a dts package
- Create an SQL Connection
- Create a destination text file connection
- Create a Transform Data Task
This task creates a query to populate the text file.
- Configure the task
- Set up an ActiveX Script task to access the text file
- Modify the script task to copy the above file to a text file
- Create a source text file
- Create a destination text file
- Create a Transform Data Task
This task maps the fields from one text file another
- Verify the results of transformation
- Add a workflow item to rectify the package
- Verify again after adding the workflow
Next: Creating the package: opening the editor >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
