how to convert currency to decimal in Azure Data Factory's copy data activity mapping

0
votes

In Azure Data Factory i have a pipe line and pipeline has one copy data activity that has a source a REST api and a destination a SQL DB Table.

In the mapping of this copy activity i am telling that which columns from REST dataset (on left) will be mapped to which columns on SQL dataset (onright)

enter image description here

there is a json property in Rest "totalBalance" that is supposed to be mapped to "Balance" field in DB Tables.

Json has "totalBalance" as string for example "$36,970,267.07" so how to convert this into decimal so that i can map it to DataBase table?

do i need to some how use mapping activity instead of copy activity ? or just the copy activity can do that ?

2
azureazure-data-factoryazure-data-factory-2

2 Answers

1
votes

finally what worked for me was having a copy activity and a mapping activity.

Copy activity copies data from REST to SQLtable where all the columns are VARCHAR type and from that table a mapping activity sinks data from SQL(allString) tables to actual destination SQLTable.

But between mapping and sink i added "Derived Column" for each source property i want to convert and in expression of that derived column i am using expression like this

toDecimal(replace(replace(totalAccountReceivable, '$', ''),',',''))

enter image description here

0
votes

The copy activity can not do that directly.

There are two way I think can do this:

First:change decimal to varchar in DB Tables.

Second:add a lookup activity before copy activity and remove the '$' in 'totalBalance' column,then add an additional column like this:

enter image description here

Finally,use this additional column map to 'Balance' column.

Hope this can help you.