August 16, 2007 at 9:09 pm
hi experts
How to Schedule Two Packages, One is Dev Box Another is in Prod Box.
After Successful Completion of Package in Dev Box then The Package in Prod Box should Run.
there is a time dely between 1st package and 2 nd package . how can i do that.
iIs there any Possible way to Schedule Two Packages on Two boxes.
senerio is:
i need extract data from tera data load the same in to sql server 2000 database.
my DTS package is extracting data from tera data and dumping into development box. after execution of 1st package, same work should run in production box(in sql server). this is done by my second DTS package.
here the problem is i need time gap between 1 st package and 2nd package atleast 4 hours.
how do i schedule
Thanks in Advance,
August 17, 2007 at 8:25 am
Given your scenario, how important is this? You want to import into Development before PROD? from the same source? Why is Development more important than PROD? Why can you not load from the same source into two targets at the same time?
In case you can't tell, I'm very confused by what you described as what you are trying to accomplish.
August 17, 2007 at 9:19 am
I'm assuming there's some processing that goes on during the four hours that needs to happen before the data can be copied to production. If that's not true, I think you should use Steve's suggestion.
When you schedule a DTS package, you're creating a SQL Server Agent job. The only way to start a job on one server from a job on another server is to enable multiserver administration and setup master/target servers (see "multiserver administration" in BooksOnLine).
As a work around, you could have the first job write to a table on completion- a datetime of completion would work- and have the second job read the table in it's first step to see if it has been four hours since the first job completed.
You'll need an idea of how long the first job runs so you can schedule the second job to start approximately four hours later and check the table repeatedly until it finds that it's been exactly four hours.
Greg
Greg
August 17, 2007 at 9:28 am
Notwithstanding the above, never tried this but...
1. Create linked server to LIVE on DEV
2. Create procedure on LIVE to execute sp_start_job
3. Add step to job on DEV to execute above proc on LIVE using 4 part naming, eg servername.databasename.owner.procedurename
Make sure appropriate account is used for linked server and/or the LIVE agent proxy account is sufficient to run sp_start_job
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply