SQL Select to SSIS Derived Column

user_11674065 0
Viewed 301

I have this Select Query. 

SELECT REPLACE(LTRIM(REPLACE('000010A', '0', ' ')),' ', '0')

'000010A'= Customer_And_Partner_Account_Nr

I would want to Create a New column while loading data through SSIS in SQL table.

SELECT REPLACE(LTRIM(REPLACE('000010A', '0', ' ')),' ', '0')

Please help me on the expression to create a Derived Column in SSIS.

etl ssis expression sql-server sql
2 Answers

Have you considered using Script Component rather than Derived Column?

In script component, you could overload the ProcessInputRow event and use the TrimStart("0"c) method on the column to get rid of leading zeroes.

It should also improve the performance of the flow because (1) it only requires single operation and (2) it only scans a portion of the input string.

user_26107010

You can use the same expression but replace single quotes with double quotes:

REPLACE(LTRIM(REPLACE([Customer_And_Partner_Account_Nr], "0", " ")," ", "0")
user_70851700
Related