Using Data Transformation Services, part 6: Intro to Text Transfer with Filtering

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

{mospagebreak title=Creating the package}

Opening the Editor

Expand the SQL 2000 Server node and right click on Data Transformation Services and click New Package to open the DTS Designer (please see the previous tutorials for this step). DTS related menu as well as file operation menu items are available. Also available are the two collections, connections and tasks. Create a package called TextDTS.

Create a source file connection

Select a Text File Source connection from the collection of connections (also review previous tutorials on this topic) and drop it on the DTS Designer’s design pane. This brings up the dialogue for configuring the text file connections as shown in the next picture. Locate the input file on the hard drive by using the Browse… button as shown. This completes the source connection.

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 after clicking on the Connection menu item, or drag and drop the item from the connection collection in the left-hand side on to the design pane. This opens up the Connection properties window as shown. Since we have chosen to work with TestWiz database (any database can be used), we set the database to TestWiz. 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.

{mospagebreak title=Creating a Transform Data Task and a Destination Text File Connection}

Create a Transform Data Task

From the collection of connections and tasks, pick up a Transform data task and place it on the two connections, source connection first and destination connection [SQL Server], next. Now the designer pane should appear as shown here.

Now right click on the Transform Data Task and choose to look at the Properties from the pop-up menu. This brings up the Transform Data Task Properties window with multiple tabs for Source, Destination, Transform, etc. as shown here.

For the destination we will be populating a newly created table TestDts in the TestWiz database in the local SQL 2000 Server with the data from the source text file. We assume a simple column transfer, that is, column 1 in the text file goes over to column 1 in the table, and so on as shown. For more details please review the earlier tutorials in this subject.

Creating a Destination text file connection

From the Connection menu item, select the Text File (Destination)… and drag it to the design pane of the editor. The editor’s design pane now contains the SQL Server Connection as well as the TextFile (Destination) connection. Also create an empty text file TransText.txt located at a suitable location in the C: drive.

The connection properties for the TextFile (Destination..) is as shown in the next picture. Browse with the ellipsis button (…) to point to the TransText.txt created earlier. This becomes the destination for the results from the TestWiz database.

{mospagebreak title=Configuring the Transform Data Task}

As described in the previous tutorials connect the SQL 2000 Server with the destination text file created in the previous step by a Transform data task as shown:

Place the cursor on the arrow (Transform Data Task arrow) and when it changes to arrows pointing North-South (or East-West) right click to show the drop down menu. Click on the properties item. The Transform Data Properties window opens up with the Table/View as default as shown. Now choose to use a SQL query. This query can be fashioned in the editor as described in the earlier tutorials. Now create a query as shown here by entering the text, or using the GUI support in the editor. This query selects the three columns only if the column value is 20.

Now the source for the transformation is the SQL Server connection and the destination is the TransText.txt file. Open the Transform Data Task window and rearrange the column mappings from table/column1 to TransText.txt file’s column1 to the ones shown here. Here you can click on Parse query.. to test if it has the correct syntax, or Preview.. the data using View Data . This completes the design of the package. This can be saved using the Save button. It can be saved in the SQL Server, or repository (the Meta Data Services node) in SQL Server’s Data Transformation node, or to a file on the desired location as a *.bas file. In the present tutorial it is saved as a TextDts package and also as TextDts.bas file.

{mospagebreak title=Verification Package Results}

The package can be executed in any number of ways described in earlier tutorials. The easiest, while in editor is to click on Package, and then click Execute. This executes the package as shown here:

Now locate the TransText.txt file and open up the file in a text editor, such as Notepad. The result will be as shown in the next picture. This was indeed the desired transformation.

Summary

The DTS designer is mostly wizard driven and easy to work with. The querying of a text file in the DTS designer is not supported and hence an intermediate stage of porting the data to a table was devised. Now being a table, the full force of SQL querying can be used. The result was achieved without writing a single line of code (except for the Select statement in the query). The result of the query was then used to populate the destination text file. Another possible approach would be to use the file system object, read the input file, filter the file and then populate the destination table.

2 thoughts on “Using Data Transformation Services, part 6: Intro to Text Transfer with Filtering

  1. The article was really useful. But I face a different problem. I want to transfer a file into another which is more than using filtering. the second table should have one rows for corresponding four rows( having some common key) in the primary table i.e concatenate four rows in the first table into a single row using a key.

  2. You have to be more explicit for em to look at. The objective of the article is to give you some kind of handle to tackle. It is not addressing a specific problem.
    If you provide more information I may be able to help.

    sincerely,

    Jayaram Krishnaswamy

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