question

It depends what formatting you need and how should loook the output. In general there are 3 possibilities. 1. Prepare a formatted template. Then SSIS can copy that template into destination and write data into it. 2. Write data into excel firle directly. THen in next step a SSIS Task can open the excel and manipulate the formatting of individual cells using VB.NET or C#. (In this case you will need excel installed on th emachine on which SSIS is running. 3. Prepare a excel template without formatting but which will contain macro, which can do whatever formatting necessary. Then SSIS again copies such excel into destinetion and writes data. Then there are trwo possibilities - execute the macro from withing SSIS Script Task (again ecesll installation is necessary) or make the macro auto start and then the formatting will be done when user opens the excel. If the formatting doesn't need to be something special, than I suggest to go by option 1, which is probably the easiest one, but it really depens on the formatting needs. Another option (some times can be better) is to use SSRS instead of SSIS for producing excel output. You simply develop a report which will contain whatever formatting necesary and then you nca create a subscription to that report which will automatically generate the excel files. In SSRS you have much better possibilities of formatting. If the data needs to be prepared by some complex ETL logic, then the SSIS and SSRS can be combined to produce perfect output.

SSIS is not the right place to do it. You will face all kind of problems even after installing some client software (particularly Excel interop). So the best you can do is to have a template file and add a macro in it which will format the sheet. But if you want to continue let say Excel interop path, then SSIS script task may help you.