Search code examples
sql-servercsvssisetlflat-file

how to skip a bad row in ssis flat file source

I am reading in a 17-column CSV file into a database. once in a while the file has a "less then 17-column" row. I am trying to ignore the row, but even when all columns are set to ignore, I can't ignore that row and the package fails.

How to ignore those rows?

Solution

  • Solution Overview

    you can do this by adding one Flat File Connection Manager add only one column with Data type DT_WSTR and a length of 4000 (assuming it's name is Column0) - So all column are considered as one big column

    • In the Dataflow task add a Script Component after the Flat File Source
    • In mark Column0 as Input Column and Add 17 Output Columns
    • In the Input0_ProcessInputRow method split Column0 by delimiter, Then check if the length of array is = 17 then assign values to output columns, Else ignore the row.

    Detailed Solution

    1. Add a Flat file connection manager, Select the text file
    2. Go to the Advanced Tab, Delete all Columns except one Column
    3. Change the datatype of the remianing Column to DT_WSTR and length = 4000

    enter image description here

    1. Add a DataFlow Task
    2. Inside the Data Flow Task add a Flat File Source, Script Component and OLEDB Destination

    enter image description here

    1. In the Script Component Select Column0 as Input Column

    enter image description here

    1. Add 17 Output Columns (the optimal output columns)
    2. Change the OutputBuffer SynchronousInput property to None

    enter image description here

    1. Select the Script Language to Visual Basic

    enter image description here

    1. In the Script Editor write the following Script

      Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

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


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

        If strColumns.Length <> 17 Then Exit Sub


        Output0Buffer.AddRow()
        Output0Buffer.Column = strColumns(0)
        Output0Buffer.Column1 = strColumns(1)
        Output0Buffer.Column2 = strColumns(2)
        Output0Buffer.Column3 = strColumns(3)
        Output0Buffer.Column4 = strColumns(4)
        Output0Buffer.Column5 = strColumns(5)
        Output0Buffer.Column6 = strColumns(6)
        Output0Buffer.Column7 = strColumns(7)
        Output0Buffer.Column8 = strColumns(8)
        Output0Buffer.Column9 = strColumns(9)
        Output0Buffer.Column10 = strColumns(10)
        Output0Buffer.Column11 = strColumns(11)
        Output0Buffer.Column12 = strColumns(12)
        Output0Buffer.Column13 = strColumns(13)
        Output0Buffer.Column14 = strColumns(14)
        Output0Buffer.Column15 = strColumns(15)
        Output0Buffer.Column16 = strColumns(16)

    End If

End Sub

    2. Map the Output Columns to the Destination Columns