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"

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.

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.

Hi everybody, Any solution? I got same issue with a tranformation [Data Conversion [1233]] 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

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.

I can't change my "output columns" datatype or size.i get this error. Error at MTD [DataReader Source [2673]]: The data type of output columns on the component "DataReader Source" (2673) cannot be changed.Error at MTD [DataReader Source [2673]]: 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)

mburger23, thank you for your thorough explanation of this fix!

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

Hi,Have you tried to use TRIM(data column) for the data column that is getting truncated?Regards.

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

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!

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

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

Thanks Michael, setting source data types on advance tab did a trick! Cheers Marina

Error: Error: 2010-08-16 14:27:45.54 Code: 0xC02020C5 Source: Create Flat File Data Conversion [387] 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 [387] 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*************************************************

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! :-)

Thank you Michael Burger. Still your answer is best after 2 years.Bhoopendra Pratap Singh

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