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?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
>>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.
TSG954Author Commented:
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?
Anthony PerkinsCommented:
I would define the table with the correct decimal data types.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.