How to prevent SSIS from truncating the last field of the last data row in a flat file?

Question

I have an SSIS package thats unzips and loads a text file. It has been working great from the debugger, and from the various servers its been uploaded to on its way to our production environment.

My problem right now is this: A file was being loaded, everything was going great, but all of the sudden, on the very last data row (according to the error message) the last field was truncated. I assumed the file we receive was probably messed up, cracked it open, and everything is good there....

Its a | delimited file, no text qualifier, and {CR}{LF} as the row delimiter. Since the field with the truncation error is the last field in the row (and in this case the last field of the entire file), its delimiter is {CR}{LF} as opposed to |.

The file looks pristine and I've even loaded it into Excel with no issue and no complaints. I have run this file through my local machine running the package via the deugger in VS 2008, and it ran perfectly. Has anybody had any issues with behavior like this at all? I can't test it much in the environment that its crashing in, because it is our production environment and these are peak hours.... so any advice is GREATLY appreciated.

Error message:

Description: Data conversion failed. The data conversion for column "ACD_Flag" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". End Error Error: 2013-02-01 01:32:06.32 Code: 0xC020902A Source: Load ACD file into Table HDS Flat File 1 [9] Description: The "output column "ACD_Flag" (1040)" failed because truncation occurred, and the truncation row disposition on "output column "ACD_Flag" (1040)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. End Error Error: 2013-02-01 01:32:06.32 Code: 0xC0202092 Source: Load ACD file into Table [9] Description: An error occurred while processing file "MY FLAT FILE" on data row 737541.

737541 is the last row in the file.

Update: originally I had the row delimiter {CR}, but I have updated that to {CR}{LF} to attempt to fix this issue... although to no avail.

Answer 1

Update:

I am able to recreate the error message that you have added to your question. The error happens when you have more column delimiters in the line than what you have defined in the flat file connection manager.

Here is a simple example to illustrate it. I created a simple file as shown below.

I created a package and configured the flat file connection manager with below shown settings.

I configured the package with a data flow task to read the file and populate the data to a database table. When I executed the package, it failed.

Clicked the Execution Results tab on the BIDS. It displays the same message that you have posted in your question.

[Flat File Source [44]] Error: Data conversion failed. The data conversion for column "Column 1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Flat File Source [44]] Error: The "output column "Column 1" (128)" failed because truncation occurred, and the truncation row disposition on "output column "Column 1" (128)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[Flat File Source [44]] Error: An error occurred while processing file "C:\temp\FlatFile.txt" on data row 2.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Flat File Source" (44) returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

Hope it helps to identify your problem.

Previous answer:

I think that the value in the last field on the last row of your file probably exceeded the value of OutputColumnWidth property of the last column on the Flat File Connection Manager.

Right-click the Flat File Connection Manager on your SSIS package. Click Advanced tab page on the Flat File Connection Manager Editor. Click on the last column and check the value on the OutputColumnWidth property.

Now, verify the length of data on the last field of the last row in the file that is causing your package to fail.

If that is cause of the problem, here are two possible options to fix this:

1. Increase the OutputColumnWidth property on the last column to an appropriate length that meets your requirements.

2. If you do not care about truncation warnings, you can change the truncation error output on the last column of the Flat File Source Editor. Double-click the Flat File Source Editor, click Error Output. Change the Truncation column value to either Ignore failure or Redirect row. I prefer Redirect row because it gives the ability to track data issues in the incoming file by redirecting the invalid to a separate table and take necessary actions to fix the data.

Hope that gives you an idea to resolve your problem.