Re: DTSRun and Batch file

From: Jeff Lockard (jlockard_at_rwd.com)
Date: 04/05/04

  • Next message: JN: "RE: Problems running a scheduled DTS package" 
    Date: 5 Apr 2004 13:38:24 -0700

    Darren Green <darren.green@reply-to-newsgroup-sqldts.com> wrote in message news:<vCWAP3NuXvbAFw3T@sqldts.com>...
    > 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 -
    I did manage to get the SQL Server Agent job running, but I can only
    get it to run when the owner is sa. I was trying to change it to a
    generic SQL login/user to avoid sa compromise. I am going to look at
    the database and table permissions to see if my generic login/user has
    the appropriate permissions, but wanted you to know that I got it
    working. Still would appreciate your further input on how to allow my
    users to execute this job (e.g., ADO, web forms, Win forms).

    Thanks...Jeff

  • Next message: JN: "RE: Problems running a scheduled DTS package"

    Relevant Pages

    • Re: DTS Process Cube Task
      ... It has a command-line interface -- so you would use a DTS Process Task ... private dimension that contained more than 2 million members. ... occasionally the cube would process, and that was throwing me off. ... DTSRUN of the package from the command-line. ...
      (microsoft.public.sqlserver.olap)
    • Re: Is there a limitation on the number of Tasks that can Run in Parellel ?
      ... Allan Mitchell MCSE,MCDBA, www.SQLDTS.com - The site for all your DTS needs. ... >> Before the sending the mail yesterday, I did increase the number of Tasks executed in Parallel to 20 in the DTS Package>> Properties window. ... >> Step Error Source: Microsoft Data Transformation Services Package Step Error Description:SubStep ... Step Error code: 80040428 Step Error Help>> File:sqldts80.hlp Step Error Help Context ID:1100>> Step Error Source: Microsoft Data Transformation Services Package Step Error Description:SubStep>> 'DTSStep_DTSDataPumpTask_10' failed with the following error: OleDBProperty 'General Timeout' already exists in the collection. ...
      (microsoft.public.sqlserver.dts)
    • RE: DTS Hanged When Scheduled
      ... Please feel free to respond to the newsgroups if you need any ... Microsoft Online Support ... | Subject: RE: DTS Hanged When Scheduled ... this behavior occurs because the package is using ...
      (microsoft.public.sqlserver.dts)
    • Re: Import and Schedule DTS on MSDE
      ... The dtsrun utility executes a package created using Data Transformation Services. ... Displays the command prompt options. ... Displays the encrypted command used to execute the DTS package without executing it. ...
      (microsoft.public.sqlserver.tools)
    • Re: Is there a limitation on the number of Tasks that can Run in Parellel ?
      ... > executed in Parallel to 20 in the DTS Package Properties window. ... > Step Error Source: Microsoft Data Transformation Services Package ...
      (microsoft.public.sqlserver.dts)