SSIS (Microsoft SQL Server Integration Services)

Attention: open in a new window. PDFPrint

String to DateTime on SSIS

Last Updated on Thursday, 27 August 2009 13:09 Thursday, 27 August 2009 12:50

The Microsoft SQL Server Integration Services provide an easy way and mostly straightforward way to perform ETL (Extract Transform Load) operations on an SQL Server 2005 or 2008. It provides a number of tasks to do transformations, conversions and other stuff you might perform around the data you like to load into or extract from your database.

Nevertheless, there are some stumbling blocks you might find. Today, I found one of them: String to Date conversion!

If you are using flatfiles as a data source or perform some pivot-transformation, you might want to convert a string representation into a datetime value. This might be done by the datatype conversion task, but if your date has some "non standard" format, this will fail. So I created a transformation script as little workaround and. A date in my desired format looks like this:

28.11.2008 18:20

The script uses the DateTime.ParseExact-Method provided by the .NET framwork. The VB script looks like this:

Read more: String to DateTime on SSIS