Transform Invalid Data Formats in SQL 2000 DTS with ActiveX Script

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

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.

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author MSSQLTips was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms