April 13, 2009 at 12:22 pm
Apologies if this is the wrong forum for this posting but I did not see a forum section listed for JOBS and I bet that the answer to this question would be closer to being associated with SSIS then T-SQL in general.
Is there anyway to dynamically specify at execution time, what actions 1 or more steps in a SQL Server Job take?
For example if I have a job with just 1 step and that step calls the ALTER INDEX REBUILD ... command to rebuild 1 index on a table, is there anyway to have the Job run with different values for 1 or more of the WITH OPTIONS? If I have the job set so that the ONLINE option is set to OFF, is there any way short of setting up a new job to have the Job run with ONLINE set to ON? In other words any way to mimic with a T-SQL job the same purpose that arguments in a Stored Procedure serve?
I know the Job can be set to look up values in a table and use that as an option but is there another way to do this? I'm betting that if its not possible with a Regular (NON-SSIS) based job to do this that SSIS packages will have a way. I'm very much a beginner with SSIS so please excuse any possible dumb questions.
Thanks everyone.
Kindest Regards,
Just say No to Facebook!April 13, 2009 at 12:29 pm
U may have to mess with system tables here if u want for ex: on success to run something. I havent done anything particularly like this, but it may also turn out to be a limitation and u may have to write your own scheduler and drive things from a config table.
April 13, 2009 at 12:33 pm
Thanks for the feedback Vishal.
Kindest Regards,
Just say No to Facebook!April 13, 2009 at 1:04 pm
AFAIK, you cannot do this with the active job.
What I mean is that you can modify a job step using msdb.dbo.sp_update_jobstep
but those modifications will only get active with the next run.
Keep in mind only members of sysadmin can update a job step owned by another user.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 14, 2009 at 2:36 am
Absolutely, you can use variables to supply values at runtime to your SSIS task. You just have to create the control flow using a combination of control flow tasks and maintenance plan tasks to implement the solution and then create a job to execute the package based on a schedule.
Cheers!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply