SSIS - Data Conversion from DT_STR to int

2
votes

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.

2
sql-serverssisetlssis-2012bids

2 Answers

2
votes

You should try

((DT_I4)gift_amount)/100

Also, in the Derived Column Editor window, you can add other types of casts - take a look through some of those options in the upper right hand part of the editor.

ssis dco editor

1
votes

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