SQL Server

Working with Flat Files in SSIS – Extended Editor

In my experience working with flat files in Microsoft Integration Services can be quite cumbersome sometimes. There are numerous swtiches and options that can be configured to smoothly load CSV files into your database.
Configuring things the wrong way may cause your ETL process to crash in case a CSV file changes its structure or content.

In this post I will describe how to safely remove a column from the load process as it is not needed any longe (while it is still contained in the CSV file)

First you need to click on the flat file source and select extended editor

2016-04-08 10_26_46-EavDwh_SSIS - Microsoft Visual Studio

Next choose Output columns and click remove the column you want to delete.

2016-04-08 10_27_29-EavDwh_SSIS - Microsoft Visual Studio

The result will be an error message about a erroneous mapping.

2016-04-08 10_27_51-EavDwh_SSIS - Microsoft Visual Studio


Now doube click on the arrow so that this dialog appears:

2016-04-08 10_28_13-Verweise auflösen

In my case SSIS is complaining about two erroneous columns although I only removed one. IMHO this is an SSIS bug. To fix the mapping first click on the column highlighted in red (kat4) and just ignore the SSIS message

2016-04-08 10_28_36-EavDwh_SSIS - Microsoft Visual Studio

You need to repeat this step for source and destination column. Next select the checkbox at the bottom and click OK.

2016-04-08 10_29_13-Verweise auflösen


As a result the mapping is now fixed and the column is removed