Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to call dts from a job

Posted on 2004-11-29
12
Medium Priority
?
461 Views
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???
0
Comment
Question by:LeeHopkins
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 13

Accepted Solution

by:
KarinLoos earned 100 total points
ID: 12696391
yes, , right mouse click on the DTS job in EE , it gives you the option to schedule as a job
0
 

Author Comment

by:LeeHopkins
ID: 12696419
yes but i need to call it in sequence of other store proc
IE
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
0
 
LVL 13

Expert Comment

by:KarinLoos
ID: 12696522
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.

HTH
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:RichardCorrie
ID: 12696593
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

/Richard
0
 
LVL 13

Expert Comment

by:danblake
ID: 12696946
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
0
 

Author Comment

by:LeeHopkins
ID: 12697006
I am unfamilure with a command shell step
0
 
LVL 13

Assisted Solution

by:danblake
danblake earned 100 total points
ID: 12697126
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).
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 100 total points
ID: 12697936
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.

Brett
0
 
LVL 4

Expert Comment

by:aaaaaa
ID: 12747179
right click dts>schedule package>...you can shedule a job here...> ok.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

577 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