I am working on SSIS 2008. My task is to get statistics from a table in OLE BD and save it in a flat file. I use two fields in a table stored in OLE DB, the fields are in the NVARCHAR data type. Amount and currency are two fields. I wanted SUM of Amount, so I tried using Decimal and Numeric for casting, but it didn’t work the way Money was used, and it worked.
My request:
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 the original OLE DB editor, a parameter of the SQl command to query the statement. Pressing the preview button displays the result without any errors.
But when I complete the task, I get an error message:
[Inv Stats [1]] Error: An error occurred with the output column "Sum" (17) at the output "OLE DB source output" (11). Column state returned: "Conversion failed because the data value overflowed the specified type.".
so I grabbed the error causing the line in the flat file, redirecting the error. and the captured file:
Amount,AmountCurrency,Records,ErrorCode,ErrorColumn
3073904391,JPY,9806,-1071607691,17
I am new to SSIS and do not know the data well. Please help. Sorry if my description is not clear, as this is my first post.
source
share