SSIS Flat File [DT_DBTIMESTAMP] cant convert to SQL datetime2

Question

I created a running SSIS and I tried inserting data without error to SQL Server but suddenly this error message pops up

Error: 0xC02020A1 at Data Flow Task, Flat File Source [59]: Data conversion failed. The data conversion for column "SCB_ActualDTime" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

It says that the error occurred in row 8 containing these data from the

SCB_ActualDTime
2017-04-16 15:28:07

It is really weird because I tried inserting the same data via SQL script and there is no error message.

Try removing that row from your incoming data and see if you still get the error. — Tab Alleman, Apr 18 '17 at 12:27

Hadi · Accepted Answer · 2017-04-19T03:38:59.667

1

Flat File is not a data source that force a specific data types for each column. so it may contains unicode characters or white spaces that prevent reading column as Datetime.

After working with many cases i decided to work with flat file in this way.

In the Flat File Connection Manager i will read all column as DT_STR column without specifying other data types
Inside The DataFlow Task i will add a script component to check if columns value can be converted to the estimated data type and then converted it

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

                Dim dtDate as Date

               If DateTime.TryParseExact(Row.MailReceivingDate.Trim, "yyyy-MM-dd HH:mm:ss", New System.Globalization.CultureInfo("en-GB"),System.Globalization.DateTimeStyles.None,dtDate) Then

                    Row.OutColumn = dtDate

               Else

                    'If column cannot be parsed

                    Row.outColumn_IsNull = True

               End If

            Else

                    Row.outColumn_IsNull = True

            End If

        End Sub

    End Class

edited Apr 19 '17 at 03:38

answered Apr 18 '17 at 21:02

Hadi

36,233
13
65
124

I encountered an error in code line `DateTime.TryParseExact(Row.SCBActualDTime.Trim, "yyyy-MM-dd HH:mm:ss", New System.Globalization.CultureInfo("en-GB"), dtDate)` The error message says `Overload resulotion failed because no accessible 'TryParseExact' accepts this number of arguments`. BTW I am using Microsoft VB 2012 if that matter. – Vian Ojeda Garcia Apr 19 '17 at 01:03
sorry i missed on argument `DateTimeStyles` use the following code `DateTime.TryParseExact(Row.SCBActualDTime.Trim, "yyyy-MM-dd HH:mm:ss", New System.Globalization.CultureInfo("en-GB"),System.Globalization.DateTimeStyles.None,dtDate)` – Hadi Apr 19 '17 at 03:38
@VianOjedaGarcia i edited my answer. you can read more about this method in this link https://msdn.microsoft.com/en-us/library/system.datetime.tryparseexact(v=vs.110).aspx – Hadi Apr 19 '17 at 03:39
One last thing before I accept your answer. What If I have another column to process. Will it create another sub for my column2? – Vian Ojeda Garcia Apr 19 '17 at 04:44
@VianOjedaGarcia not `Overrides Sub Input0_ProcessInputRow` is for row processing , just repeat the code inside this Sub – Hadi Apr 19 '17 at 04:46
@VianOjedaGarcia but you have to mark the other column as input and create a new output column for it – Hadi Apr 19 '17 at 04:52
@VianOjedaGarcia also take care this code will set to Null every columns that cannot be parsed to date. Good Luck i will be offline for some time – Hadi Apr 19 '17 at 04:53
Yeah I noticed it in the code. The problem is I still needed that date. I can't have a null date in my database. – Vian Ojeda Garcia Apr 19 '17 at 04:54
You have to add your own code here. You have to decide if you will add a constant value or do some logic – Hadi Apr 19 '17 at 05:20
If all dates are wel formatted it will note convert to null – Hadi Apr 19 '17 at 05:29
Thank you very much. Very much appreciated – Vian Ojeda Garcia Apr 19 '17 at 06:26
im trying to make a way to reuse the code. Because it kinda bug me to repeat the same code – Vian Ojeda Garcia Apr 19 '17 at 09:08

SSIS Flat File [DT_DBTIMESTAMP] cant convert to SQL datetime2

1 Answers1