Problems with Exporting to Excel


Experts, I have a question.


I have a requirement that the users would like some data pumped into
Excel from our SQL Server database. I have the sp's written to extract
the data. My problem lies in the export from SQL Server to Excel.

Since they want the reports to be delivered in 1 documents I've decided
to use worksheets. I have the template .xls created and have that
copied in.

The steps of the DTS are as follows:
1) Copy over template to \export directory.
2) Run stored procs to prep data
3) Export Data
4) Send .xls via mail

The problem is in step 3. I have 12 different pipes from the one
source into the .xls. I did this to avoid having to have multiple
sources and multiple sql tasks to run 1 stored proc. I'm having the
following issues doing so.
1) Sometimes the pipes fail giving error that the document is in use
and cant resubmit or reopen the pipe. I believe this is caused by one
of the other pipes blocking the current pipe. I have found no way to
sequence the pipes for priority or steps.
2) Sometimes on some of the sheets when I open the document appear to
be blank, however, when I scroll down there is data. I'm getting 30-40
blank rows between the header and the first row of data.

Any help or ideas would be greatly appreciated. In the mean time, I'm
going to start breaking this appart because I have that feelings thats
just how I have to do it.. :(

Thanks in advance!

Pete Miller

.