Hi all, 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 LEN(AccountName) > 16 ? SUBSTRING(AccountName,1,16) + " / " + RIGHT(AccountName,2) : AccountName I guess the code may not be perfect, but should give you the hint.
Something to cosider is a mechanism that accepts variable length account numbers, such as : DECLARE @var_1 VARCHAR(20) = '123456789' DECLARE @var_2 VARCHAR(3) = ' / ' DECLARE @var_3 VARCHAR(2) SET @var_3 = RIGHT(@var_1, 2) SET @var_3 = @var_3 + SUBSTRING(@var_1, 1, ( LEN(@var_1) - 2 )) SELECT @var_1 AS [var1] , @var_2 AS [var2] , @var_3 AS [var3] , LEFT(@var_1, ( LEN(@var_1) - 2 )) + @var_2 + @var_3 AS [combo] This is the TSQL version but you should be able to transfer it to SSIS if you need to
This is what I came up with in the end thanks everyone for your help! TRIM(AccountNumber) == "" ? NULL(DT_WSTR,1) : TRIM(SUBSTRING(AccountNumber,1,16) + " / " + SUBSTRING(AccountNumber,16,2)) TRIM(NewAccountNumber) == "" ? NULL(DT_WSTR,1) : TRIM(SUBSTRING(NewAccountNumber,1,16) + " / " + SUBSTRING(NewAccountNumber,17,2))