By: MSSQLTips | Comments | Related: 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:
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:
We create the connection to the destination database and choose Transform Data Task from the Task menu:
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:
We then click properties to open the 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"):
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:
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:
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
- When faced with invalid date formats that need to be imported into SQL Server, consider the use of the ActiveX Script Transformation, particularly when faced with large amounts of data to be imported.
- Review information on VBScript Language Reference and JScript Language Reference
- Review information on Using ActiveX Scripts in DTS
- Refer to MSSQLTIPS for more information on Data Transformation Services.
About the author
MSSQLTips.com 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
