I already have an SSIS package that runs perfectly that I just built with the export wizard in SQL. However, I want the Excel sheet output to have a certain formatting. I want to headings shaded and the output range in gridlines. How is it possible to make the package do this? I have Microsoft Visual Studio to edit the package, I just have never used it. Thanks, Alex K

You then need to use MS Office interoperability. Specifically you will add some .Net code into a new component namely the Script Task.Arthur My Blog

To compliment my reply above: Here is a short demonstration on how to format an Excel spreadsheet using C# (if you are on SSIS 2008): http://csharp.net-informations.com/excel/csharp-format-excel.htm Just create your own code and use it inside the Script Task.Arthur My Blog

Where is the script task? I can't even find the editor to insert any kind of code...

It is in your Tools bar.Arthur My Blog

Using .Net COM interop to adjust formatting of Excel (or any Office) documents isn't recommended by Microsoft for very valid reasons. You'd probably do better to format an Excel template file, and insert your rows into a copy of that template. Talk to me now on

That's actually what I've been trying to do. Unfortunately the package won't insert into the rows with formatting. Is there a way to force it to do this? Also how can I configure the package to clear the contents of the excel file each time it is run?

You could use an UPDATE [sheetname$] SET [columnname] = '' Execute SQL Task to blank out rows. As for the formatting, I wasn't aware you were trying to set the format on the contents of the data you were inserting. I don't believe there's a method to do that beyond using COM Interop, or an "on open" macro in Excel. (I'd try the "on open" macro in Excel...) Talk to me now on

You could use an UPDATE [sheetname$] SET [columnname] = '' Execute SQL Task to blank out rows. As for the formatting, I wasn't aware you were trying to set the format on the contents of the data you were inserting. I don't believe there's a method to do that beyond using COM Interop, or an "on open" macro in Excel. (I'd try the "on open" macro in Excel...) Talk to me now on

Using a template file, you do the following: Assuming you have header rows, you enter data on line 2. Do any formatting you desire on this data. Once you are finished, highlight the row and delete the data (do not delete the row). When you export to the template, data will be inserted from line 3 onwards. It will take the formatting from row 2. Unfortunately, with this method you are left with a blank row between your header row and data. However, you are limited in formatting options. If you want more extensive formatting, I suggest you write an "on open" macro. Chaos, Disorder and Panic ... my work is done here!

Using a template file, you do the following: Assuming you have header rows, you enter data on line 2. Do any formatting you desire on this data. Once you are finished, highlight the row and delete the data (do not delete the row). When you export to the template, data will be inserted from line 3 onwards. It will take the formatting from row 2. Unfortunately, with this method you are left with a blank row between your header row and data. However, you are limited in formatting options. If you want more extensive formatting, I suggest you write an "on open" macro. Chaos, Disorder and Panic ... my work is done here!