I have some membership numbers being imported in an SSIS package. One of our customers has an extra long account number so we would like to split the last two characters off and insert a whitespace, forwardslash, whitespace and the remaining two characters as an identifier. For example :
100418032766000999 would then be
1004180327660009 / 99 .
How can this be done as an SSIS expression? I was thinking maybe a combination of substring/replace maybe in a derived column transform?Thanks for your help!
Yes, I guess you are on the right track but my combination would be LEN and SUBSTRING/RIGHT, something like
I guess the code may not be perfect, but should give you the hint.
answered Apr 10, 2012 at 02:26 PM
Something to cosider is a mechanism that accepts variable length account numbers, such as :
This is the TSQL version but you should be able to transfer it to SSIS if you need to
answered Apr 10, 2012 at 04:05 PM
This is what I came up with in the end thanks everyone for your help!
TRIM(NewAccountNumber) == "" ? NULL(DT_WSTR,1) : TRIM(SUBSTRING(NewAccountNumber,1,16) + " / " + SUBSTRING(NewAccountNumber,17,2))
answered Apr 18, 2012 at 10:35 AM