none
Can't fit 4000 chars in a Oracle destination column 4000 bytes

    Question

  • Hi,

    I have an Oracle destination with one column called memo that has a size of 4000 bytes. My source is a nvarchar(4000) column.
    e = 1 byte
    ë = 2 bytes

    Any good suggestions on how to cut of some chars at the end to fit in 4000 bytes? I want to cut of the minimal number of chars for each record....

    (using SSIS 2012 with Attunity Oracle destination)


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Wednesday, August 21, 2013 12:21 PM
    Moderator

All replies

  • I'd use a Derived Column Transformation (or Data Conversion Transformation) to cast the unicode to a string of the appropriate length.  E.g., if the 1252 code page would work for you:
    (DT_STR,4000,1252)
    Wednesday, August 21, 2013 3:08 PM
  • The source already is (DT_WSTR,4000,1252) / nvarchar(4000)


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Wednesday, August 21, 2013 4:44 PM
    Moderator
  • No, if the source is NVARCHAR, I believe that would be a DT_WSTR

    A null-terminated Unicode character string with a maximum length of 4000 characters.

    You would want to convert that to something more like a DT_STR:

    A null-terminated ANSI/MBCS character string with a maximum length of 8000 characters.

    Wednesday, August 21, 2013 5:05 PM
  • Sorry... read it to fast...

    The oracle destination requires dt_wstr, so converting to non-Unicode and then back to Unicode wont help me. And I will possibly loose some characters within words instead of at the end.

    I need to chop of chars at the end. I first used a left([column], 3850) but still not all records fitted in the Oracle column. Reducing it to an even lower number will unnecessary remove chars from strings that will fit without the chopping... I'm trying to avoid that.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Wednesday, August 21, 2013 6:38 PM
    Moderator