SSIS error when loading data from flat files

Introduction:

It is sometimes inevitable that data within an SSIS package will need to be loaded from flat files. If you have ever had to develop with SSIS you know that it can be quite finicky with its data types. I have found that it is sometimes even worse when the data is coming from flat files because the package would fail before I could even apply a data transformation within the data flow task. Below there is an example of a common error I get when working with flat files in SSIS:

Error: 0xC02020A1 at Data Flow Task, Source – Distribution by County table (from QPRProduction database)_csv [1]: Data conversion failed. The data conversion for column "FullName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Solutions:

I am going to offer four possible solutions and steps that I take in order to get the data from the flat file into the database. I will list the four steps, however, keep in mind that a combination of the four steps will sometimes be needed in order to allow SSIS to load the data successfully.

1. Make sure to properly configure the "Flat File Source". When setting the connection properties to the flat file, take time to click on the advanced tab and ensure that the" Name", "DataType", and "OutputColumnWidth" properties are set properly. I have found that if this is setup correctly when the initial connection is created, some if not all of the data type issues and errors can be alleviated. The "Flat File Connection Manager Editor" can be accessed while initially creating the connection or by double clicking on a flat file connection within the "Connection Managers" for connections that have previously been created. This is shown below:

2. Depending on the order and steps that were used to create the connection to the flat file, sometimes the data types need to be updated in an additional area. This can be found by right clicking on the "Flat File Source" and selecting "Show Advanced Editor…" as shown below:

The IT Leader's Guide to Multicloud Readiness

This guide provides practical key insights and important factors to consider to make informed decisions in your multicloud journey.

Download the Guide

Once in the advanced editor, click on the "Input and Output Properties" tab. Expand the "External Columns" folder. For each field being loaded from the flat file there are some configurable properties. Make sure that the "DataType" field is properly set for each field. In the screenshot below the field named "PDOCount" is properly set to a "four-byte signed integer [DT_14]".

3. Something else that can be done if you are sure that the data type is set correctly in both of the two previously mentioned locations is to set the "Flat File Source" to essentially ignore those annoying truncation errors. On the same "Input and Output Properties" tab, expand the "Output Columns" folder. For those fields listed, there is a "TruncationRowDisposition" property. By default this is set to "RD_FailComponent". This can be switched to "RD_IgnoreFailure" in order to allow the data to successfully pass through the "Flat File Source" even if SSIS believes that truncation is going to occur. Along with making this change, you can also check the "DataType" in the "Output Columns" as well. This is shown in the screenshot below:

Caution: If you do set the "Flat File Source" to "RD_IgnoreFailure" as mentioned above, always take time to review the data loaded in the target table to ensure that the integrity of the data was not jeopardized.

Note: I have found that when the "DataType" for both the "External Columns" and "Output Columns" is manually updated that it does not remain the same when the advanced editor is reopened. For this reason, try Steps 1 and 2 before setting the "Output Columns" manually.

4. The last thing to try, and this applies specifically to loading data from Excel files as opposed to text or CSV is to set the package to run in 32-bit mode. Click on "Project" on the top menu and select "Data Imports Properties…". Click on "Debugging" under the "Configuration Properties" and set the "Run64BitRuntime" to "False".

Conclusion:

Working with data from flat files can sometimes be difficult in SSIS. By using one or many of the approaches I have listed above you should be able to create a repeatable process that is frequently needed within most SSIS packages. Be very careful when setting data types within SSIS and make sure to do it upfront when necessary because it can be harder to debug later in the development process. If the proper changes are made it should not be a surprise to feel a big SSIS developer sense of relief when the screen shows all green.