none
Data Conversion and Derived Column issues RRS feed

  • Question

  • I have a strange issue occurring with one of my SSIS packages. 

    What needs to happen:

    1. I have to read data from a table that stores a field in NCHAR(40)
    2. Send it through a Data Cleansing SSIS Component that forcefully outputs the data at a cleansed state as DT_STR(50)
    3. Update the same source table with cleansed data - Using an UPSERT third party tool

    Of course, I can't update the tables that stores data in NCHAR(40) with data from DT_STR(50), so I'm trying to use the Data Conversion Component, the Derived Column Component or a combination of, to set the data to DT_WSTR as well as to set the correct length, from 50 to 40.

    The Data Conversion Component fails when I try to set the incoming data (DT_STR(50)) to DT_WSTR(40):

    [Data Conversion [186]] Error: Data conversion failed while converting column "MD_Address1" (97) to column "_MD_Address1" (190).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

    Now I tried the same thing with the Derived Column Component, converting the data from the DQS component from (DT_STR(50)) to DT_WSTR(40) and there's the error message:

    [Derived Column [196]] Error: The "Derived Column" failed because truncation occurred, and the truncation row disposition on "Derived Column.Inputs[Derived Column Input].Columns[_MD_Address1]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    I also tried a combination of Data Conversion (From DT_STR(50) to DT_WSTR(50)) and a Derived Column just casting to the correct size, but that didn't work either. 

    Every time I try to change the size, one of the components fail. 

    It appears that my issue has nothing to do with the data types, but the actual data length.

    Why can't SSIS CAST my input from 50 to 40?

    What do I need to do to cast/convert data from DT_STR(50) to DT_WSTR(40)?

    Tuesday, May 27, 2014 7:27 PM

Answers

  • I resolved this issue by doing something I'm not proud of. 

    I used a combination of LEFT with CAST in a Derived Column Component. 

    That allowed me to fit the output into a length my destination would be able to support, while casting with the correct data type. 

    I expected the type cast to take care of both issues, but it didn't. 

    Example:

    (DT_WSTR,40)LEFT(MD_Address1,40)

    • Marked as answer by RealSQLSantos Monday, August 11, 2014 7:54 PM
    Monday, August 11, 2014 7:54 PM

All replies

  • DT_STR(50)) to DT_WSTR(40) is the issue, with Unicode you store twice as fewer characters thus the truncation

    Arthur My Blog

    Tuesday, May 27, 2014 8:17 PM
    Moderator
  • Arthur, 

    What you're saying is that I cannot convert a DT_STR(50) field to DT_WSTR(40)?

    Tuesday, May 27, 2014 8:35 PM
  • You DT_STR(50) does not fit the 40 chars limit on the DT_WSTR side length-wise, you have a longer than 40 characters long text and therefore the truncation occurs.


    Arthur My Blog

    Tuesday, May 27, 2014 8:54 PM
    Moderator
  • Hi IgorSantos_FL,

    It is the expected behavior when we try to convert DT_STR(50) (means maximum 50 characters in the value) to DT_WSTR(40) (means maximum 40 characters in the value). However, the truncation issue should not occur if you convert DT_STR(50) to DT_WSTR(50). Could you post the error message that you received when converting DT_STR(50) to DT_WSTR(50)? It may be a different issue.

    Regards,


    Mike Yin
    TechNet Community Support

    Tuesday, June 3, 2014 4:56 AM
    Moderator
  • Aren't you trying to convert DT_WSTR(40) to DT_STR(50)?? Certainly is how I read your 3 steps...
    Tuesday, June 3, 2014 5:15 AM
  • I resolved this issue by doing something I'm not proud of. 

    I used a combination of LEFT with CAST in a Derived Column Component. 

    That allowed me to fit the output into a length my destination would be able to support, while casting with the correct data type. 

    I expected the type cast to take care of both issues, but it didn't. 

    Example:

    (DT_WSTR,40)LEFT(MD_Address1,40)

    • Marked as answer by RealSQLSantos Monday, August 11, 2014 7:54 PM
    Monday, August 11, 2014 7:54 PM