none
Truncation error in Derived Column

    Question

  • I have a data flow that uses Excel source to pull from an Excel spreadsheet.

    I use Derived Column to convert the strings to latin1 (code 1252).

    One of the source columns is called "seller".  Derived Column converts it to a new column called "oseller". None of the source values exceeds 46 characters in length.  The target column in the MSSQL database is varchar(60).

    Another is called "item_number".  Derived Column converts it to a new column called "oitem_number".  All of the source values are 15 characters long. The target column in the MSSQL database is varchar(15).

    Yet I am getting this truncation error on "oseller" in the Derived Column transform:

    Error: 0xC020902A at Data Flow Task - load rxinfo, Derived Column 2 [32]: The "Derived Column 2" failed because truncation occurred, and the truncation row disposition on "Derived Column 2.Outputs[Derived Column Output].Columns[oitem_number]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    Oddly, I sometimes get that same error on the "oitem_number" column in the Derived Column transform.

    I cannot figure out why a max 46-char seller column gets a truncation error when cast to a 60-char dt_str.

    Or why a 12-char item_number gets a truncation error when cast to a 15-char dt_str.

    Monday, March 20, 2017 7:47 PM

All replies

  • Hi,

    a. As far as I remember, Excel text columns are always nvarchar so each character actually takes 2 bytes.

    b. Please, check that your Excel data doesn't contain trailing spaces. "Alex " would count as 5 characters, not 4 !


    Monday, March 20, 2017 7:56 PM
  • From what encoding do you translate?

    Why would you translate?


    Arthur

    MyBlog


    Twitter

    Monday, March 20, 2017 8:04 PM
    Moderator
  • This issue  may caused due to extra spaces on columns.

    Can you do LTRIM(RTRIM(oseller) and LTRIM(RTRIM(oitem_number) in the Derived column expression.

    Let me know if you still find any issues.

    Please Mark this reply as answer if it has solved your issue. Or please use Vote As Helpful if my post was useful so that other forum members can benefit from it.


    • Edited by TWoW2017 Monday, March 20, 2017 8:09 PM
    Monday, March 20, 2017 8:09 PM
  • There are no trailing spaces. Each item_number is exactly 12 chars. Each seller name is a single "word", occasionally as long as 46 chars.

    In the Derived Column transform I do a cast:

    (dt_str,15,1252)item_number

    (dt_str,60,1252)LEFT(seller,50)

    BTW: The LEFT() function (and all char functions) operate on character counts, not byte counts.

    ==============================================

    The only thing I can think of is ... Suppose an Excel source field value has a multi-byte Unicode char that does not have a single-byte latin1 equivalent.  That might cause a problem. Still, I don't think it would be a truncation.

    But, alas, I cannot find any chars that fall outside the latin1 (or ASCII) range. They're all numbers, letters, and a few punctuations (dots and underscores). Sigh!

    Monday, March 20, 2017 9:19 PM
  • My target db table has varchar() columns, not nvarchar(). They're all latin1 single-byte chars.

    It seems that the data coming from the Excel source is in Unicode.

    If I don't convert, I get errors in the OLD DB Destination:

    Error at Data Flow Task - load rxinfo [OLE DB Destination - sales [193]]: Columns "ITEM_NUMBER" and "item_number" cannot convert between unicode and non-unicode string data types.
    Error at Data Flow Task - load rxinfo [OLE DB Destination - sales [193]]: Columns "SELLER" and "seller" cannot convert between unicode and non-unicode string data types.

    Monday, March 20, 2017 9:25 PM
  • Hi,

    That's what I suspected (and remembered vaguely from past experiences) : Excel shows its data always as Unicode, no matter if it's a purely ASCII string like "Hello World"

    Monday, March 20, 2017 9:36 PM
  • Cut it down to 1 row and check if it can process. If it works, add some more rows until you get the actual row causing the problem. It's far easier to figure out once sample data is identified
    Monday, March 20, 2017 9:38 PM
  • I just tried that. I still get the same error.
    Monday, March 20, 2017 9:51 PM
  • Can you show us the definitions, screenshot of derived column window and the sample first row?
    Monday, March 20, 2017 10:57 PM
  • Hi Bruce Malmat,

    I have made a test on my machine, but I couldn’t reproduce the issue. You’d better share an example data to reproduce it. In your case, you could have a look at following article to learn how to use error outputs to capture row-level errors. This article provides a detailed demo to explain it and you have a see.

    https://msdn.microsoft.com/en-us/library/ms141679.aspx

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 21, 2017 2:28 AM
  • Thanks. I will take a look at that.

    I was working this over the weekend, with time to spare. But just now I have other things occupying my time so I won't be able to get back on this for a bit.  Please excuse any delays in my response.

    Tuesday, March 21, 2017 8:22 AM