tinku.reddy. Ellen and I are trying to write Excel files on a regular basis. I don't have Office installed on my server, so I can't do any detailed manipulation of the spreadsheet - only write simple output to it.

I need output with pivots and charts that are based on the data I have just written, so I have written a lot of Excel code to format these, and it is triggered from the Workbook_Open event. As well as this, it seems that SQL Server is pretty clueless about Excel destinations unless Excel is on the server, so I came up with the idea of the dummy line to tell it what kind of data to expect.

I therefore have an empty spreadsheet, with all the code already in it, which I use as a template. The column headings are in place, so I can map my extracted data to the right workbook column, and there is a line of dummy data to tell SSIS how to configure the output.

I have an SSIS package the same as yours, with a File System Task that copies the template workbook to an output area and a Data Flow task that writes my data to the copy workbook. The original template stays where it is ready for the next run.

My customers open the copy of the workbook (which has been sent to them with a sendmail task), and the code in the workbook automatically starts, creating and formatting pivots and charts, and deleting the dummy line.

There is no need to delete an old spreadsheet. The file system task that copies the template to the output area is configured to overwrite the previous file.

Sorry about the long answer but I'm hoping it's a good example for you of how to do this and why you might want to.

As I said above, I'm not convinced it's a good way - it seems incredibly clumsy. A total botch, even. But no-one has offered a better solution.

I only do this kind of thing when I need to schedule a job. For ad-hoc Excel reports I have created a number of store procedures that can be called from Excel. I find that I only need 4 or 5 parameter driven generic extracts to fulfill all our reporting requirements, and I can then use VBA to format the extracted data as necessary.

Hope this all helps

B2B