Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38

    Question Unanswered: DTS launch from SQL Server Agent

    Hi,

    I want to build a task that does 3 things automatically at a fixed time interval:

    1) Refresh the data (copy newly inserted data from a datamart to a datawarehouse)
    2) Run a program (exe) to produce some external reports
    3) Refresh an OLAP Cube with the new data so that users can access it

    Basically, using SQL Server Agent, setting up steps 1 and 2 was pretty straightforward (for step 1, the main point of entry is a single sp).

    I ran into some trouble figuring the easiest way to complete step 3. I created a DTS package with one object that updates the Cude on my server. This seems to be working fine. Then, I created step 3 in Agent and chose 'CmdExec' as the type of command. I then referenced the external exe 'dtsrun' to execute my DTS package.

    Everything seems fine but I'm not sure I used the "best" way of doing things. Since the SQL Server Agent and DTS manager are both SQL Server tools (so is the Analysis services), I'm wondering if there wasn't an easier (thus more robust) way of completing my 3rd step?

    Anyone? How is this done where you've worked?

    Thanks,

    Skip.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Well, that is the technique that MSDN suggests.

    You could always make the DTS Package into a SQL Agent job, then run the job using sp_start_job (but that is deviant).

    -PatP

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    As always I suggest this kBA http://support.microsoft.com/default...;EN-US;Q269074 to help DTS to schedule as a job.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •