Looping either 10 times or breaking out if successful

[collierd2]

Hello

Using SSIS (SQL 2005), I'm looking for SSIS to help with the:

I want to truncate a table
Then connect to a server and extract data from that server into a table
This I can do with a simple trucate and insert

Problem is, the connection seems to fail pretty frequently
So, I would like to generate a routine that tries this 10 times
If it succeeds, it then moves to the next part (in this instance another container)
If it fails, it e-mails this failure before moving to the next container

So the loop should break if successful OR carry on if not
Then return a success/failure so I can either e-mail or not

I've played around with the loops and I'm struggling to break and work with 2 variables

Any thoughts

Thanks

Damian.

 

[xlbo]

simply have a script on success of final task in your steps
increment an integer variable for your loop count and set a success/failure variable if executes - they key here is to have the default value of the variable in the package as failure and only set to true if the script is reached

You can then set up expressions in your loop (set up as a do while loop) testing on your incrementing variable <=10 and success/fail variable = failure). Once either of these is true, the loop will be exited and you can choose what to do by testing the value of each variable in your precedence constraints

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question