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

The reason for the missing rows will be you probably tried to delete the rows (or someone did)
As for the document being in use. In the workflow properties of the task there is a "Close connection on completion" checkbox.
Check it.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"PeteMiller" <peter.miller (AT) educate (DOT) com> wrote

I dont think it was that, because no one uses the document until its
sent via email.

Then make DTS only execute 1 task at a time in the package properties or use workflow to split the datapumps. You should still
close the connection on completion in each datapump though

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"PeteMiller" <peter.miller (AT) educate (DOT) com> wrote

Cool.. thanks for your response. I was thinking that is what I was
going to have to do.