How to convert a string to money in the Data Conversion Transformation process in SSIS?

user_620720 0
Viewed 1235

How to convert a string to money in the Data Conversion Transformation process in SSIS?

My Source file is an Excel File. I added the Data Conversion Transformation to convert the Price column to the Data Type "currency[DT_CY]".

Source value : $50,240.30

Expected result : 50240.30

SQL Destination table has the column Price with the Data Type : Money

However, conversion fails with the following error :

The "Data Conversion.Outputs[Data Conversion Output].Columns[Price]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Out

This query works.. fine.. However, this query is applied only after we received the data to the Table.

We are trying to get the data while loading into the table as "Money" data type.

select convert(MONEY, [Price]) AS [Price] , * from dbo.StagingTable

Per Nick's comment, source file conversion is working fine. However, we need the string to be converted to Money data type.

Source Price : $55,176.80

Derived Column Expression : REPLACE(REPLACE([Price],"$",""),",","") Data Type : DT_WSTR

Output Price : 55176.80

sql-server-data-tools etl ssis sql-server sql
2 Answers

As @Nick.McDermaid suggested, try cleaning the data and perform casting using derived column, before inserting it to SQL Server. Also make sure you check for NULL values. You can use the following expression:

ISNULL([Price]) ? NULL(DT_CY) : ([Price] == "" ? NULL(DT_CY) : (DT_CY)(REPLACE(REPLACE((DT_WSTR,50)[Price],"$",""),",","")))

After removing the $ sign and commas ,, cast operation will work fine.

Helpful link

user_70312300
user_99408030
Related