locked
SSIS:Data Conversion Failed on Flat File destination

    Question

  • [Flat File Destination [46500]] Error: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page." What does this error mean exactly?

    I am taking columns from a flat file source. Then I am adding some new columns then rewriting the file to a ragged file format with fixed column values.  I've taken the Destination component off and it works fine.  So I know it could be the destination component but what could it be? Any ideas?

    Friday, December 15, 2006 5:42 PM

Answers

  •  Kevin Lyles wrote:

    [Flat File Destination [46500]] Error: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page." What does this error mean exactly?

    I am taking columns from a flat file source. Then I am adding some new columns then rewriting the file to a ragged file format with fixed column values.  I've taken the Destination component off and it works fine.  So I know it could be the destination component but what could it be? Any ideas?

     

    That error basically means you are overflowing the destination column data type.

    Compare the data type of the destination column with the data type of the same column in the data pipeline. Chances are they are different, so use data transform to make the required adjustment or change the format of your destination column.

    Friday, December 15, 2006 5:47 PM
    Moderator

All replies

  •  Kevin Lyles wrote:

    [Flat File Destination [46500]] Error: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page." What does this error mean exactly?

    I am taking columns from a flat file source. Then I am adding some new columns then rewriting the file to a ragged file format with fixed column values.  I've taken the Destination component off and it works fine.  So I know it could be the destination component but what could it be? Any ideas?

     

    That error basically means you are overflowing the destination column data type.

    Compare the data type of the destination column with the data type of the same column in the data pipeline. Chances are they are different, so use data transform to make the required adjustment or change the format of your destination column.

    Friday, December 15, 2006 5:47 PM
    Moderator
  • I am having the same problem under the same circumstances, but there is no reason to believe that data is overflowing the destination column.  A Derived Column data flow just prior to the Flat File Destination object (fixed width) has a one-character field, type DT_STR, length 1, populated with the character "a".  The flat file destination uses exactly the same data type (DT_STR) and same length (one).  I can view the data output of the Derived Column data flow using a Data Viewer, so I know that the "a" is OK up to that point.  But the process fails with the message described above: "The data conversion for column "Column 0" returned status value 4 and status text 'Text was truncated or one or more characters had no match in the target code page.'"  (It is column 0 in my case too.) 

     

    I realize that certain properties of the Flat File Connection Manager object might be relevant.  In this case, DataRowsToSkip=0, ColumnNamesInFirstDataRow=False, Unicode=False.  I have experimented with all these properties -- to no avail. 

     

    I have also learned that if I allow two errors instead of none by changing Package property MaximumErrorCount to 2 , I get a partial row of data in the output flat file.  The data that gets through is a 9-character SSN in an 11-character field (both the Derived Column and the Flat File Destination object have the same 11-character size; a last name, a first name... ) And then I find a one-character field (gender) gets through too.  (Surprise.)  The process chokes again, with the same message, on the next field whose input data completely fills the field -- birth date as yyyymmdd in an 8-character field (DT_STR).

     

    The presence of the word "conversion" in the error message suggests that a Data Conversion data flow object might be of some assistance.  But Mr. Salas, if the flat file destination requires a DT_STR(1) field, what should the DT_STR(1) data in the Derived Column data flow object's column be converted TO? 

     

    To make a long story short, Mr Salas, I suggest you try this yourself to prove to yourself that it works before giving us advice.  What matters is not whether it should work, but whether it actually does.

     

     

    Thursday, October 18, 2007 6:17 PM
  • I ran into an issue recently where I had to increase the size of a single character column in the flat file destination from 1 to 3. This may have been due to text delimiters, but I didn't have the opportunity to test that. Increasing the size did resolve the issue.

     

    I do have to point out that Rafael's suggestion to the original poster is correct - the most common cause of this is a mis-configured destination component. His solution evidently did work for the original poster. Just because you are receiving the same error message does not mean that it is the same problem.

    Friday, October 19, 2007 2:41 AM
    Moderator
  • This greatly helped!

    I've been trying to import some data from a CSV file but all the while fighting these seemingly ambigous error messages.

    My problem was that I had made my import column size of length 10 (it was a bank a/c no), but in actual fact quite a few of the bank a/c nos had a space before and after them making them 11 chars (9 char is pretty standard for bank a/c nos here is Australia).

    Simply setting my column length to 11 or 12 fixed all my problems.
    Wednesday, March 19, 2008 3:31 AM
  • this thread is locked because the thread starter's issue was resolved.
    Wednesday, March 19, 2008 6:15 AM
    Moderator