convert string into integer in ssis

Question

Hi Expert,

How to convert string into integer in derived transformation . I may also have some spaces in number. I have target column as integer

Column1
2342

Answer 1

In a derived column set the expression to something like this: (DT_I4)TRIM(REPLACE(MyColumn," ",""))

Answer 2

You'll need to use a custom expression to normalize the data before the conversion. Perhaps something like this (not tested):

TRIM(REPLACE(myStringColumn, " ", ""))

This would replace spaces in the value with nothing and then trim the string. At that point, if it is a number, then you should be able to convert it. Otherwise it'll error and you're error processing will kick in. If you wanted to handle that as well then your expression would get bigger. At some point though it might be easier to switch to a scripting task to handle the work but I wouldn't start there.

Answer 3

TRIM(REPLACE(myStringColumn, " ", ""))

thanks for trim function... could you please help in converting to integer in derived transformation