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

Extended editor in flat file source

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

Removing a column from Output columns

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

Error message about erroneous mapping

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

Dialog after double clicking on the arrow

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

Fixing the mapping by clicking on the column highlighted in red

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

Selecting the checkbox and clicking OK

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