I'm pretty sure that what I do is not the best way but if you try it, you should get over that problem.

My template has headings in the first row and dummy data in the second. So text fields are filled with a character to their maximum size, and number fields are populated with the biggest number likely to come their way. If there is a decimal point that is shown too.

So fill text fields with all 'A's or similar, set integers to 123456 (for as many positions as you need). Decimals will be 1234.56, and dates will be 01/01/2001 or whatever format you prefer.

Your ssis package will then populate from line 3 onwards, and you need a macro that runs on the workbook open event that detects the dummy line and deletes it.

I'm ok with this until we need to make a change to the spreadsheet. The package then turns all the text fields into WSTR format, and then gives an error. I don't know how many times I have edited the advanced settings to change each field back to STR.

You can bet there is a better way to do this, but I have asked and asked, all over this and other boards, and no-one has answered yet.

Recently I have discovered the joys of sp_makewebtask as an alternative way of creating excel output. Indeed I am increasingly avoiding SSIS wherever possible as, without any expert advice, it is more trouble than it is worth.

Here's an example of makewebtask that I have just built:

DECLARE @outputfile NVARCHAR(255)

DECLARE@query NVARCHAR(500)

SET @outputfile = 'e:\mirror_files\bliff.xls'

SET @query = 'EXEC [dbo].[usp_Report_PM_Weekly_Overdue_Reforecast] @RefcastDetectStart = N' + CHAR(39) + '2008-11-14'+ CHAR(39) + ', @RefcastDetectEnd = N' + CHAR(39) + '2008-11-21'+ CHAR(39)

EXEC sp_makewebtask

@outputfile,

@query,

@colheaders =1,

@FixedFont=0,@lastupdated=0,@resultstitle='Weekly overdue, due in next 28 day and reforecast reports'

It has certain restrictions but I've normally been able to accomplish what I need with an Excel macro.

Hope that helps

B2B