Re: DTSRun and Batch file

From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 04/03/04 

Date: Sat, 3 Apr 2004 18:35:42 +0100

In message <279e1d30.0404020827.60e8ba2e@posting.google.com>, Jeff
Lockard <jlockard@rwd.com> writes
>Hello -
>I'm running SQL Server 2000 and I've built 3 OLAP cubes that are
>refreshed via DTS package, including an Analysis Services task to
>re-process each cube. Now, I'd like to provide the ability for select
>users to refresh/re-process the cubes without installing SQL Server
>EM, Analysis Services on the client machine. Is this possible?
>
>I thought that I could do this with DTSRun, so I reviewed the
>redist.txt and have built a small Zip file with a batch file to
>register the DTS dll's and copy the rll's to the appropriate directory
>on the client machine.
>
>I performed the zip "install" and register tasks on a client machine
>without AS or EM. Now, when I run the batch file that calls DTSRun to
>execute the DTS package, I am getting the following errors:
>
>DTSRun: Loading...
>
>Error: -2147221005 (800401F3); Provider Error: 0 (0)
> Error String: Invalid class string
> Error Source: Microsoft Data Transformation Services (DTS) Package
> Help File: sqldts80.hlp
> Help Context: 713
>
>I've read some of the other postings, but not sure if they apply
>directly to my case. Bottom line question: Is it possible to build a
>tool for non SQL Server/AS client machines to run a DTS package that
>reprocesses cubes?
>
>Thanks in advance.
>Jeff

No because DTS is a client side tool, and as you know you need to
install the DTS DLLs on the local machine. So the DTS package is running
on that local machine from where it is called. The error is probably
because you haven't installed the OLAP processing task on that machine,
so the package load fails.

You need to abstract the package execution location from the user, The
package needs to run on a machine with all the required dependencies,
best place is probably the server. Perhaps one method is to allow the
user to start a SQL Agent Job that runs the package. You only need ADO
for example to call the sp_start_job stored procedure. How you provide
this as an interface is up to you, but both Win form and web form
methods could easily be used.

Make sense? 

-- 
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Relevant Pages
Loading