Shulou(Shulou.com)06/01 Report--

During the execution of Package, if Error appears in Data Flow, Data Flow component can output error lines by simply configuring it in the component's ErrorOutput, referring to "Data Flow's ErrorOutput". Compared with Data Flow,Control Flow's handling of OnError events, it is more complex and elaborate, and the following five aspects need to be considered:

1. In Control Flow, Package itself, Task, and Container have the attribute MaximumErrorCount

2 the Event handler of the Task OnError event can capture the OnError event of Task or Container, and handle the Error

3The handling of Error by Execution Result and progress message of Magazine package

4. The attributes FailPackageOnFailure and FailParentOnFailure control the Error to be passed up

5OnError event can be passed to the parent component, similar to bubbling

First, by default, when an error occurs in container, package execution fails

Second, attribute MaximumErrorCount

The property MaximumErrorCount Specifies the maximum number of errors before the executable fails, which specifies the number of Error that the Executable can hold. When the upper limit set by the property MaximumErrorCount is reached, the Executable fails to execute and throws an Error. The default value is 1, and the component will Fail whenever Error occurs.

Setting the property MaximumErrorCount of Execute SQL Task has no effect, which has no effect on Container or Package.

1. Set the property MaximumErrorCount=2 of Container, and an error occurred in its child Task, as shown in the following figure

The execution of the child Task (Execute SQL Task) fails, and its parent Container executes successfully, and because the precedence constraint of the Container and the downstream component is Success, the package continues to execute the downstream component.

In the Progress tab, SSIS reports Warning information

Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

The final execution result of SSIS Package is

The reason for the failure is that Error continues to be passed to the parent component until it is passed to Root Level (Package), while Package's MaximumErrorCount=1.

2. Modify the attribute of Package and MaximumErrorCount=2 the attribute of Package to view the execution result.

View the execution process in Progress Tab

[Execute SQL Task] Error: Executing the query "insert into dbo.test_env

Values (1) failed with the following error: "An explicit value for the identity column in table 'dbo.test_env' can only be specified when a column list is used and IDENTITY_INSERT is ON.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Third, attributes FailPackageOnFailure and FailParentOnFailure

FailPackageOnFailure: if set to True, then if a single task fails, the entire package fails. The default value is False

FailParentOnFailure: if set to True, then the upper component of the Task will fail as long as a single Task fails, and the Parent component of the Task is Container or Package. The default value is False.

On the Task where the error occurred, both properties are false, and from the point of view of execution, these two properties do not play any role.

Fourth, the Event handler of OnError events

In an OnError Event processor, if you set the Propagate property to False, you do not need to modify the MaximumErrorCount property of Parent container to ensure that the package continues to run after an error occurs. Please read "Propagate of Event" for details.

1. Create an OnError Event handler for Execute SQL Task under Container

2. Set the system variable Propagate of OnError's Event handler to False

3. View the execution result of package

The Error Msg found in Progress is:

[Execute SQL Task] Error: Executing the query "insert into dbo.test_env

Values (1) failed with the following error: "An explicit value for the identity column in table 'dbo.test_env' can only be specified when a column list is used and IDENTITY_INSERT is ON.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

3. Deploy Package to Integration Services Catalog and view the result of execution. Satus is the error message displayed in Succeeded,Error messages.

Fifth, conclusion

Any error that occurs in Control Flow will be caught by SSIS Engine; no matter how you set the properties of Task or Container, Error Message will be generated whenever an error occurs.

Error can be passed up, and when Error is captured by the Event handler of the OnError event, and Propagate is set to False, Error stops passing.

The Execution Result of Package has nothing to do with the presence of Error in Package. It is still possible for the component to throw the Error,Package successfully, except that the Error message is recorded in the progress.

Appendix:

Quote "Understanding MaximumErrorCount":

When the number of errors occurring inside a container during execution reaches its MaximumErrorCount, the container's ExecutionResult is changed to Failure if it is not already set to that state. A value of zero sets the error count threshold to infinity, disabling this functionality.

Errors and execution result are distinct concepts. A container's internal logic may set its result independent of whether errors have been raised. It's possible for a container to return success and yet have raised errors or to report failure without having fired any errors. The behavior controlled by MaximumErrorCount bridges between these two concepts, overriding the container's internal logic to coerce a failed result when the specified number of errors occurs.

MaximumErrorCount's triggering of a failure result does not terminate the container's execution. However, the state of failure may be used to influence control flow via precedence constraints. Also, in the case of For and Foreach Loop containers, a failed ExecutionResult disables further iteration.

Some documentation asserts that MaximumErrorCount defines the number of errors that can occur before a container stops running. Based on extensive testing using Microsoft SQL Server Integration Services Designer Version 12.0.2344.23 where I was unable to reproduce MaximumErrorCount halting a container's execution, I believe this documentation to be inaccurate.

Propagation

By default, errors bubble up from child to parent containers. Within a package, this propagation may be disabled for a particular container by having its OnError event handler or set the system variable Propagate to false. Note that this variable only affects propagation inside a package. Even when set to false, errors raised in a child package are still passed to the parent package.

Each container ina container hierarchy makes an independent determination of whether a propagated error causes its MaximumErrorCount threshold to be met. For example, an error bubbling up may cause a parent container to fail even though its child container reports success because the parent container's MaximumErrorCount is set to a lower threshold.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.