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!

more ▼

asked Apr 10, 2012 at 01:30 PM in Default

avatar image

1.1k 56 60 66

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Apr 10, 2012 at 02:26 PM

avatar image

Usman Butt
14.8k 6 13 21

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

Apr 10, 2012 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 

But I am not sure of it either as I cannot test it at the moment.

Apr 10, 2012 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, 2012 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, 2012 at 05:06 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Apr 10, 2012 at 04:05 PM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left

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

answered Apr 18, 2012 at 10:35 AM

avatar image

1.1k 56 60 66

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 10, 2012 at 01:30 PM

Seen: 1913 times

Last Updated: Apr 18, 2012 at 10:35 AM

Copyright 2018 Redgate Software. Privacy Policy