CODEWITHSUNDEEP
sql-serverssisetlssis-2012derived-column
Gerardo
Gerardo

Reputation: 369

Convert String to Int SSIS

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

enter image description here

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

Upvotes: 3

Views: 21419

Answers (2)

Hadi
Hadi

Reputation: 37313

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

enter image description here

You can read more in this useful blog article:

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

Upvotes: 4

user3662215
user3662215

Reputation: 526

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.

Upvotes: 1

Related Questions