SSIS : insert characters

Question

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!

Answer 1

0

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.

answered Apr 10 '12 at 02:26 PM

Usman Butt
13.8k ● 6 ● 8 ● 14

Thanks Usman that's great what would the equivalent SQL statement be? I just want to test it against the data...

Apr 10 '12 at 02:38 PM jhowe

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.

Apr 10 '12 at 02:56 PM Usman Butt

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

Apr 10 '12 at 03:16 PM jhowe

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 :)

Apr 11 '12 at 05:06 AM Usman Butt

(comments are locked)

Answer 2

0

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

answered Apr 10 '12 at 04:05 PM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Answer 3

0

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))

answered Apr 18 '12 at 10:35 AM

jhowe
919 ● 29 ● 42 ● 46

(comments are locked)

SSIS : insert characters

Follow this question

Related Questions