SSIS flat file destination generating more digits than in the database

Question

I have a simple SSIS package creating flat file from SQL view. The data in the view is two digits like 391.61 but when SSIS creates the flat file it is converted to 391.61000000000001. Is there something I missed?

Answer 1

To correct this, you need to modify the mechanism for extracting data. Within your OLE/ADO/ODBC Source, change it from Table or View Name and write the equivalent query and explicitly shape your data as expected.

Before - Table or View Name

dbo.MyTable

After - Direct Query (name approximiate)

SELECT X.Col1, X.Col2, CAST(Col3 AS decimal(7,2)) AS Col3 FROM dbo.MyTable AS X;

Where Col3 is the column that needs coercion.

You can probably do this with a data conversion component or a derived column component but it's far cleaner just shaping the data before you bring it into the data flow.

The other thing to be aware of, SSIS may or may not "hear" the change in metadata - based on whether the new column width will fit into the old one. And I think a decimal can fit in a float. Switching from table or view name to a query probably is radical enough to reset the metadata, otherwise use a query like SELECT 1 as FooBarBlee and click OK. Observe all the downstream components show red Xs. Replace that query with the correct query and now your column names and metadata are corrected and you should be good.

If not, then you need to examine the definition of your Flat File Connection Manager. On the Columns tab, examine Col3 and see how that was defined and adjust it as needed.