Using Data Transformation Services, part 6: Intro to Text Transfer with Filtering
(Page 1 of 5 )
In this tutorial, Jay describes the transformation of data from one comma delimited text file to another. Sometimes applications require transfering information from one text file to another, making a subset of the original with certain constraints. The subset is based on a query of the original text, but there is no built-in support for querying text file connections. It is possible to make this transfer by using an intermediate step, which this article focuses on.
Desired Transformation:
The following picture shows the few rows of the original text file called, textTrans.txt.
You must create a text file populated by rows with a first column containing the value 20. The column ordering must be as shown:
The steps required to create the destination file
The original text file is ported to a table in the SQl 2000 Server in the first step. In the second step, the table is queried to furnish data to populate a destination text file after some column transformations. The following steps are needed to accomplish this task.
- Create a dts package
- Create a source file connection
- Create an SQL Connection
- Create a Transform Data Task
- This task populates a table in the SQL 2000 Server.
- Create a destination text file connection
- Create a Transform Data Task
- This task creates a query to populate the text file.
- Verify package results
Next: Creating the package >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
