opc.three (1/3/2013)
The only way to format an Excel Workbook programmatically using SSIS is to leverage the Excel Object Model from a Script Task.One technique I have used in the past however is to create a Workbook formatted the way I like and then use that to export into. When targetting an Excel Worksheet in an OLE DB Destination the formatting is ignored. So format your Workbook and save it as a "template" reachable by your SSIS package. In your SSIS package use a File System Task to copy the "template" into a new location and export your data from SQL Server into the copy.
+1 to this second idea.
Or, in a similar vein, have an Excel template which contains a 'Format' button & associated VBA code to allow the formatting to be done by whoever opens the Excel file after the export has completed