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
TSG954Asked:
Who is Participating?
 
Anthony PerkinsCommented:
I would define the table with the correct decimal data types.
0
 
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.
0
 
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.