Skip to main content
How to format columns when exporting to excel in SSIS
Entry Date: Rate:
RSS Description:

When exporting data to csv files, it is not possible to format columns when opening the csv file in excel without any macros or user actions. E.g. when you export the value 012 and open the file in Excel, the value will be displayed as 12. Even if you export the value as '012, "012" or "'012" the result after opening in Excel is not satisfying as the value is now shown as '012.


SSIS is a bit limited when generating Excel, you can just generate plain data.

This post explains you a trick to add formatting to your tabular reports, the tricks consists in format an Excel file and use it as template (Excel destination but not over a new file, write over an existing one). This solution won't work for all scenarios, and won't give you all the formatting that you expect, but at least it's something to try before going for a custom script task (XMLSS, Open XML, ...)