SSIS Derived Column Date Formats

Question

I have a large data file that I am running through SSIS. Very simple XML source into a SQL database. I seldom use SSIS so I am far from an expert.

I have some records that I push into an error table because the dates are incorrect.

What I want to say is, if the date is not in a date format, then update this date to NULL.

I don't know if Derived Column is the way to go, but it appears it should be given examples I have been reading online.

I want to fix this issue in the SSIS stage, not in SQL after the data has been loaded in to the error tables.

score 1 · Answer 1 · edited May 23 '17 at 11:54

It is better to use a script component instead of using a derived column to achieve this because Scripts contains more options to manipulate dates.

If you want to check for specific dates formats you can use DateTime.TryParseExact() method or you use DateTime.TryParse() to try parsing date based on CultureInfo Date Formats.

First you have to add a Script component, mark the date column as input, create a new output column of type DT_DBTIMESTAMP

I will give two examples: Assuming that the input Column name is inDate and the output column is outDate

DateTime.TryParseExact()

Dim strFormats() As String = {"dd/MM/yyyy HH:mm:ss", "yyyy-MM-dd HH:mm:ss", "yyyyMMdd HH:mm:ss", "dd-MMM-yyyy HH:mm:ss", "MM/dd/yyyy HH:mm:ss"}

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)



    If Not Row.inDate_IsNull AndAlso
                 Not String.IsNullOrEmpty(Row.inDate) Then


        Dim dtTemp As DateTime

        If DateTime.TryParseExact(Row.inDate, strFormats, System.Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, dtTemp) Then

            Row.outDate = dtTemp

        Else

            Row.outDate_IsNull = True

        End If



    End If
End Sub

DateTime.TryParse()

 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)



    If Not Row.inDate_IsNull AndAlso
                 Not String.IsNullOrEmpty(Row.inDate) Then


        Dim dtTemp As DateTime

        If DateTime.TryParse(Row.inDate, System.Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, dtTemp) Then

            Row.outDate = dtTemp

        Else

            Row.outDate_IsNull = True

        End If



    End If
End Sub

you can read more about Datetime conversion using SSIS script component in this posts:

score 0 · Answer 2 · answered Mar 30 '17 at 13:25

You need to configure Error Output of the component which fails to convert from text to date.

It is usually either a Data Conversion, or a Derived Column component. In case you are already receiving the column in date format from the source - it is your XML Source component.

1a. Double-click on yout Data Conversion or Derived Column component, and go to Configure Error Output.
1b. Double-click on your XML Source, go to the Error Output.
1. For your date column change the default Error and Truncation options to Ignore Failure.

The values which failed to convert will now contain NULLs instead.

https://technet.microsoft.com/en-us/library/ms141679(v=sql.110).aspx

score 0 · Answer 3 · answered Mar 30 '17 at 13:36

SSIS expressions are limited in date and error handling. I would suggest, instead, to use a script component.

Under input columns, select the column that you want to work with. In the example below, my column is called "date"

Add the following to the script:

using System.Globalization;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    CultureInfo culture = CultureInfo.CreateSpecificCulture("en-US");
    DateTime dateResult;
    if (!DateTime.TryParse(Row.date, culture, DateTimeStyles.None, out dateResult))
    {
        Row.date = null;
    }
}

The date column is a string, which can be converted to Datetime with a data conversion component before being inserted into the database. The Culture is set to US English, but this can be changed to your specific needs. This hint will accept the local time format, i.e. MM/dd/yyyy.

TryParse() returns true if it can successfully parse a date from the input string. When it returns false, we just set the column to null.

SSIS Derived Column Date Formats

3 Answers3