 | |
DTS Package and SQL Agent Job
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
DTS Package and SQL Agent Job -
11-15-2006
, 03:40 PM
#2
| |||
| |||
|
|
Running SS2K here...I have a DTS package that is scheduled to run as a Job under the SQL Server Agent. My job needs to pull data from a non-SQL server database every day and once (only) each day. I have a pre-set time when I can start to "poll" the source database to see if the data is ready to be pulled into SQL Server via my DTS package. I need this data more or less as soon as it becomes available so I plan on scheduling the job to run daily, starting at a pre-set time and recurring every few minutes. In most situations the data will be available the first or second time the agent fires the job. Is there a way I can systematically flag the job not to run again that day once the Job has run to successful completion (i.e. downloaded the necessary data)? I would prefer not to have this job continue needlessly polling potentially dozens of times after the fact. Thanks in advance! Chris Worthington. |
#3
| |||
| |||
|
|
Hello Chris, Here's an idea Your job wakes up every 10 minutes or so, checks for the data and then imports it if it is available. As the last step in the package you could go ahead and disable the job if you have imported the data. You have another job which runs at the start of the watching process and enables again the watching job for you Regards Allan Mitchell Konesans Ltd T +44 7966 476 572 F +44 2071 008 479 http://www.konesans.com Running SS2K here...I have a DTS package that is scheduled to run as a Job under the SQL Server Agent. My job needs to pull data from a non-SQL server database every day and once (only) each day. I have a pre-set time when I can start to "poll" the source database to see if the data is ready to be pulled into SQL Server via my DTS package. I need this data more or less as soon as it becomes available so I plan on scheduling the job to run daily, starting at a pre-set time and recurring every few minutes. In most situations the data will be available the first or second time the agent fires the job. Is there a way I can systematically flag the job not to run again that day once the Job has run to successful completion (i.e. downloaded the necessary data)? I would prefer not to have this job continue needlessly polling potentially dozens of times after the fact. Thanks in advance! Chris Worthington. |
#4
| |||
| |||
|
|
Allan, Sounds good...so, Job A is scheduled to run once daily at the pre-set time and doesn't do much more than enable Job B. Job B is scheduled to run daily, recurring every "n" minutes. The last step in Job B (assuming successful data import) disables Job B. Hopefully, the last question on this topic...how would I systematically disable the job? Is there a system stored procedure I can invoke to accomplish this task or some other mechanism you would recommend? Thanks again. Chris Worthington. "Allan Mitchell" wrote: Hello Chris, Here's an idea Your job wakes up every 10 minutes or so, checks for the data and then imports it if it is available. As the last step in the package you could go ahead and disable the job if you have imported the data. You have another job which runs at the start of the watching process and enables again the watching job for you Regards Allan Mitchell Konesans Ltd T +44 7966 476 572 F +44 2071 008 479 http://www.konesans.com Running SS2K here...I have a DTS package that is scheduled to run as a Job under the SQL Server Agent. My job needs to pull data from a non-SQL server database every day and once (only) each day. I have a pre-set time when I can start to "poll" the source database to see if the data is ready to be pulled into SQL Server via my DTS package. I need this data more or less as soon as it becomes available so I plan on scheduling the job to run daily, starting at a pre-set time and recurring every few minutes. In most situations the data will be available the first or second time the agent fires the job. Is there a way I can systematically flag the job not to run again that day once the Job has run to successful completion (i.e. downloaded the necessary data)? I would prefer not to have this job continue needlessly polling potentially dozens of times after the fact. Thanks in advance! Chris Worthington. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.