Working with Flat Files in SSIS – Datatypes

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.

This post will briefly describe how to configure data types so that source columns match destination columns.

In this example we see warning due to non matching data types. The reason for this warning is, that the outputs column data type is not in sync with the tables destination column. In this example the Flat File datasource has column „Kategorie“ with data type DT_STR and a length of 173 while the destinatin table column has datatype varchar(159)

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

The solution is to open the Extended Editor on the Flat File Datasource and set the length of column „Kategorie“ to 159 so it matches the destination table. Also we need to configure the property „TruncationRowDisposition“ to RD_IgnoreFailure so SSIS does not the stop the load process as soon as it need to truncate the string.

 

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