Ralf Eisenreich

Importing Date values e.g. from Flat Files it can happen that the format of the date is different to the Host System date formatting of the regional settings. Especial the date setting for English (UK: day/month/year) and English (US: month/day/year) are often very problematic.

In SSIS a solution for this issue can be realized with a ScriptComponent as follows.

1. Date column
   First of all you should import your Date column as nvarchar type. In this example let us assume we are going to import a Birthday column in Germany format on a host system with US-based regional setting.
2. Script Component: Columns
   We specify as import column the BIRTHDAY_STR column (nvarchar) from the Flat File and as export column we specify a column of type date named BIRTHDAY_DATE.
3. ScriptComponent: Code
   In the Script we are using the TryParse method which is stable and flexible enough. But in order to import the German date we have to create a culture object.

   
Dim dt As DateTime
Dim culture As Globalization.CultureInfo
culture = Globalization.CultureInfo.CreateSpecificCulture("de-DE")
Try
If DateTime.TryParse(CStr(Row.BIRTHDAY_STR), culture, Globalization.DateTimeStyles.None, dt) Then
Row.BIRTHDAY_DATE = dt
Else
Row.BIRTHDAY_DATE_IsNull = True
End If
Catch ex As Exception
Row.BIRTHDAY_DATE_IsNull = True
End Try


This example should help you to import the dates of different regional settings.