November 6, 2008 at 3:19 am
I have created a SSIS package which imports data from a flat file. This package runs fine when run from BIDS manually.
But when run from SQL server agent it gives me the below error:
Description: Data conversion failed. The data conversion for column "Column 221" 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: 2008-11-06 09:34:43.26 Code: 0xC020902A Source: Import Prubond File Flat File Source [1] Description: The "output column "Column 221" (1561)" failed because truncation occurred, and the truncation row disposition on "output column "Column 221" (1561)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. End Error Error: 2008-11-06 09:34:43.26 Code: 0xC0202092 Source: Import Prubond File Flat File Source... The package execution fa... The step failed.
What beats me is my truncation row disposition is RD_NOTused for all the columns still i get this error..
Help needed..
November 9, 2008 at 8:38 am
any new on that?
November 25, 2008 at 3:35 am
Hi I have the same issue, did you find a solution ?
November 26, 2008 at 8:43 am
I have no idea why it works one way and not the other, but why not solve the truncation?
Maybe irrelevant, but SSIS defaults char fields to a length of 50. Depending on the number of columns, can you try changing them to match the file spec? or increasing them one by one to 500?
I spent ages a few weeks ago trying to work out which column was giving me an import error, before realising that I had forgotten to specify that the file contained column headers so the column name was causing the failure.
April 5, 2009 at 2:02 am
Hi,
I too face the similar problem.
I have a flat file source with 33 columns. out of 33, one column say 20 has data of around 500 characters and it can even be upto 1023 characters (as per sql destination table).
By default, external columns and output columns are specified with 50 characters. When I am trying to change length column 20 to 1023 from the Show Advanced Edit of flat file source task, the length is not getting set.
How can i set the column length in this case?
NOTE: I do not believe, data conversion task helps in this case. Because, the issue is in Flat file source task only.
Could you please give your helping hand in this?
Thanks,
Umamahesh
April 11, 2012 at 9:00 am
I also faced this issue when getting data from one SQL to another. The column was exactly same on source an ddestination server. But when i checked the data flow line ( the connecting line between OLE source DB and OLE Destination DB, i saw that the column length (under meta data )was smaller than the actual column length. And that is why the package had fatal error due to truncation.
what I did is delete the data flow task, recreated it again, and i saw that the column width was correct, this resolved the issue.
Try your luck.
April 12, 2012 at 12:33 am
Rashid Manzar (4/11/2012)
what I did is delete the data flow task, recreated it again, and i saw that the column width was correct, this resolved the issue.
These options are a bit faster than dropping and re-creating the entire dataflow:
* go to the advanced editor of the source and adjust the datatype in the output columns
* double click on the source, go to the columns, deselect them all and select them all back. This causes a metadata refresh.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 12, 2012 at 7:34 am
Thanks Koen, that is eevn better.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply