I am using SSIS to upload a bunch of csv tables into SQL Server Express. The csv files are quarterly data files. They are largely consistent but every couple of years a column may be added or an existing column deleted.
Every time SSIS encounters a change in the columns present it throws one or both of these errors:
Error: 0xC0202055 at Data Flow Task, Flat File Source [2]: The column delimiter for column "XYZ" was not found.
Error: 0xC0202091 at Data Flow Task, Flat File Source [2]: An error occurred while skipping data rows.
When a new column is added, both errors appear. When an existing column is excluded, only the 2091 error appears. Note that the columns are not just null when they are deleted, they no longer in the data.
Is there setting or something I can use so that SSIS accommodates these changes in columns? I've tried setting up the target table with the complete set of columns but that hasn't helped.
Thanks,
Best Answer
You can use a script transformation to create the actual column mapping. For your flat file source, make every output line become a single large column.
In your script transformation, create column definitions for each column you need mapped / already know about.
This assumes your first line contains headers:
Then, in your script transformation , split each row by the delimiter you need. You can create a boolean value indicating if the first line has been read yet. If not, parse the line and store the column mapping positions in a Dictionary<string,int> such as ColumnMappings and do not write to OutputBuffer. If your row reader sees that the column mappings have been read, then you can set your output to the correct column with something like:
An example of an entire transformation with a column "Day" is below