Importing yyyyMMdd Dates From CSV in SSIS

Question

I have 12 columns using the yyyymmdd format. In the Data Flow Task, I have a Flat File Source, a Derived Column Task and an OLE DB Destination. I'm applying the following expression to these fields in the Derived Column Task:

(DT_DBDATE)(SUBSTRING((DT_STR,10,1252)([Date_Column]),1,4) + "-"
+ SUBSTRING((DT_STR,10,1252)([Date_Column]),5,2) + "-"
+ SUBSTRING((DT_STR,10,1252)([Date_Column]),7,2))

It keeps making me convert the field before I substring it, but I have the fields set up as DT_STR in the Connection Manager. The destination field is in DATE format in SQL Server. SSIS always shows the "could not be converted because of a potential loss of data" error. I've checked the raw data in the CSV and don't see any dates that look out of place. Everything fits the format.

I've also attempted to use a Data Conversion task as suggested here, but ended up getting the same kind of error. Any recommendations? The answers I've seen on here all point to the use of a Derived Column task, but several others I've mentioned this to have had the same problem I'm having.

EDIT: Here's a sample of the data. The field specifically mentioned in all of the errors is CRTFCTN_DT. There are 470 fields in the dataset, but this does show the first two date fields.

SOLUTION: In the Connection Manager, I changed the yyyymmdd fields to DT_STR(10) (8 characters for the date, 2 for the added dashes), and rebuilt my Data Flow Task. This solved the problem. If you DON'T rebuild the task, the Derived Column task won't pick up on the new data type. Once the task was recreated, I also no longer needed to cast the dates as strings before applying substring.

I'm wondering if it is interpreting the field as integer. That might explain the loss of data warning as well as the need to Cast the field before using string operations on it. — John Pasquet, Apr 18 '17 at 18:18
The casting error specifically mentions that Substring doesn't work with DT_DBDATE. — grichmer, Apr 18 '17 at 18:34
@grichmer try casting to `DT_DATE` .. take a look at my answer and give a feedback — Hadi, Apr 18 '17 at 18:48

score 2 · Accepted Answer · answered Apr 18 '17 at 20:19

2

You setup column [Date_Column] as DT_STR in Connection Manager. What is the length you specified? by default it is 50. In Derived Column, you used its length as 10, which is the error message "could not be converted because of a potential loss of data".

answered Apr 18 '17 at 20:19

Wendy

640
1
4
8

You called it. I had set up the date fields with a length of 8. I changed this to 10 and rebuilt the Data Flow Task. Now it works! Thanks for the help! – grichmer Apr 19 '17 at 15:02

Hadi · Answer 2 · 2017-04-18T20:37:44.760

1

Try casting as (DT_DATE) instead of DT_DBDATE

(DT_DATE)((SUBSTRING((DT_STR,50,1252)([Date_Column]),1,4) + "-"
+ SUBSTRING((DT_STR,50,1252)([Date_Column]),5,2) + "-"
+ SUBSTRING((DT_STR,50,1252)([Date_Column]),7,2)))

You can do this workaround

Assuming that the date column name is inColumn

In the DataflowTask, Add a script component , Mark inColumn as input column, add a new Output column outColumn with dataType DT_DBTIMESTAMP

Change script language to vb.net

Mark your date column as Input

Create an Output column for each date column

Inside the script, in Input0_ProcessInputRow sub use DateTime.ParseExact Function as follow:

In this example the date column is MailReceivingDate

Public Class ScriptMain
    Inherits UserComponent


    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        If Not Row.MailReceivingDate_IsNull AndAlso
              Not String.IsNullOrEmpty(Row.MailReceivingDate.Trim) Then

            Row.outColumn = DateTime.ParseExact(Row.MailReceivingDate.Trim, "yyyyMMdd", New System.Globalization.CultureInfo("en-GB"))

        Else

            Row.outColumn_IsNull = True

        End If

    End Sub

End Class

edited Apr 18 '17 at 20:37

answered Apr 18 '17 at 18:24

Hadi

36,233
13
65
124

Double click on the script component. And go to script tab. Then click on design script button. It will open a vs editor – Hadi Apr 18 '17 at 19:32
And it is dt_date not db_date – Hadi Apr 18 '17 at 19:33
I've tried using DT_DATE instead, but I get the same error. As for the second portion of your comment, I'm attempting it, but Script Component is a new one for me. Do I need to put that line of code in there once for each date field? – grichmer Apr 18 '17 at 19:37
If all has the same issue yes. – Hadi Apr 18 '17 at 19:55
I'm getting several semantic errors saying that 1) I need to define the column names and 2) *DateTime*, *New* and *System.Globalization.CultureInfo* do not exist in the current context. – grichmer Apr 18 '17 at 20:02
@grichmer if interested in date conversion in ssis read this for more info http://stackoverflow.com/questions/42035354/ssis-source-format-implicit-conversion-for-datetime/42038060#42038060 – Hadi Apr 19 '17 at 15:16

Importing yyyyMMdd Dates From CSV in SSIS

2 Answers2

Linked