question

jhowe avatar image
jhowe asked

SSIS : insert characters

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!
sql-server-2008ssissql-server-2008-r2
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
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.
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Usman that's great what would the equivalent SQL statement be? I just want to test it against the data...
0 Likes 0 ·
I guess that could be SELECT CASE WHEN LEN(AccountName) > 16 THEN LEFT(AccountName,16) + ' / ' + RIGHT(AccountName,2) ELSE AccountName END FROM YOURTABLE But I am not sure of it either as I cannot test it at the moment.
0 Likes 0 ·
For the moment we're assuming full acc numbers are always 18 digits. We may reject numbers greater than this so I've come up with this, i'm guessing this will behave the same way as what you've provided?
SELECT REPLACE(AccNumber, AccNumber,LEFT(AccNumber, 16) + ' / ' + SUBSTRING(MembershipNumber, 16, 2)) 
FROM    dbo.Account
0 Likes 0 ·
I am confused a bit :) Your script have AccNumber and MembershipNumber? Are they both the same? OR the MembershipNumber's last two digits have been added intentionally to make all the accounts eighteen character long (provided with the minimum account length is sixteen characters) with the same pattern? And if you have to reject the accounts more than eighteen characters long then a WHERE clause may be needed. Since the logic is unknown to me I can only guess that your script seems to be to doing the right thing :)
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image
jhowe answered
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))
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.