Hi,

I want to set up DTSRun to be executed by some particular users so that they
can update specific cubes on demand (e.g. when they have just added some
journals and want to see the effect on account balances in their cube).

I have got DTS running (using encrypted command line options) on a client
machine, but I run into issues when I try to process a cube this way.
Apparently I need to install the appropriate client software using
dtstasks.exe, which is in SP3 of SQL Server 7.0. We are already running SQL
Server 2000, so my questions are:

1. where do i get a copy of dtstasks.exe? (I haven't actually managed to
find this SP let alone download it - I will keep looking) Is it already on
the disk?
2. will this work OK with a 2000 SQL Server?
3. Is there a better way to do this, i.e. a secure way for particular
non-technical users to update particular cubes on demand?

We are running XP on client and server. I am not sure what SP we are up to
on the server, but I would have thought SP4.

Thanks in advance,

You could add a beginning step to the dts package that checks a table for a
'go' value that could be updated by the user. If the job finds the 'go'
record in the table the flow control could then execute the step or other
dts package that processes the cube. You would then just have to give users
a way to insert a record into the table when they wanted the cube processed.
The package could then execute on the server and you wouldn't need to worry
about the client machines.

Walter


"Les Russell" <LesRussell (AT) discussions (DOT) microsoft.com> wrote

Hello,

Thanks Walter - I am not clear on a couple of points, so I hope you can
help. I think if I try to execute any task, even a package, it will still
execute on the client machine. How do I get it to execute on the server? -
wouldn't that have to be run by the SQL Agent or similar?

Are you suggesting some sort of separate, scheduled monitoring job which
frequently checks the status of the added record and then fires off the cube
processing task if the record exists, thereby running on the server?

Thanks
Les

"Walter Mallon" wrote:

The package would be run on the server, not the client. There would be a
sql server agent job that runs say every five minutes and checks a table in
a database. I've used this before and it works fairly well, having a job
that just runs a select statement puts almost no load on the server. You
could have a execute sql task that selects * from table where hasrun = 0 for
instance. If @@rowcount = 0 then use raiserror to force step failure, else
use on success to go to an execute package task that calls your package.

You'd just need to give your user a way to write an entry to the table and
have the dts package update the hasrun = 1 at the end for that entry. If
the user doesn't have sql tools and you don't want to write a front end then
you could give them an Excel file with a VBA module in it that connects to
the database and writes an entry to the table. That's not that hard if
you've done any vb programming before (I could give you an example if you go
that way).

There may be a reason why you want the package to run on the client;
however, having the package run on the server would normally be more
efficient since the package would then not have to pull data back and forth
from the server to the client. The only reason why you might not want to do
this would be if the cpu, I/O, and memory are already taxed on the server.

Walter

"Les Russell" <LesRussell (AT) discussions (DOT) microsoft.com> wrote

Walter,

Thanks for that. I had sort of thought of something like that, but was
worried it may have problems. However you have obviously made it work and
seem happy with it, so I'll give it a go.

I don't especially want the job to run on the client - I was mainly
concerned about a quick response - i.e. it updates as soon as the user
"presses the button". Hopefully five minutes won't cause a problem, but it
is amazing how demanding users are getting (although they never want to spend
any money of course).

Thanks again,
Les

"Walter Mallon" wrote: