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.

Honk

If you run the package in your non-production environment with the production zip file, does it produce the same results (error)? If you unzip the file and examine it, does it still remain CRLF for all the rows except the last one or is it solid? – Kb

Yes, the last row is a column called ACD_Flag, and has either A, C or D. It looks perfect on the last one, and has the {CR} and {LF} with the final line being complete blank. – Honk

added error message to question. It's definitely on that row. – Honk

See thats whats weird about it.... the data is perfectly fine and has processed on the other environments flawlessly. I'm really wondering more if theres just a known bug where this has happened to somebody in the past. Your examples are great though. – Honk

If anybody has an idea about common flat file code-page mismatches or maybe any experience with a similar (un-duplicatable) error and how you fixed it that would be greatly appreciated. Major props to @Siva for the extremely detailed answers, even if they weren't precisely what I was looking for. – Honk

Answer 1

So I've come up with an answer. The other answers are extremely well thought out and good, but I solved this using a slightly different technique.

I had all but eliminated the actual possibility of truncation because once I looked into the data in the flat file, it just didn't make sense... truncation could definitely NOT be occuring. So I decided to focus the second half of the error message: or one or more characters had no match in the target code page

After some intense Googleing I found a few sites like this one: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/6d4eb033-2c45-47e4-9e29-f20214122dd3/

Basically the idea is that if you know truncation isn't happening, you have characters without a code page match, so a switch from 1252 ANSI Latin I to 65001 UTF-8 should make a difference.

Since this has been moved to production, and the production environment is the only environment having this issue I wanted to make 100% sure I had the correct fix in, so I made one more change. I had no text qualifier, but SSIS still keeps the default Text_Qualified property for each column in the Flat File Connection Manager to TRUE. I set ALL of them to false (not just the column in question). So now the package doesn't see it needs a qualifier, then go to the qualifier and see <none> and then not look for a qualifier... it just flat out doesn't use a qualifier period.

Between these two changes the package finally ran successfully. Since both changes were done in the same release, and I've only received this error in production and I can't afford to switch different things back and forth for experimental purposes, I can't speak to which change finally did it, but I can tell you those were the only two changes I made.

One thing to note: the production machine running this package is: 10.50.1617 and my machine I am developing on (and most of the machines I am testing on) are: 10.50.4000. I've raised this as a possible issue with our Ops DBA and hopefully we'll get everything consistent.

Hopefully this will help anybody else who has a similar issue. If anybody would like additional information or details (I feel as if I've covered everything) please just comment here and let me know. I will gladly update this to make it more helpfull for anybody coming along in the future.

Honk

This was a fantastic solution. I don't think the text qualifier had anything to do with it though. I just changed my code page and it worked. – Mellen

This answer is great! After I changed to Unicode and made sure all the Text_Qualified as False, I had to change the <b>destination</b> property too: change DefaultCodePage as 65001, and set AlwaysUseDefaultCodePage as True – Trenchant

Answer 2

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.

Wilds

Although this didn't solve my issue, I gave an upvote because it is an incredibly helpful and detailed answer that will probably help tons of ppl who find themselves on this question. – Honk

Answer 3

It only happens on the one server? And you aren't using a test qualifier? We have had this happen before. This is what fixed it.

Go to that server and open the xml file. Search forTextQualifier and see if it says:

 <DTS:Property DTS:Name="TextQualifier" xml:space="preserve">&lt;none&gt;</DTS:Property>

If it doesn't make it say that.

Songer

You are extraordinarily close to the right answer! I finally found it! I made two changes, either of which could've been the one that snaped it into order. (1) Since the text qualifier was set to <none> already, I decided to mark each field property in the connection manager to Text_Qualifier=FALSE. (2) At the suggestion of a blog post with a similar issue I changed by code page from "1252 ANSI Latin I" to "65001 UTF-8". It was one of those two changes, or the combination of the two that corrected everything. – Honk

Also the server running this is in production is on 10.50.1617 but my machine and the machines tested are 10.50.4000... that could've been the cause of some of the headache as well. – Honk

Answer 4

I had the exact same error. My source text file contained unicode characters and I solved it by saving the text file using unicode encoding (instead of the default utf-8 encoding) and checking the Unicode checkbox in the Data Source dialog.

Slavin

Answer 5

Just follow these simple steps.

1. Right-click the OLE DB source or destination object, and then click Show Advanced Editor…. 2. On the Advanced Editor screen, click the Component Properties page. 3. Set AlwaysUseDefaultCodePage to True. 4.Click OK. 5.Clicking OK saves the settings for use with the current OLE DB source or destination object within the SSIS package.

Coplanar

Answer 6

I know this is a whole year later, but when I opened the flat file connection manager, for the text qualifier it had "_x003C_none_x003E_". I replace the "_x003C_none_x003E_" hex code garbage and put arrows like it should be, "<" none ">" (the editor is removing the arrows), and it stopped dropping the last row of the file.

Timberland

thats an issue that popped up w/ 2008 R2 after a particular update. If some machines in your workplace are past a certain version and some are before you will see the issue many times. I can't find further information at the moment. It wasn't the problem I was experiencing here. – Honk

Answer 7

Below steps may help you in solving your problem.

1. Go to show advance editor by right clicking on Source. 2.Click on Component properties.
2. And set AlwaysUseDefaultCodePage to TRUE.
3. And save he changes.

Childs