If you created a flat file connection in Microsoft SQL Server Integration Services and the structure of the input files changed (a column is added), you can add a column to you’re flat file connection, by following the steps below. This will preserve the existing column mappings and column renames, it will only add a column at the end.
- Double click on you’re flat file connection, this will open the “Flat File Connection Manager Editor”
- Click on the New button
- This will add a new column at the end, now you can change the Name, ColumnDelimiter, OutputColumnWidth etc.
Note: The ColumnDelimiter value of the last column, should be the same as the RowDelimiter
Because mine input file is a CSV file, with rows delimited by newlines and columns delimited by commas, I expected the ColumnDelimter of each column to be a comma, but when you try to change the ColumnDelimiter of the last field to a comma, you will get the exception: “The row delimiter cannot be the same as the column delimiter.” This is because the last ColumnDelimiter should be the same as the row delimiter. The last column of each record will be followed by the row delimiter and not a ColumnDelimiter.