I have a column credits decimal(4,2) with data like

0.473, 2.454, 1

See here:

When querying the data in SQL Server, I see values as 0.47, 2.45 and 1.00, exactly the way I want. But when I run an SSIS package in Visual Studio with an OLE Db source for the SQL Server table and a flat file destination as CSV, the 1.00 value is extracted as 1.

I tried changing the column data type in the SSIS flat file manager to string or decimal, and also in input out properties; nothing worked. Even though the SQL Server source data type for the column is decimal and numeric in the advanced editor for the SSIS Source, I still see the 1 instead of 1.00.

I want to always see the decimal places for this value. How can I fix this?

I have a column credits decimal(4,2) with data like

0.473, 2.454, 1

See here:

https://dbfiddle.uk/u_upph_9

When querying the data in SQL Server, I see values as 0.47, 2.45 and 1.00, exactly the way I want. But when I run an SSIS package in Visual Studio with an OLE Db source for the SQL Server table and a flat file destination as CSV, the 1.00 value is extracted as 1.

I tried changing the column data type in the SSIS flat file manager to string or decimal, and also in input out properties; nothing worked. Even though the SQL Server source data type for the column is decimal and numeric in the advanced editor for the SSIS Source, I still see the 1 instead of 1.00.

I want to always see the decimal places for this value. How can I fix this?

• sql
• sql-server
• ssis

• When I do as you described, create a table with credit decimal(4,2), populate it; and create a new SSIS package with one DataFlow component, and OLEDB driver to read from this table, and write to a flat file; all with default SSIS settings; I do get 1.00 in the file. Are you certain that the file has 1 and not 1.00, by opening it in a text editor, like Notepad, Notepad++, etc, and not EXCEL? – tinazmu Commented 2 days ago

1 Answer 1

Try using CONVERT() with style = 0 in your source query to change the value to a string, instead of doing the conversion at the target.

select convert(varchar(5), credits, 0) 
from test;

https://dbfiddle.uk/1z43EmWh