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

Question

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

that would be in the Derived Column with substring , right ? — goofyui
Yes it's in a derived column but use something like REPLACE(REPLACE(<yourcolumn>,'$',''),',','') docs.microsoft.com/en-us/sql/integration-services/expressions/… — Nick.McDermaid
Oh... maybe SSIS expression use " instead of ' for string literals. I can never remember and the help doesn't contain any examples, Try REPLACE(REPLACE(<yourcolumn>,"$",""),",","") — Nick.McDermaid
I just noticed @Hadi clarified this in his answer - please accept his answer if it's correct — Nick.McDermaid

Hadi Hadi · Accepted Answer · 2019-01-16T01:37:26

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

SSIS how to convert string (DT_STR) to money (DT_CY) when source has more than 2 decimals

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

2 Answers