Using Data Transformation Services, part 3: Using the DTS Designer Objects (continued)

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
{mospagebreak title=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. 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. For details refer to the previous tutorial. Create a package called FileCreated.

Creating an SQL Server connection

We will 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.

 

{mospagebreak title=Creating a Destination text file connection}

From the Connection menu item, select the TextFile (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 as shown in the next picture. Additionally, create an empty text file, TextDts.txt, and place it in a suitable location on the C: drive.

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

Configuring the Transform Data Task

As described in the previous tutorial, connect the two connections by a Transform Data task from the SQL Server to the TextFile. 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. 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.

The Transform Data Task Properties window has several tabs but opens with the Source tab to start with. While it is possible to use the Table/View as is, an SQL Query can be set up as shown. A simple select statement selects the LastName, FirstName, HireDate, City, and PostalCode from the Employees table.

 

It is also possible to construct the query interactively by choosing the SQL Query radio button, and  clicking 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.

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 as shown.

 

{mospagebreak title=Set up an ActiveX Script Task to access the text file}

The ActiveX Script Task can be chosen from the menu item Task. Click on Task to choose the ActiveX Script Task from the drop down menu. It can also be chosen from the Task collection icons as shown in this picture.

Drag and drop the ActiveX Script object on the editor’s design pane (named as HelloDTS in this tutorial). After adding this object the design pane appears as follows:

 

The addition of the ActiveX scripting object opens up the ActiveX Script Task Properties window, where the scripting has to be carried out, as shown. It pops up the default function Main. The default function always produces a success. Although the default language is VB Script, several other languages can be chosen as shown in the dropdown box.

 

The following picture shows the functions which are accessible using PerlScript. For this tutorial the default VB Script is used.

 l

{mospagebreak title=The FileSystemObject}

Since we are dealing with the text files that will be stored on the computer, a way must be provided to access the files. The FileSystemObject is a very flexible object that provides methods and properties for looking deeply into the local folders and files. The FileSystemObject can be accessed by using the CreateObject method as shown here:

set fsys=CreateObject(“Scripting.FileSystemObject”)

Once created, the methods and properties of the object provides flexible access to the files and folders.

The HelloDTS ActiveX Script task has the following script:

‘*********************************************
‘ Visual Basic ActiveX Script
‘*********************************************

Function Main()
Call testfile
Main = DTSTaskExecResult_Success
End Function

sub testfile()
1 set fsys=CreateObject( “Scripting.FileSystemObject”)


2 ‘msgbox(fsys.FileExists(“C:Documents and Settingscomputer userMy DocumentsDevShedDTSDTSTEXTTextDts.txt”))
 

3 set file=fsys.GetFile(“C:Documents and Settingscomputer userMy DocumentsDevShedDTSDTSTEXTTextDts.txt “)


4 msgbox(file.name & vbcrlf & file.DateCreated
& vbcrlf & file.Size)


end sub

The line numbers have been added for the sake of explanation, and are not created by the ActiveX Script Control. The function Main calls the procedure testfile. In line one, a FileSystemObject is created. An empty file TextDts.txt is then verified to exist in the second, commented out line, which returns true. In line three, the variable file is set pointing to the TextDts.txt file. The fourth line returns the name of the file, the date it was created and its size through a message box as shown below.

Modify the script task to copy the above file to a text file.

The Data Transformation task goes from source to destination. The TextFile (Destination) we created is of the destination type. Since we need to further process this text file to yield another text file, this file needs to be copied to a new file so that the copied file can become a source file. This is accomplished by adding the following lines to the testfile procedure above:

5 fsys.CopyFile file, “C:Documents and Settingscomputer userMy DocumentsDevShedDTSDTSTEXTDestin.txt”
 

6 set file2=fsys.Getfile(“C:Documents and Settingscomputer userMy DocumentsDevShedDTSDTSTEXTDestin.txt”)


7 file2.Attributes=32

The fifth line copies this file to another file called Destin.txt. The sixth line sets up file 2 to point to this Destin.txt file. Line seven sets up the file attribute of Destin.txt to archive (otherwise Read-Only). If the package were to be run at this stage (the SQL Server connection, the TextFile [destination] and the ActiveX Script task), the file TextDts.txt would be created, followed by the copying of the file to a newly created Destin.txt file at the desired location. Destin.txt is an exact copy of TextDts.txt, but it is not a destination type file in the context of DTS.

{mospagebreak title=Create a source text file}

Create a new TextFile (source).. called FirstCopy by following the same procedure as before. In the property page of this connection, make the following entries. For file name use the ellipsis button to locate the Destin.txt created in the previous step. The result after processing Destin.txt will be transferred to Destin2.txt (SecondCopy).

Create a destination text file

Again from the menu connection drag and drop a TextFile (Destination). Set its description attribute as SecondCopy. The file name is Destin2.txt, which is created in a convenient location. Now browse using the ellipsis button to locate this file, while configuring its properties as shown below.

With these added elements, the editor’s design pane should have the following elements. The filtered data from SQL Server will be transferred to the Text File. The HelloDTS ActiveX Script Task creates a copy of the TextDts.txt in the computer. The FirstCopy is pointing to the copy. A separate text file, SecondCopy, is created to receive the filtered data from the FirstCopy.

However, there is just one problem with this arrangement. Both FirstCopy and SecondCopy must exist, even if they are empty, before the Transformation Task from FirstCopy to SecondCopy can take place. However, HelloDTS creates the FirstCopy after execution. So to start with, FirstCopy does not exist. We will resolve this after configuring the mapping from FirstCopy to SecondCopy.

{mospagebreak title=Create a Transform Data Task}

We will use the Data Transformation task to modify the mapping from FirstCopy to SecondCopy. Place the cursor on the connecting link from FirstCopy to SecondCopy, and when it changes to an East-West pointing cursor, right click to look at the properties of the Data Transformation task as shown below. The Data Transformation Properties window opens with the default Source tab as shown. This is the FirstCopy text file.

Clicking on the Destination brings the following into view. This is the SecondCopy corresponding to Destin2.txt.

The next tab is the Transformations tab. Clicking this tab reveals the following, where we have deleted one of the columns from being mapped. The PostalCode has been dropped.

When we click OK to this, the SecondCopy will be created with the PostalCode field removed. It is possible to test the Task, edit and delete the task in this window. We assume that dropping a column is all that is required, and therefore click OK.

{mospagebreak title=Verify the results of transformation}

Case 1:

Destin.txt and Destin2.txt both exist and are empty. If the package is executed now, the result is as shown. There is an error in the Data Transformation task.

 One can look at the error message in the Event Viewer:

Event Type: Information
Event Source: DataTransformationServices
Event Category: None
Event ID: 80
Date: 05/03/2005
Time: 4:03:38 PM
User: N/A
Computer: XPHTEK
Description:
The execution of the following DTS Package succeeded:

Package Name: FileCreated
Package Description: DTSScript
Package ID: {61DC5E41-EB68-4A80-A84C-DDC92C8C8B7A}
Package Version: {0FE41506-7B8D-4038-A39C-6B9FBA2E21A7}
Package Execution Lineage: {E57FBB0A-56C8-40E4-90FC-123D101BDD5B}
Executed On: XPHTEK
Executed By: computer user
Execution Started: 05/03/2005 4:03:36 PM
Execution Completed: 05/03/2005 4:03:38 PM
Total Execution Time: 2.594 seconds

Package Steps execution information:


Step ‘DTSStep_DTSDataPumpTask_1’ succeeded
Step Execution Started: 05/03/2005 4:03:36 PM
Step Execution Completed: 05/03/2005 4:03:36 PM
Total Step Execution Time: 0.11 seconds
Progress count in Step: 9

Step ‘DTSStep_DTSActiveScriptTask_1’ succeeded
Step Execution Started: 05/03/2005 4:03:36 PM
Step Execution Completed: 05/03/2005 4:03:38 PM
Total Step Execution Time: 2.563 seconds
Progress count in Step: 0

Step ‘DTSStep_DTSDataPumpTask_2’ failed

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error creating datafile mapping: The volume for a file has been externally altered so that the opened file is no longer valid.

Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0

Step Execution Started: 05/03/2005 4:03:36 PM
Step Execution Completed: 05/03/2005 4:03:36 PM
Total Step Execution Time: 0.047 seconds
Progress count in Step: 0

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

Case 2:

Destin.txt does not exist; Destin2.txt exists, but is empty. If the package were to be executed now, you would get the following result. Since the FirstCopy does not exist to start with, the transformation fails.

However, after execution Destin.txt is created as a copy of TextDts.txt.

The execution of the following DTS Package succeeded:

Package Name: FileCreated

Package Description: DTSScript

Package ID: {61DC5E41-EB68-4A80-A84C-DDC92C8C8B7A}

Package Version: {0FE41506-7B8D-4038-A39C-6B9FBA2E21A7}

Package Execution Lineage: {F5B1F4A7-162E-43B1-933A-29950ACF3953}

Executed On: XPHTEK

Executed By: computer user

Execution Started: 05/03/2005 4:08:58 PM

Execution Completed: 05/03/2005 4:09:01 PM

Total Execution Time: 3.36 seconds

Package Steps execution information:

 

Step ‘DTSStep_DTSDataPumpTask_1’ succeeded

Step Execution Started: 05/03/2005 4:08:58 PM

Step Execution Completed: 05/03/2005 4:08:58 PM

Total Step Execution Time: 0.078 seconds

Progress count in Step: 9

Step ‘DTSStep_DTSActiveScriptTask_1’ succeeded

Step Execution Started: 05/03/2005 4:08:58 PM

Step Execution Completed: 05/03/2005 4:09:01 PM

Total Step Execution Time: 3.344 seconds

Progress count in Step: 0

Step ‘DTSStep_DTSDataPumpTask_2’ failed

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider

Step Error Description:Error opening datafile: The system cannot find the file specified.

Step Error code: 80004005

Step Error Help File:DTSFFile.hlp

Step Error Help Context ID:0

Step Execution Started: 05/03/2005 4:08:58 PM

Step Execution Completed: 05/03/2005 4:08:58 PM

Total Step Execution Time: 0.047 seconds

Progress count in Step: 0

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

In both cases, Destin.txt is created but Destin2.txt remains empty.

{mospagebreak title=Add a workflow item to rectify the package}

Highlight the HelloDTS ActiveX Script task and the FirstCopy connection, and add an On Completion Workflow as shown in this picture. This workflow specifies that, upon completing the HelloDTS task, the next Data Transformation task must be initiated.

Verify again after adding the workflow

Case 1: Both Destin.txt and Destin2.txt exist but are empty. Upon executing this package with the work flow added, the result is as shown. The Transform Data Task is now waiting, and when the message box is relieved, the transformation is completed as shown in the next picture.

Case 2: Destin.txt does not exist; Destin2.txt exists, but is empty.

The package is successfully executed without any error in any of the steps, similar to case 1 above.

Destin.txt file output:

Destin2.txt file output:

Summary

The DTS designer is mostly wizard driven and easy to work with.  Several new items in the Data Transformation Services designer were described. The TextFile (Source) and TextFile (Destination) and their usage was described. The ActiveX Scripting task was described with the help of a simple script to copy a text file from one location to another. Data Transformation in the context of text files was described again with a simple example. Adding workflow helps in a situation where a step has to be completed before the next is initiated, as shown in this tutorial.

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