I want to execute a sql server job based on a table value. ie i have a table called dbo.T001_ControlTable and it has a column called as "Output"(which is filled by an SSIS package, when there is a success in load job), which store value "Success" & "Failure" values.Now based on that value i want to execute a job. i.e if value is "Success" then run a sql server job else run a different job. Could you please provide me a stored procedure for such activity.
Thanks for the reply David. The way it is currently set up is there is a job which has 6 steps in it, the first 3 steps should be dependent on flag value of a table(if status = ''y) to run the job. If any of the 3 steps is failed in the then update the control table with Y and run it again else proceed to next series of steps. The skeleton is(with my process name) : **SF to CDM** -- This is dependent on flag value, say if step 2/step 3 is failed then set the flag value to Y and run it again. Step 1 Step 2 Step 3 **CDM to CIS** Step 1 Step 2
I'm not really clear what you are trying to do or which part you are finding difficult. You know much more about what you are trying to achieve. Why don't you try it and come back with a specific question if a part of this is still giving you trouble.
SQL Agent doesn't give you much flow control like dynamically deciding where to go after each step, re-running the step etc. It's not the right tool for this kind of thing. You might want to have a look at SSIS?