Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Transform Invalid Data Formats in SQL 2000 DTS with ActiveX Script


By:   |   Read Comments   |   Related Tips: More > Data Transformation Services

Problem
Date formats don't always come in the acceptable formats used by SQL Server, requiring transformation prior to bringing the data into the database. The use of views to transform date formats can be done but requires moving the data twice-once to the working table and again to the production table. This may cause performance issues. Another method for transforming data is through the use of ActiveX transformations.

Solution
ActiveX transformations use scripting languages like VBScript and JScript to apply custom features to tasks and transformations in DTS. In this case we will talk about VBScript. When it comes to transformations, use can manipulate the column of the incoming string to match whatever column the data will eventually reside in. Here are some examples of string manipulations:

  • left("column_name", "number_of_characters")
  • right("column_name", "number_of_characters")
  • mid("column_name", "starting_character", "number_of_characters")

Note that left and right designations are the same as they are for T-SQL statements; the third one, however, is a little different. Mid is the equivalent of substring in T-SQL. Now let's look at a situation where ActiveX transformations will help us fix an invalid date format.

We have our list of concert dates we want to import so we will be reminded of the great ones coming up. Some of the dates look acceptable but others don't, so we run the following query against one of the strange looking ones:

Using the IsDate statement to evaluate the date string

Since it's not one of the acceptable date formats in SQL Server 2000 we will have to transform it using ActiveX. We do see, however, that it is consistently ten characters, which makes it easy to transform. Once we open a new DTS package in Enterprise Manager we create a text file source:

Creating a Text File (source) connection

Setting Delimiter and Qualifier in Source connection

Preview of data in Text File (source) connection

We create the connection to the destination database and choose Transform Data Task from the Task menu:

The Transform Data Task

Double-click the line now connecting the source and destination connections. The particulars of the Transform Data task appear. When you click on Transformations some or all of the transformations will be automatically created. We click Delete All and decide to create our own. When we drag the source column from the left side to the destination column on the right we have a choice of transformations-we choose ActiveX Script:

Choosing the ActiveX Script transformation

We then click properties to open the script window:

Opening the ActiveX Script window

When the script window first opens we see that a default transformation has been created for us already, which essentially says copy DTSSource("column 1") to DTSDestination("EventDate"):

Default transformation created

Since the date in the text file is formatted as "YYYY/DD/MM", we want to manipulate the date format to an acceptable one, so we concatenate the right two characters, sixth and seventh characters, and left four characters, with a "/" in between each string:

ActiveX script to transform incoming date

Once we are done we can click the Parse button on the bottom left of the designer window to ensure syntax is correct and click OK. We can now test whether the concatenation works by clicking the transformation line and choosing Test:

Testing the ActiveX Script Transformation

Since the rest of the columns in the text file are straight-forward, we can simply choose to Copy Column from the list of transformations.

Next Steps



Last Update:






About the author
MSSQLTips author Edgewood Solutions Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools