CODEWITHSUNDEEP
sql-serverssisetlbids
Vian Ojeda Garcia
Vian Ojeda Garcia

Reputation: 867

SSIS Flat File [DT_DBTIMESTAMP] cant convert to SQL datetime2

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.

Upvotes: 3

Views: 4046

Answers (1)

Hadi
Hadi

Reputation: 37368

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

enter image description here

Mark your date column as Input

enter image description here

Create an Output column for each date column

enter image description here

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

Upvotes: 1

Related Questions