Monday, May 12, 2008

Dropping Timestamps from SQL Server and Text File Data Sources in Integration Manager

As promissed, the following post deals with managing those rogued timestamps in SQL Server and text file data sources. This problem, contrary to what anyone would think, is very common among Integration Manager developers and has haunted newsgroups and message boards with questions on how to effect such conversions. I have devised two methods that are effective in removing timestamps from data sources. The first method deals with the problem at its source -- the source query. The second method utilizes VBScript to remove the timestamp.

SQL Server Query Method (Source Query)

To drop a timestamp from a SQL Server data source, you will need to setup a simple ODBC or advance ODBC data source. The query will have to make use of the SQL Server convert function to remove the timestamp from the datetime field as follows:

select field1, field2,..., convert(datetime, convert(char(15), thedatefield, 101)) as newdatefield from your_table where your_condition

Note the dual conversion, from date to char and back to a datetime expression.

VBScipt Method (Date Field Script)

In order to apply this method, you will need to open the script editor for the date field and enter the following script:

tDate = SourceFields("SourceQuery.thedatefield")
newDateField = right("00" & Month(tDate), 2) & "/" & right("00" &_
Day(tDate), 2) & "/" & year(tDate)
CurrentField.Value = newDateField

The neatness of this script rely on the ability to set the dows (leading zeroes) in front of the month and day values without extending the script beyond 3 lines.

Hope you liked these two simple, yet useful methods of dropping timestamps from data sources.

Until next post!

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC


Anonymous said...

Helpful post. What I've been doing is pulling the source text file into excel, changing the format to mm/dd/yy, then saving the file still in text format.

Mariano Gomez said...

What you have been doing is not uncommon. I am glad I provided you with tools to improve your work.