SSIS how to convert string (DT_STR) to money (DT_CY) when source has more than 2 decimals
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.
Answer
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.