Link to home
Start Free TrialLog in
Avatar of TSG954
TSG954Flag for United States of America

asked on

SSIS Flat File Destination Decimal Formating Issue

I have created an SSIS project that will take a CSV file, upload it into a SQL Table, then it performs a query on that table with a few other tables and saves the result as a CSV and then emails the CSV file. The problem that I am having is all of the decimal fields in the query are being saved with the scale up to 10 in length. The query that I am using is actually performing calculations and rounding to 2 decimal places using the round function, but when it gets exported to the Flat File destination the rounding is no longer used. I also have set the properties in the Flat File connection to have a scale of only 2, but that didn't work either. Any suggestions?

Thanks
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

>>The problem that I am having is all of the decimal fields in the query are being saved with the scale up to 10 in length. <<
It sounds like you have made the mistake of using the float data type in your table.
Avatar of TSG954

ASKER

The data types of the columns are actually varchar, I am using cast to convert this into a decimal with 3 decimal places. Is there a way in SSIS to round the numbers before exporting to the flat file destination? In the OLE DB source that I am using it performs the rounding of the numbers fine and when I preview the output all is well in the world, but when I connect this to the Flat File Destination it drops the rounding. Is there a transformation that I can use between the OLE DB Source and the Flat File Destination or do I need to restructure the table to use decimal types?
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial