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

Question

I have a source flat file with values such as 24.209991, but they need to load to SQL Server as type money. In the DTS (which I am converting from), that value comes across as 24.21. How do I convert that field in SSIS?

Right now, I am just changing the type from DT_STR to DT_CY, and it gives a run error of 'Data conversion failed. The data conversion for column "Col003" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".'

Do I use a Data Conversion task? And then what?

I've also tried setting the source output column to DT_NUMERIC, and then convert that to DT_CY, with the same result.

I've also tried using Derived Columns, casting the DT_STR field Col003 to (DT_NUMERIC,10,2)Col003 and then casting that to (DT_CY)Col003_Numeric. That's getting a cast error.

Solution 1

The flat file defaults to all fields being DT_STR. Use the Advanced option on editing the connection to have the numeric field as float (DT_R4). Then, in the advanced editing of the Flat File Source (on the Data Flow tab), set that output column to money (DT_CY).

Then, the field will convert without any additional conversions. The issue was leaving the source file definition as DT_STR.

Solution 2

If you don't have any null value use Data Conversion, and make sure you don't have any funny character (e.g. US$200 produce error)

If you have null or empty fields in your field and you are using Flat file source, make sure that you tick "Return null value from source.."

Another trick I have used is something like: (taxvalue != "" ? taxvalue : NULL(DT_WSTR,50)). in Derived Column transformation (you can just replace the field)

Generally SSIS doesn't convert empty strings to money properly.

Solution 3

For some reason in my scenario, the OLE DB Destination actually was configured to accept a DT_CY. However, casting to this format (no matter the length of the input and destination data, and no matter wether or not the data was NULL when it arrived) always caused the same issue.

After adding data viewers, I can conclude that this has something to do with the locale. Here in Denmark, we use comma (,) as decimal delimiters and dots (.) as thousands-delimiters, instead of the opposite.

This means that a huge number like 382,939,291,293.38 would (after the conversion to DT_CY) look like 382.939.291.293,38. Even though I highly doubted that it could be the issue, I decided to do the opposite of what I originally had intended.

I decided to go to the advanced settings of my OLE DB Destination and change the DT_CY column's type to DT_STR instead. Then, I added a Derived Column transformation, and entered the following expression to transform the column before the data would arrive at the destination.

REPLACE(SUBSTRING(Price, 2, 18), ",", ".") where Price was the column's name.

To my big surprise, this solved the problem, since I figured out that my OLE DB Destination was now sending the data as a string, which the SQL Server understood perfectly fine.

I am certain that this is a bug! I was using SQL Server 2008, so it might have been solved in later editions. However, I find it quite critical that such an essential thing is not working correctly!