How do I retry a data flow task when connection fails

[dmusson17]

Hello,

I have a package that contains a loop where I have to connect to a data source via a DataReader Source component, running a different query built on the fly each time. My problem is that eventually the data flow in the loop will fail because it cannot connect to the data source. Shortly after that I am able to connect again.

The problem is on the data provider's side and I'm not sure when or if it will ever be resolved.

Thus I would like to build some error handling into the package to retry the Data Flow task when it fails but am unsure where/how to do this.

I see where I can set up an event handler for my data flow task but am unable to figure out how to get it to retry the task based on the certain error message that I get when the flow cannot connect. I want it to fail for errors other than the "cannot connect" .

Any direction or help in this matter is much appreciated.

Dave

 

[MDXer]

Untested but first thought is I would

Create a script task Prior to my dataflow in question. I would have this task try to open a connection and only after it has opened a connection allow the task to succeed. I would probably have a max attempts where it would fail after so many attempts. Place an On Success Constraint from the Script to the Dataflow

You would want the Max attempts variable so it wouldn't sit there and ping the server non-stop for who knows how long.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[dmusson17]

Thanks Paul.

I tried the script, only problem is I ran into an instance where the script succeeded but by the time I passed onto my data flow and try to connect again the server is too busy to respond again.

Is there any way I can configure my data flow task to retry upon failure?

Dave