how to skip a bad row in ssis flat file source

Question

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?

What error causes the package to fail? Is it failing while reading the file or writing to the database? — Mark Wojciechowicz, Jan 02 '18 at 19:25
cannot find delimiter on "phone type" which represents a record that did not have all of the requisite information. the column "Phone Type" is column 15 of 17 in the list of fields, and in several records it's not there. The file has over 400,000 to several million records in a given file. — arcee123, Jan 02 '18 at 19:33
Gotcha, I would use a StreamReader with a Try..catch block to throw out the bad rows. This will avoid loading the entire file into memory: https://msdn.microsoft.com/en-us/library/system.io.streamreader%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396 — Mark Wojciechowicz, Jan 02 '18 at 19:39

score 4 · Accepted Answer · answered Jan 02 '18 at 19:55

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

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

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

In the Script Component Select Column0 as Input Column

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

Select the Script Language to Visual Basic

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

Map the Output Columns to the Destination Columns

It is no necessary. You can use `DT_STR` – Hadi Jan 03 '18 at 05:39 — Hadi, Jan 03 '18 at 05:39

score 2 · Answer 2 · answered Jan 02 '18 at 16:08

2

C# Solution for Loading CSV and skip rows that don't have 17 columns:

Use a Script Component: On input/output screen add all of your outputs with data types.

string fName = @"C:\test.csv" // Full file path: it should reference via variable

string[] lines = System.IO.File.ReadAllLines(fName);

//add a counter
int ctr = 1;

foreach(string line in lines)
{
    string[] cols = line.Split(',');

    if(ctr!=1) //Assumes Header row. elim if 1st row has data
    {
    if(cols.Length == 17)
    {
          //Write out to Output
          Output0Buffer.AddRow();
          Output0Buffer.Col1 = cols[0].ToString(); //You need to cast to data type
          Output0Buffer.Col2 = int.Parse(cols[1]) // example to cast to int
          Output0Buffer.Col3 = DateTime.Parse(cols[2]) // example of datetime
          ... //rest of Columns
    }
    //optional else to handle skipped lines
    //else 
    // write out line somewhere
    }
    ctr++; //increment counter
}

answered Jan 02 '18 at 16:08

KeithL

5,348
3
19
25

Hi Keith....this is awesome code. can you tweak it for extremely LARGE files? > 1Gb? Thanks. – arcee123 Jan 02 '18 at 17:48
The only thing I would change if it were possible would be to change the split to Regex i.e. https://stackoverflow.com/questions/18144431/regex-to-split-a-csv in order to handle the issues that arise inside strings like quotes in quotes and commas in quotes – KeithL Jan 02 '18 at 18:03
Instead of FileReadAllLines you can use a stream and ReadLine – KeithL Oct 26 '18 at 15:18
Can this be accomplished with multiple files in a given file path? – SidC Oct 07 '20 at 23:17
@SidC - I added a separate answer on how to work with multiple files. – KeithL Oct 08 '20 at 17:54

score 1 · Answer 3 · answered Oct 08 '20 at 17:54

This is for @SidC comment in my other answer.

This lets you work with multiple files:

        //set up variables
        string line;
        int ctr = 0;

        string[] files = System.IO.Directory.GetFiles(@"c:/path", "filenames*.txt");
        foreach(string file in files)
        {
            var str = new System.IO.StreamReader(file);
            while((line = str.ReadLine()) != null)
            {
                // Work with line here similar to the other answer
            }
        }

how to skip a bad row in ssis flat file source

3 Answers3

Solution Overview

Detailed Solution

Linked