I have around 50 stored procedures which are giving me data necessary
for a lot of reports. I wanted all this stored procedures to go into
the same excel workbook, different sheets; one table goes into one
sheet.

So far so good; I thought that defining a single dts package is
simplier, so I did. Basically what I'm going to have is one SQL
source, one excel Destination and around 50 Transform Data Tasks.

Right now I'm facing four different problems:

1. Is there a way to simplfy not to be necesarry to define 50 Tasks
but one parametrized ?
2. How can I define the precedence of the tasks in this case (I tried
with workflow on completion or success but still the tasks start all
on the same time and sometimes they try to use simultaneouslly the
same source and I get errors)
3. I want the data to be deleted then appended, not just appended
4. Is there a way to perform some crosstabs pivot tables throu' DTS or
I just have to send the data to excel and then to "pivot" them

THKX in advance


Adrian

Q: Is there a way to simplify not to be necessary to define 50 Tasks
but one parameterized ?
A: It all depends on what you are using for the input parameters. You can
use as many ? parameters as needed, you just need to be able to provide them
as input to the task. These parameters can come from other SQL queries, INI
files... Just evaluate where they can come from and adapt your package to
those input sources...

Q: How can I define the precedence of the tasks in this case (I tried
with workflow on completion or success but still the tasks start all
on the same time and sometimes they try to use simultaneously the
same source and I get errors)
A: I bet you are getting Lock thrown when your script runs. What you will
have to do is run the SP's that use the same data sources and/or tables one
after asynchronously or one after another. You can set the on success or on
failure values to the task just before it. If they run at the same time and
lock the table, your script will fail. You must keep these tasks from
running at the same time...

Q: I want the data to be deleted then appended, not just appended
A: What you can do is in the first task create a VBS script that uses the
Excel object model to delete the contents of the spreadsheet. If you don't
do this, the data from each run will get appended to the spreadsheet from
the run before. The Excel Objs can be accessed from any VBScript...

Q: Is there a way to perform some crosstabs pivot tables through DTS or
I just have to send the data to excel and then to "pivot" them.
A: Not in native DTS tasks, but once again, you could probably use the Excel
objs to do this as the last task in the package after the workbook is
completely created. Just explore the possibilities of the Excel objects and
you will be surprised what you can do programmatically with a VBScript /
ActiveX task.

HTH
Ozone




"Adrian" <thor76 (AT) home (DOT) ro> wrote

Thanks a lot for the response....
From all the questions, all that is left is the final one. I'm going
to search for an example of "script" transformation of the data "on
the fly" to have the Pivots already copied.
Maybe you can help with a such a script example...anyway, it's been
very helpful so far.

Rgds,

Adrian