SSIS: is it possible to remove a specific character in a string then transform it to integer

Question

I am wondering if you can handle a string data 337-4425 and remove a specific character like - and change it to a integer data type. I am curious if this is possible. You can share your thoughts about it, I'll appreciate it and like to learn from an experienced programmer.

EDIT 1: I tried using derived column using expression like this Replace([MyColumn],"-","") also wondering if its possible to do this like this Replace([MyColumn],"-","") || Replace([MyColumn],"*","") because im getting an error while trying it. So I opted temporarily with the first expression, then I tried to execute the IS then encountered this error.

Error: 0xC0049060 at Data Flow Task, Derived Column [59]: An overflow occurred while converting between data types. The source type is too large to fit in the destination type. Error: 0xC0209029 at Data Flow Task, Derived Column [59]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049060 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[INTSCBMIN]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (59) failed with error code 0xC0209029 while processing input "Derived Column Input" (60). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. Error: 0xC02020C4 at Data Flow Task, Flat File Source [73]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

I changed the output column to numeric and I have a data type bigint for my destination.

Solution

Replace dashes for empty strings, then convert that to integer. In your data flow, use a derived column.

(DT_I4)REPLACE([MyColumn],"-","")

Comments

[Chris Albert]

What is your data source? Text file?

[Vian Ojeda Garcia]

@ChrisAlbert its a flat file data source. csv

[observer]

you can use REPLACE(REPLACE( @[User::StringNum] , "-", ""), "*", "") instead of using Logical OR (||) in between two replace function.

[Vian Ojeda Garcia]

what is the use of (DT_I4)

[Troy Witthoeft]

It is a data cast. It cast the string to a 4 byte signed integer. simple-talk.com/sql/ssis/working-with-ssis-data-types

[Vian Ojeda Garcia]

@Witthoeft I get an error in my expression REPLACE( [SCB_ActualMIN],"-","") || REPLACE( [SCB_ActualMIN],"*","") I dont see anything wrong

[observer]

you can use REPLACE(REPLACE( @[User::StringNum] , "-", ""), "*", "") instead of using Logical OR (||) in between two replace function.

[Vian Ojeda Garcia]

@observer still encountering those errors in my edit.

[observer]

@VianOjedaGarcia did you used (DT_I4) before outer REPLACE function. If not then use it. If still facing issue then post your update Expression and error as it is.