SSIS Error Text was truncated or one or more characters had no match in the target code page
I the same issue or something close. Except I have oneField (27) that get a trunacation error Error: Data conversion failed. The data conversion for column "Column 27" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". The "output column "Column 27" (91)" failed because truncation occurred, and the truncation row disposition on "output column "Column 27" (91)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. Data looks like:Red Text is the field that is throwing the error! 00000412, 0000000011411001, 0273508793, 01, "RUTH ", "EDWARDS ", 19500415,20080401, "N", 04488013, "1", "F", 365094, 20080401, 000472162716, "1447203880 ", 43995202341210, 00120.000, 0010, 00008.26, 00004.96, 000.00, 00002.70, 00007.66, 0, "PROMETH/COD SYP 6.25-10 ", "Y", "Promethazine w/ Codeine Syrup 6.25-10 MG/5ML ", 0000, "001C", " ", 610020,"WELLP1537", "O", "N", 00, "D", "S", "G", " ", " ", " ", "ID01V012008782", "TOM AHL CHRYSLER ", "M", "M", "PBD $20/10+40%/20% ", 00008.26, " ", "1184641367"
July 16th, 2008 11:52am
So you have truncation going on. What's the problem? Ensure your data flow data types are sized accordingly. Also check your advanced properties of the source component to ensure that the input and output data types are sized correctly.
July 16th, 2008 11:54am
I have a similar issue. I've already checked advanced properties and both source and destination are varchar(30). What's more confusing is that the TruncationRowDisposition is set to RD_NOTUsed but the error message still says that the row disposition setting on that column specifies failure.
September 30th, 2008 4:36pm
Hi everybody, Any solution? I got same issue with a tranformation [Data Conversion ] Error: Data conversion failed while converting column "CUSTOMER_NUMBER" (401) to column "Copy of CUSTOMER_NUMBER" (1256). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". I dont care if these data will truncate, I found a temporal solution leaving to SQL 05 to make a table and everything looks fine, but then I need to make a store procedore to copy this datato my destiny table, I wan tto know if somebody already found a correct solution with data conversion or activex or any other tool. regards, Marcos
October 13th, 2008 6:22pm
I just solved this issue. Here are the steps I used: 1. Created the target database connection. 2. Use Microsoft SQL Server Management Studio to explicity set the Data Type of the field causing the error (in my case I set it to varchar(255)). 3. Right click on the Flat File Source and select "Show Advanced Editor". 4. Within the editor select the "Input and OutputProperties" tab. 5. Expand the "Flat File Source Output". 6. Expand both the "External Columns" and "Output Columns" folders. 7. Check the "DataType" propertyfor the field causing the error in both folders. For me it was "string [DT_STR]", thiswas fine. 8. Check the Length for the field causing the error in both folders. For me the length was set to "50" for both. 9. Update the Length to match the length specified in the Database Table (in my case 255). 10. Run the package. Conclusion: Not only do you need to check that the fieldmatches within the "External Columns" and "Output Columns" folders within the Advanced Editor, but you also need to make sure that the value you assignis the same value that exists within the Database as well.
October 15th, 2008 10:00pm
I can't change my "output columns" datatype or size.i get this error. Error at MTD [DataReader Source ]: The data type of output columns on the component "DataReader Source" (2673) cannot be changed.Error at MTD [DataReader Source ]: System.Runtime.InteropServices.COMException (0xC020837D) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
October 20th, 2008 5:49am
mburger23, thank you for your thorough explanation of this fix!
February 5th, 2009 5:20am
I'm not able to use mburger23's response. The sentence: "3. Right click on the Flat File Source and select "Show Advanced Editor"." is not specific enough for me to use. I have tried right-clicking on: 1) the drop-down entry for "Flat File Source" 2) the file in the Open File dialog 3) the "Advanced" selection in the left column of the Wizard In no case does a right-click offer a "Show Advanced Editor" option. I have also searched the SSMS Help for "Show Advanced Editor" to no avail. I am using SSMS 2005. Perhaps mberger23 was using SSMS 2008? Wayne Erfling
October 15th, 2009 1:02pm
Hi,Have you tried to use TRIM(data column) for the data column that is getting truncated?Regards.
October 20th, 2009 1:23am
With further research I discovered that there are three places where columns are specified: 1) In the "Advanced" section at the beginning of the wizard, where columns can be updated individually (though somewhat arduously). Apparently the "Advanced" capability looks different in different versions of SSMS, thus my inability to use the earlier suggestion directly. To avoid the truncation error, this specification must be changed. Even if the next two items have enough capacity for a column, the first "column length" will still cause the truncation error. 2) The grid in "Edit Mappings", and 3) The "CREATE TABLE" statement that can be edited manually from the "Edit Mappings" dialog
October 21st, 2009 6:52am
I have found four things that I always check when I run into this problem. I have yet to find a time when one of these didn't work (specifically helps when reading data from flat files but I suppose most of the four would apply to any source). Check out myblog post, content repeated below:1. Make sure to properly configure the "Flat File Source". When setting the connection properties to the flat file, take time to click on the advanced tab and ensure that the" Name", "DataType", and "OutputColumnWidth" properties are set properly. I have found that if this is setup correctly when the initial connection is created, some if not all of the data type issues and errors can be alleviated. The "Flat File Connection Manager Editor" can be accessed while initially creating the connection or by double clicking on a flat file connection within the "Connection Managers" for connections that have previously been created.2. Depending on the order and steps that were used to create the connection to the flat file, sometimes the data types need to be updated in an additional area. This can be found by right clicking on the "Flat File Source" and selecting "Show Advanced Editor...". Once in the advanced editor, click on the "Input and Output Properties" tab. Expand the "External Columns" folder. For each field being loaded from the flat file there are some configurable properties. Make sure that the "DataType" field is properly set for each field.3. Something else that can be done if you are sure that the data type is set correctly in both of the two previously mentioned locations is to set the "Flat File Source" to essentially ignore those annoying truncation errors. On the same "Input and Output Properties" tab, expand the "Output Columns" folder. For those fields listed, there is a "TruncationRowDisposition" property. By default this is set to "RD_FailComponent". This can be switched to "RD_IgnoreFailure" in order to allow the data to successfully pass through the "Flat File Source" even if SSIS believes that truncation is going to occur. Along with making this change, you can also check the "DataType" in the "Output Columns" as well. Caution: If you do set the "Flat File Source" to "RD_IgnoreFailure" as mentioned above, always take time to review the data loaded in the target table to ensure that the integrity of the data was not jeopardized. Note: I have found that when the "DataType" for both the "External Columns" and "Output Columns" is manually updated that it does not remain the same when the advanced editor is reopened. For this reason, try Steps 1 and 2 before setting the "Output Columns" manually.4. The last thing to try, and this applies specifically to loading data from Excel files as opposed to text or CSV is to set the package to run in 32-bit mode. Click on "Project" on the top menu and select "Data Imports Properties...". Click on "Debugging" under the "Configuration Properties" and set the "Run64BitRuntime" to "False".Working with data from flat files can sometimes be difficult in SSIS. By using one or many of the approaches I have listed above you should be able to create a repeatable process that is frequently needed within most SSIS packages. Be very careful when setting data types within SSIS and make sure to do it upfront when necessary because it can be harder to debug later in the development process. If the proper changes are made it should not be a surprise to feel a big SSIS developer sense of relief when the screen shows all green.Let me know if this works!Check out my blog!
October 21st, 2009 10:45am
I was originally responding to these two lines: 2. Use Microsoft SQL Server Management Studio to explicity set the Data Type of the field causing the error (in my case I set it to varchar(255)). 3. Right click on the Flat File Source and select "Show Advanced Editor". I have been unable to find "Show Advanced Editor" in SSMS 2008. Is it possible that Step 2 is meant for SSMS and Step 3 for SSIS? Otherwise, where can I find "Show Advanced Editor" in SSMS 2008? Thanks!Wayne Erfling
October 27th, 2009 6:37am
It's not available from Management Studio. You must use either VS2008 or the Business Management Studio tool, if you installed it as a part of the SQL Server 2008 install.Rick
October 28th, 2009 3:08pm
Thanks Michael, setting source data types on advance tab did a trick! Cheers Marina
April 10th, 2010 5:57am
Error: Error: 2010-08-16 14:27:45.54 Code: 0xC02020C5 Source: Create Flat File Data Conversion  Description: Data conversion failed while converting column "FIRST_NAME" (327) to column "FIRST_NAME" (779). The conversion 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: 2010-08-16 14:27:45.54 Code: 0xC020902A Source: Create Flat File Data Conversion  Description: The "output column "FIRST_NAME" (779)" failed because truncation occurred, and the truncation row disposition on "output column "FIRST_NAME" (779)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. End Error *************************************************Solution************************************************* The Output File was set to 1252 (ANSI - Latin). There was an Unprintable character that resided in the Source not recognized by ANSI. Change the output flat file to 65001 (UTF-8) which includes additional characters. Problem Solved! *************************************************Solution*************************************************
August 18th, 2010 1:56pm
Honestly don't know how people fixed their errors in the pre-internet age - thank you Michael Burger - your solution helped me - almost a whole year later! :-)
October 8th, 2010 11:55am
Thank you Michael Burger. Still your answer is best after 2 years.Bhoopendra Pratap Singh
August 8th, 2011 6:03am
I agree, just to expand on this solution: after fighting for half a day with the same dreaded error message, the fix that did it was: In Choose a data source dialog box, Advanced, DataType drop down box I changed the value from string [DT_STR] to text stream[DT_TEXT]. Worked like a magic. Hope this will save somebody 4 hours of sheer frustration. Jakub
September 2nd, 2011 11:12pm
My error is related to trying to read from a 65000 UTF-8 file and thus the code page error because the file contains Asian characters that don't conform to the code page. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ed03e7a8-2f9c-4370-86e8-2e9ada441e13/ I tried to import them all as nvarchar with wide fields, and then to use 1252 Ansi Latin code page but that to failed as well. The "SSIS" I use is the the primitive package that comes with the Import/Export wizard in SSMS. I read some links to Connect.microsoft.com that indicated the 65000 UTF-8 files were unsupported. SOOOO.... If it helps anyone, I SOLVed this by importing the fields as DT_TEXT (text streams). Once they are in place they can be converted after the fact. I also, as above, used the 1252 Ansi Latin Code Page (and using Mike Burger's advice), I set the project to ignore truncation errors. But the puzzle answer for me is mostly the DT_TEXT assignment to the fields that are problematic (using a 65000 UTF-8 source file). SANTOSH Y... that looks like personal data that should not be exposed in public. Perhaps you should do a re-write? R, J
October 25th, 2011 6:22pm
Third answer worked for me. Thanks Priya
March 16th, 2012 5:27am
Michael, You solution works in a way that the error goes and the package runs ... BUT as you pointed out under caution ..."Caution: If you do set the "Flat File Source" to "RD_IgnoreFailure" as mentioned above, always take time to review the data loaded in the target table to ensure that the integrity of the data was not jeopardized." It does jeopardizes the data, out of my 8000 odd rows only 4000 are copied .. rest are ignored I guess. I have 4 columns in my DB table. FName, LName, ADName, Email. All were set to varchar(max) and DT_STR in the package. I first got this error in the email column when I ran the package. None of the solutions worked until I changed the data type to TEXT at both ends, which made the error for email go. Then I got the same error on ADName. Unfortunately I cannot change my ADName column to TEXT, to maintain my application integrity which picks the data from this DB. So I am stuck. Is there any other solution except from changing the data type to TEXT OR setting the flatfile source to "RD_Ignorefailure". Coz both of these solution arent exactly a choice for me .....
May 27th, 2012 12:38am