Search code examples
sql-serverssisetlssis-2012bids

SSIS - Data Conversion from DT_STR to int

How to apply the below transformation in SSIS data flow task

cast(gift_amount as int)/100

In Derived Column Transformation Editor, I am unable to change the data type. It gives 0xC0049064 error upon giving typecast function.

Solution

  • You can acieve this using a script component

    1. Add a Script Component
    2. Mark gift_amount as input column
    3. Add an output column of type Decimal or float (ex outColumn)

    4. Add the following code: (using vb.net)

      If Not Row.giftamount_IsNull AndAlso
   Not String.IsNullOrEmpty(Row.giftamount) Then

    Row.outColumn = CDec(CInt(Row.giftamount) / 100)

Else

    Row.outColumn_IsNull = True

End If