I am working on SSIS 2008. My task is to get the stats from a table in OLE BD and save it in a flat file. I am using two fields in a table saved in OLE DB the fields are in NVARCHAR data type. Amount and Currency are the two fields. I wanted the SUM of Amount, so tried using Decimal and Numeric to cast but it didnt work so used Money and it worked.
My Query is:
select sum(cast(PAID_AMOUNT as money)) as Amount, CUR as AmountCurrency, COUNT(*) as Records
from Raw_table group by CUR order by 2
I am using a OLE DB source editor - SQl command option to query the statement. Clicking on the preview button displays the result without any error.
But when I execute the task i am getting an error:
[Inv Stats [1]] Error: There was an error with output column "Amount" (17) on output "OLE DB Source Output" (11). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
so I captured the error causing row in a flat file by redirecting the error. and the file captured :
Amount,AmountCurrency,Records,ErrorCode,ErrorColumn
3073904391,JPY,9806,-1071607691,17
I am new to SSIS and have poor knowledge on data types. Please help. Apologies if my description is not clear as this is my first post.