Convert String to Int SSIS

Question

I have an string of original source and into destination table It was decimal(5,2), I try to change it with a rule

But when I run task It always fail, can someone help me what is wrong there?

Update

I also try (DT_DECIMAL,2)TRIM(NAME1) but It still fail

Solution 1

The task failure may be caused by some values that cannot be converted (contains non-numeric value)

In the Data Conversion component you can set the error output to Ignore failure then if NAME1 cannot converted to DT_I8 the new column value will be NULL

You can read more in this useful blog article:

• http://microsoft-ssis.blogspot.com/2011/04/isnumeric-or-isnumber-expression-is.html

Another WorkAround

You can achieve this using a script Component instead of Derived Column

Just add a script component , add an output column of type DT_I8 (assuming it's name is OutColumn) and mark NAME1 column as Input.

Inside the script window write the following script (using Vb.Net):

Public Class ScriptMain  
    Inherits UserComponent  

    Public Overrides Sub InputBuffer0_ProcessInputRow(ByVal Row As InputBuffer0)  


        Dim intValue as Int64

        If Not Row.NAME1_ISNULL AndAlso 
           Not String.iSnullOrEmpty(Row.NAME1.Trim) AndAlso 
           Int64.TryParse(Row.NAME1.Trim, intValue) Then

            Row.OutColumn = intValue

        Else

           Row.OutColumn_IsNull = True

        End If   

    End Sub  

End Class

Solution 2

Try applying a combination of DECIMAL and FLOAT. I tried a test of this and it worked.

(DT_DECIMAL,2)(DT_R4)TRIM(NAME1)

This first converts to a float and then to a decimal.

Comments

[user3662215]

But if you need to convert the string to an integer (as defined in the title of your question), then apply (DT_I4)TRIM(NAME1). This also worked for me, but it did drop the decimals (string value of 8.11 became just 8).

[Gerardo]

Great answer. Thanks!