Slowly Changing Dimensions

  • Hi guys,

    Delving into the world of SSIS now and having some fun with SCD's. Particularly around NVARCHAR(max). It would seem that they're not supported in either the SCD tool or lookups. How do you usually handle these? I can't alter the source database to add a flag and I can't alter the datatype in the source database... basically my hands are tied!

    I need to know if this particular field changes and the only solution I currently have is to do a left(4000) and convert to nvarchar(4000) which should cover 99.999% of scenarios but the perfectionist in me isn't liking in.

    Any thoughts / suggestions?

    Thanks!

    Rik

  • Hi

    I think you will have no choice but to convert your nvarchar(max) to a compatible format

    But what you can do to take 100% of the cases, is to determine the longest value from your table

    Then add a derived column and cast the type nvarchar(max) to nvarchar(<longest caractere lenght>) before using it in your lookup

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

  • Thanks a lot, sometimes it's good to have your worst fears confirmed 😉

  • Load all data to your destination server and then use MERGE there.

    I hope it would be faster and easier to use.

    ____________________________________________________________

    AP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply