Attention: open in a new window. PDFPrint

String to DateTime on SSIS

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:


Imports System
Imports System.Data
Imports System.Math
Imports System.Globalization
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
    Inherits UserComponent
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Eingabe0Buffer)
        If Row.UPLOADDATE_IsNull Or Row.UPLOADDATE.Equals("00.00.0000 00:00") Then
            Row.CopyofUPLOADDATE_IsNull = True
            Row.CopyofUPLOADDATE = DateTime.ParseExact(Row.UPLOADDATE, "dd.MM.yyyy HH:mm", CultureInfo.InvariantCulture)
        End If
    End Sub
End Class

The script uses a single input column (UPLOADDATE) and a single output column (Copy of 'OUTPUTCOLUMN') defined in the script components properties. Parse exact uses the given format "dd.MM.yyyy HH:mm" (Day.Month.Year Hour:Minute) to parse the given date. So you are able to use other formats like yyyyMMddHHmmss. Just change the script on your needs.

By the way: I hope, Microsoft will add C# Scripting in future ;)