SSIS: Truncation Error When Loading Data From Flat Files
This is one of those errors that can really frustrate you. The error looks something like this:
[Flat File Source - Employee ] Error: Data conversion failed. The data conversion for column "Column 7" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
The scenario in this case is a fixed width file that is transformed to a SQL table. With a fixed width file you know the width of the columns. The SQL table that was created has the same column widths. This should be a slam dunk. Ah, but you're getting this glaring error and you wonder why. Well that's part of the fun of being a developer. You get to go off on wild tangents trying to figure out why things break for no reason whatsoever. It would be boring if this didn't occur on a regular basis. Let me show you the solution I came up with so you can work on other fun errors that pop up.
Flat File Connection
Check your flat file connection to make sure your values are correct. Input and output widths should be what you expect and make sure your datatype is the correct one to use. Check your database to make sure the datatype you are using matches what is being used as the default in the database.
Flat File Source
Sometimes the connection is fine and you still need to update the data types in another area. Right click your Flat File Source in the container and click on Select Advanced Editor. You will see the above screen. Click on the Input and Output Properties tab. Make sure the data type properties for each input column are correct.
Do the same for the output columns. If they match up to what you are expecting there is one more thing to try. 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.
Run The Container
Once you save the properties, run your task. You should now see no problems related to truncation. I'm no SSIS guru but I think the error is the result of the order of steps taken to create the connection to the flat file. I hope this solves your problem and now you can go and tackle the next one!