SSIS flat file with values containing text qualifier

2
votes

I received a flat file that cannot be generated in other way. The delimited is a comma and the text qualifier is a double quote. The problem is that sometimes a have a double quote in the value. In example:

"0","12345", "Centre d"edu et de recherche", "B8E7"

Because of the double quote in the value, I received this error:

[Flat File Source [58]] Error: The column delimiter for column "XYZ" was not found.

[Flat File Source [58]] Error: An error occurred while processing file "C:\somefile.csv" on data row 296.

What can I do to process this file?

I use SSIS 2016 with Visual Studio 2015

1
csvssisetlflat-filedelimited
Are you sure that Centre d"edu has one quote, not two next to each other? If there is only one, I would ask whoever is generating file for you to fix it. If not fixed, you can't correctly recognize embalmed string like "," – Piotr
Yes I'm sure, but we don't have control on the way the file is generated. I must find a way to deal with it. – Bradford1138
I don't know your situation, but my answer would be that this file can't be imported in a maintainable way and escalate to whoever creating this. – Piotr

1 Answers

0
votes

You can use the Flat File Source error output to redirect bad rows to another flat file and correct values manually while all valid rows will be processed.

There are many links online to learn more about Flat File Source Error Output:

Update 1 - Workaround using Script Component and conditional split

Since Flat File error output is not working you can use a script component with a conditional split to filter bad rows, the following update is a step by step guide to implement that:

  1. Add a Flat File connection manager, Go To advanced Tab, Delete all columns except one column and change it length to 4000

enter image description here

  1. Add a script component, Go to Input and Output Column Tab, add desired output columns (in this example 4 columns) and add a Flag Column of type DT_BOOL
  2. Inside the Script Component write the following script to check if the number of columns is 4 then Flag = True which means this is a valid row else set Flag as False which mean that this is a bad row:
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {

        if (!Row.Column0_IsNull && !String.IsNullOrWhiteSpace(Row.Column0))
        {

            string[] cells = Row.Column0.Split(new string[] { "\",\"" }, StringSplitOptions.None);


            if (cells.Length == 4)
            {

                Row.Col1 = cells[0].TrimStart('\"');
                Row.Col2 = cells[1];
                Row.Col3 = cells[2];
                Row.Col4 = cells[3].TrimEnd('\"');
                Row.Flag = true;
            }
            else
            {
                bool cancel;
                Row.Flag = false;
            }

        }
        else
        {

            Row.Col1_IsNull = true;
            Row.Col2_IsNull = true;
            Row.Col3_IsNull = true;
            Row.Col4_IsNull = true;
            Row.Flag = true;
        }

    }

}
  1. Add a conditional split to split rows based on Flag column

enter image description here

  1. Map the Valid Rows output to the OLEDB Destination, and the Bad Rows output to another flat file where you only map Column0

enter image description here