December 4, 2006 at 8:19 pm
I have to generate an output file with several columns. One of the business rules requires the TableID to be 5 digits, so if the value is less than 5 digits I would have to add 0's to the beginning.
Ex: original value = 123 new value = 00123.
Is it possible to do this with an existing transformation task (Derived Column)?
Thanks again.
December 6, 2006 at 7:29 am
yep, you can do this very easily in your activeX script of Data Transformation task, as long as your database column is a char/varchar field
December 6, 2006 at 9:18 am
Thanks for the suggestion. Fortunately, I was able to find a way to do this using a derived column task and expression.
Example:
Column Action Expression
TableID Replace(tableID) (len(tableID) == 3 ? "00" + tableid : TableID
Explanation - the expression above is similar to a case statement. "?" = if and ":" = else
So basically, "(len(tableID) == 3 ?" is the True/False check. If (len(tableid) == 3 Then "00" + tableID else ":" tableID
December 6, 2006 at 12:06 pm
I found a better way to add 0's to the tableID
Using derived column task, I add a new column with the following expression.
RIGHT("0000", 5 - (LEN(TableID))) + TableID
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply