how to call dts from a job

Posted on 2004-11-29
Last Modified: 2013-11-30
I have a DTS package that runs in about 10-15 min. I am currently using a stored proc using the sam query as the dts. it takes 20-30 min.

i call the store proc from a job is there a way to call a dts package from a job???
Question by:LeeHopkins
    LVL 13

    Accepted Solution

    yes, , right mouse click on the DTS job in EE , it gives you the option to schedule as a job

    Author Comment

    yes but i need to call it in sequence of other store proc
    step 1 Store proc
    step 2 stored proc
    step 3 DTS
    step 4 stored proc
    step 5 dts

    each step is dependent on the one before it
    LVL 13

    Expert Comment

    what i did was schedule the DTS package, as an example,  then schedule the real job and copy the command of the step over to the new step (edit paste style)
    you can set each step to proceed only if the previous step succeeded.
    sorry about the EE (its monday) , should be IE.

    LVL 10

    Expert Comment

    You can create a multi-step job that does just that!!!

    do as KarinLoos suggests and create a schedule to run the DTS then (in EE) expand Management then expsnd SQL Server Agent, Click on Jobs. the right hand pane will show list of jobs.
    Select you job by clicking on it
    right mouse click, select properties
    click "steps" tab
    Click insert to add step above DTS
    enter details to run your stored procedures

    add other steps as required
    Save and close

    ensure that the steps are due to run in the correct order

    LVL 13

    Expert Comment

    call a dts package from a job???
    --> Easy way,


    use dtsrun ...
    Create a Command Shell Step and place the dtsrun vars in the step, execute a DTS package saved in the SQL Server msdb database, use:
    dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password

    Author Comment

    I am unfamilure with a command shell step
    LVL 13

    Assisted Solution

    OK, .. in your command step in the edit view, choose Operating System Command (CmdExec) --> This will be where it normally says Transact SQL (TSQL )...

    Then put in the dtsrun.... text.
    You might want to take a moment to have a look at the different available job step types, ....
    (If nothing else, you might find that you can run all sorts of items here that you will find very useful in future).
    LVL 34

    Assisted Solution

    Even better, run the command DTSRUNUI from a DOS prompt or Start/Run and the GUI will create the command line that you need to execute your package.  Then, like suggested above, schedule.

    LVL 4

    Expert Comment

    right click dts>schedule package> can shedule a job here...> ok.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now