Retain NULL not working for all columns

Question

I have a flat file with the following information which I am trying to load it into a SQL Server table with same columns and data type as date -

StartDate,EndDate
2017-01-02,2017-03-01

2017-01-02,2017-03-01

Note that second row is blank. Now I am setting both the source columns as DT_DBDATE data type. Also in the Flat File Source I am checking on the option Retain null values from the source as null values in the data flow

At the destination DFT I am enabling the Keep NULL option. Here is the problem. When I run the package I am seeing null for StartDate but for EndDate I see the following error -

Error:Year, Month, and Day parameters describe an un-representable DateTime.

Any idea how to get over this or what is causing this problem?

Answer 1

The Problem

The empty rows does not contains any column delimiter which mean it contains only one column, which will cause problems while reading from the falt file.

How To Fix it

In the flat file connection manager remove the column delimiter and read each row as one column of type DT_STR and set the length to 4000

Then Add a script component and do the following:

1. Check if the row is empty
   • If yes output empty columns
   • If not split the row into columns
2. Output must contains the columns you needed

You can use a similar code: (i used vb.net)

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        If Not Row.Column0_IsNull AndAlso
                    Not String.IsNullOrEmpty(Row.Column0) Then

            Dim strcolumnms() As String = Row.Column0.Split(CChar(";"))

            Dim dtTemp As Date


            If DateTime.TryParse(strcolumnms(0), System.Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, dtTemp) Then

                Row.Column1 = dtTemp

            Else

                Row.Column1_IsNull = True

            End If


            If DateTime.TryParse(strcolumnms(1), System.Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, dtTemp) Then

                Row.Column2 = dtTemp

            Else

                Row.Column2_IsNull = True

            End If


        Else

            Row.Column1_IsNull = True
            Row.Column2_IsNull = True





        End If

    End Sub

Dealing with dates in Flat files

It is better to choose DT_STR type when working with date values (or other data types) stored in a flat files and convert them (after checking if they are well formed) using a Script Component or Data Conversion Transformations.

Using Script Component

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

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.

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 = {"yyyy-MM-dd"}

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:

• SSIS Source Format Implicit Conversion for Datetime
• SSIS convert string date with various formats to DateTime

Using Data Conversion Transformation

Use a Data Conversion Component to convert the DT_STR column to DT_DBDATE and on the Error Output set Ignore failure so values that cannot be converted is replaced by NULL