Date column populating from CSV to table behaving differently using SSIS

Question

I have CSV file

**date column**
06/04/1999
06/04/2000
.
.
.06/04/2017

I'm just loading this file from Csv file to Database table using SSIS

Flat file source to OLEDB Destination

where date column is absolutely DT_STR and SQL Table date column is varchar(50)

But it is behaving differently when I load sometimes it is populating like

  **date column**
    06/04/1999
    06/04/2000

and some other times like

 **date column**
    1999-04-06
    2000-04-06

I'm just trying to load the same what ever is there in Flat file but why it is giving "-" sometimes . Can any one please suggest me why it behaving differently each time . It doesn't have any conversions(like Derived Columns) in SSIS

what is the data type in metadata after the source? The first is more like in VARCHAR type in your destination, but for the 2nd loads, it is more like column is in DATE format — LONG
Have you validated that the dates in the files are always the same format, or are those sometimes formatted one way, sometimes the other? Since you are just importing the data as a string, this is important to know. — Laughing Vergil
how I can validate the dates from file if I see file is looking same with same date format DD/MM/YYYY — mohan111
@mohan111 it may contains some rows with different formats, if both columns are varchar and there is no transformation while the data import process, the problem must be in the flat file. take a look as my provided answer, and give me a reply — Hadi

Hadi Hadi · Accepted Answer · 2017-04-21T16:16:10

You have to convert these values to DateTime then to String with desired format yyyy-MM-dd using a ScriptComponent Transformation

Create a Flat File connection manager and an OLEDB Connection Manager (Source and Destination)
Add a DataFlow Task
In the DataFlow Task add a Flat File Source , Script Component , OLEDB Destination
your DataFlow should look like Source --> Script --> Destination
In the Script component Mark the Time column as input( assuming it's name is inDateColumn) and add an Output Column (ex: OutDateColumn) of String DT_STR

In the Script Write The Following Code: (using Vb.net)

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)  

    If Not Row.inDateColumn_IsNull AndAlso _
        Not String.IsNullOrEmpty(Row.inDateColumn.Trim) Then

        ' You can add more formats inside the following method

        Row.OutDateColumn = DateTime.ParseExact(Row.inDateColumn.Trim,New String(){"yyyy-MM-dd","dd/MM/yyyy"},New System.Globalization.CultureInfo("En-GB"), System.Globalization.DateTimeStyles.None).ToString("yyyy-MM-dd")

    Else

        Row.OutDateColumn_IsNull = True

    End If

End Sub

Map OutDateColumn to the destination column

Date column populating from CSV to table behaving differently using SSIS

1 Answers