Logiacl Sql server SSIS question

Question

Logiacl Sql server SSIS question

sqlmaverick

Default port

Points: 1462
More actions
January 23, 2015 at 10:03 am

#300557

Hi, please help me to get below logic in Derived transformation, SSIS.
Logic:ensure the Docket number is 5 digits and populate with leading zeros if not.
I have to check input number field is 5 digits, if not I have to populate with leading zeros to make it as 5 digits.
It will be more helpful if any one got me the logic.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

stevenb 14609 SSCommitted Points: 1832 More actions · Answer 1

sqlmaverick (1/23/2015)
Hi, please help me to get below logic in Derived transformation, SSIS.
Logic:ensure the Docket number is 5 digits and populate with leading zeros if not.
I have to check input number field is 5 digits, if not I have to populate with leading zeros to make it as 5 digits.
It will be more helpful if any one got me the logic.

The easiest thing to do in a case like this is code that looks like the following:

SELECT RIGHT('00000' + DocketNumber,5)

So, you are putting together 5 0s and your docket number and taking the 5 rightmost characters. You have to make sure that your docket number is a character type or else you'll get an error with putting a string together with a numeric type. Also, if your docket number is ever more than 5 characters, you are only going to get the last 5 characters of the docket number.

I'm not sure what the equivalent expression is in SSIS, but that's the same logic should be able to be used anywhere.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

stevenb 63624 (1/23/2015)
sqlmaverick (1/23/2015)
Hi, please help me to get below logic in Derived transformation, SSIS.
Logic:ensure the Docket number is 5 digits and populate with leading zeros if not.
I have to check input number field is 5 digits, if not I have to populate with leading zeros to make it as 5 digits.
It will be more helpful if any one got me the logic.
The easiest thing to do in a case like this is code that looks like the following:
SELECT RIGHT('00000' + DocketNumber,5)
So, you are putting together 5 0s and your docket number and taking the 5 rightmost characters. You have to make sure that your docket number is a character type or else you'll get an error with putting a string together with a numeric type. Also, if your docket number is ever more than 5 characters, you are only going to get the last 5 characters of the docket number.
I'm not sure what the equivalent expression is in SSIS, but that's the same logic should be able to be used anywhere.

If DocketNumber is a numeric datatype, you can cheat like hell.

SELECT RIGHT(DocketNumber+1000000,5)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)