When I load numeric column directly to a money column, OLEDB component is truncating the value in it, but TSQL and ADO.NET components are rounding as expected.
Source value : 2.081250
Vat1: map directly ,
Vat2: map after converting to money data type
Result:
VAT1 VAT2 CONN
2.0813 2.0813 TSQL
2.0813 2.0813 ADO
2.0812 2.0813 OLEDB
2.0812 : numeric -> money directly with OLEDB
Do you have any idea? Is it default behavior of OLEDB component?
Regards,
Mustafa
Test Case:
-- Source
CREATE TABLE TEST_NUMERIC(VAT1 NUMERIC(38,6), VAT2 NUMERIC(38,6))
INSERT INTO TEST_NUMERIC VALUES( 2.081250, 2.081250)
-- Destination
CREATE TABLE TEST_MONEY(VAT1 MONEY, VAT2 MONEY, CONN NVARCHAR(20))
-- Test 1 - With TSQL
INSERT INTO TEST_MONEY
SELECT VAT1, CAST(VAT2 AS MONEY), 'TSQL' FROM TEST_NUMERIC
-- Test2 - With SSIS, ADO NET and OLE DB components:
ADO.Net Source:
SELECT VAT1, CAST(VAT2 AS MONEY) VAT2, N'ADO' AS CONN
FROM TEST_NUMERIC
Ado .Net Destination: TEST_MONEY
**OLE DB Source:**
SELECT VAT1, CAST(VAT2 AS MONEY) VAT2, N'OLEDB' AS CONN
FROM TEST_NUMERIC
OLEDB Destination: TEST_MONEY
The maximum scale for DT_CY (SSIS data type for money) is four and OLE DB connections do support this data type, which by the data in your example (2.081250), would be 2.0812. Since you're doing the casting within SQL Server for the V2 column, this is rounded up to 2.0813 before SSIS processes it. The ADO equivalent of money would be decimal (reference here). So while you can add the money column in your ADO.NET destination and map to the the numeric data type of your source, the destination money data type is still interpreted as decimal, which preserves the longer scale, and in this case would round up to the 2.0813 value that you're seeing here.
