July 21, 2009 at 3:56 am
I have a solution where I have some backups and data transfer taking place on Server A. When done it sets a value in a table on Server B. A job on Server B will see this value, run some stuff over there, and then reset the value. I then want the job on Server A to continue where it left off.
Now I was hoping to try and use Checkpoints to do this, as I cannot think there is a While loop function in SSIS. The job would update the value in the execute SQL task and then I would force a failure. When Server B had finished it would kick the job back off.
Can anyone think of a better way of doing this?
If this IS a good way - I can't get the ForcedExecution to fail anyway for some reason. I'm on version 9.00.3042.00. It just completes the task as completed even when I set it to "Failure".
My other question relates to an OnError event I have. If anything within the package fails I just send an OnError event that sends a notification email. however when somebody cancels the job this fails 30 steps and sends 30 emails! Best way of dealing with this?
Thanks in advance :hehe:
July 21, 2009 at 4:10 am
Why does the Server A task need to wait for the Server B task - is there some dependence there that you have not mentioned? What happens if the Server B task fails - are you doing a rollback on Server A?
You could implement a While loop in a script task - but the idea of repeatedly checking a value in a table using such a loop makes me cringe a little at the thought of all the I/O.
July 21, 2009 at 4:25 am
Server A is making a backup of Database A. Server B then transfers this backup over, and restores it on there.
At the moment I have a SQL Execute task looping waiting for the status record to change.
I'm doing it this way as I need this automated, and I figured it'll be slow running the restore from Server A. Maybe not though.
July 21, 2009 at 4:42 am
Running the restore from Server A would work fine, I believe. Server B would still be the one doing all the work: Server B is just issuing the control commands.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply