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 : 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.
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 !
- Edited by Sebastian I. Sajaroff Monday, March 20, 2017 8:02 PM
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 Nr5952 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:
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!
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 ]: 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 ]: Columns "SELLER" and "seller" cannot convert between unicode and non-unicode string data types.
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.
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.