Thursday, March 8, 2012

Connection faileed Package abort

Hello,

I am connecting with various sqlservers in a package to get the information. If connection failed with any sqlserver, package failed after 3 tries.

I would like that if connection failed with any sqlserver, package should not end rather move ahead and connecto next sqlserver.

I already tried to increase the number of error from 3 to 1000 but still package failed as soon sqlserver connection failed.

Any help is appreciated.

Thanks.

--

Farhan

There's quite a numbers of options for "on error resume next" style execution in SSIS. Consider precedence constraints,

the System::Propagate OnError event handler variable, and the

MaximumErrorCount and ForceExecutionResult properties.

One way to continue execution despite failure is to use Completion or Failure precedence constraints rather than Success constraints. When using Completion style precedence, execution will continue, and the package will fail by default due its max error count having been exceeded.

Another way to continue despite task errors is by setting ForcedExecutionResult of the tasks. Here again, execution will continue post failure, and the package will exit with a failure status due to the default max failure count (unless you set ForceExecutionResult on the package itself). This is effectively completition style precedence, except different events are fired. The OnTaskFailed event is not fired here, since the task "did not fail".

Another option is non-bubbling errors via System::Propogate in an OnError event handler. This approach will keep the error(s) from bubbling up the container

hierarchy, excepting validation errors. The task may fail, but the

package will succeed, because the task's execution errors never bubbled

up.

To use non-bubbling errors, create an error event handler on the failing task(s), and set the System::Propagate variable to false. Use this option in combination with completion based constraints when you want a package result of success despite failed tasks and want the failed tasks logged as such. Also, using the non-propagating error tactic, the OnTaskFailed event is fired, so you can hook that for logging purposes if need be.|||

Many thanks, I will give it a try.

You are the only who have replied, glad someone out there who knows and wants to help.

No comments:

Post a Comment