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.
Using file system task i was able to copy the file template from one location to other.Using data flow task i manipulated the source using source and then i coded to execute the file system task.
In dataflow task,
I sorted the data and then i connected with excel destination.Where the destination is the template.
I am getting the output without special formats.
Data flow task and file system task was completed successfully.
What to do for rectifying the above and obtaining the desired output.