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.

Solution 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.

Solution 2

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.

Solution 3

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.

Solution 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.

View more solutions

Comments

[misterManager]

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.

[misterManager]

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.

[Pow-Ian]

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.

[misterManager]

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.

[Cherry Wu]

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