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