manibad (1/2/2013)

---

Hi,
I need to export an Excel sheet with special formats (mandatory) where SQL is the source. I have achieved it using Scripts via script component in SSIS but my question is there any other way in doing it so.
Please explain it if there is a way in doing it so.
Thanks in advance:)

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.

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.

manibad (1/7/2013)

---

I will list out the things what i have done.
1.In data flow task,OLE DB source->Sort->Data conversion->Excel destination(target is Format.xlsx(template file))
2.In file system task,Source path is Format.xlsx(template) and destination file name is destiny.xlsx.Here the destination file is the copy of template file.
3.Package will be executed in this order Dataflow task->File system task

Note:Format.xlsx was placed after assigning the special formats to the worksheet.

but still i am getting the exported file with general format.I dint get it with special formats.

Now please tell me where i went wrong.So that i will be able to rectify it and proceed without making error.

opc.three (1/7/2013)

---

Pre-requisite: open Format.xlsx (template file) in Excel and apply formatting as needed. Save to disk in original location as original name Format.xlsx.

SSIS Workflow:

1. In file system task, set to Copy source path Format.xlsx (template file) to destination file name is destiny.xlsx (copy of template file). Here the destination file is the copy of template file.
2. In data flow task,OLE DB source->Sort->Data conversion->Excel destination (target is destiny.xlsx (copy of template file))
3. Package will be executed in this order File system task->Dataflow task