May 27, 2011 at 8:09 am
Hello,
I am trying to grab some data (including currency data) from Oracle to Excel in SSIS. So
1) I create the table in Excel via 'Execute SQL Task' and Excel connection manager. - I think 'MONEY' should be the type for currency data. But let me know if this is wrong.
CREATE TABLE `BBA_ByTask` (
`FiscalYear` NVARCHAR(10),
`Month` NVARCHAR(10),
`ProjectNumber` NVARCHAR(10),
`ProjectName` NVARCHAR(100),
`Budget` MONEY,
`CurrMonthExpenses` MONEY
);
2) Create a 'Data Flow Task' to pull data from Oracle, convert data types and send to an Excel worksheet. The task has three components, each doing the job I just mentioned.
a. OLE DB Source - pull the data from Oracle. When I do preview, I can see currecny data with 2-digit precision.
For example, budget = 78343.34
b. Data Conversion - convert 'Budget' to 'currency[DT_CY]'
c. Excel Destination
However, when I open the Excel file, the precision is lost, kind of - see the '00' to the right of decimal point. budget = '$78343.00'
Where I am doing wrong? How do you handle currency data in this setting? Thanks bunch for any help!
May 29, 2011 at 11:46 pm
If you set-up a dataviewer right before the Excel Destination, is the precision there still correct ?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 31, 2011 at 7:12 am
I thought this was a very common task for people doing SSIS... I spent hours and hours debugging/testing and found that I need to change 'Data Type Properties' for 'Output Columns' to 'currency [DT_CY]' for the currency columns in the 'OLE DB Source' component. The 'Data Conversion' component doesn't help at all. Don't know why. Hopefully this will save other people's time.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply