SSIS latency verification method and ssis latency Verification

SSIS latency verification method and ssis latency Verification

Verification is an event. When a Package is executed, the first event is triggered. verification can prevent the SSIS engine from executing an abnormal Package or Task. DelayValidation delays verification operations until the Package runs (run-time). When delayed verification is disabled, an Error is returned once the verification operation is performed) or Warning, the Package will not continue. When updating massive data, you usually need to use a temporary table to load the remote data to the local device. This requires creating a temporary table after the Package is executed. After the Package is executed, delete a temporary table. If latency verification is disabled. When delayed verification is disabled, the verification operation checks whether the temporary table exists before the Package is executed. Because the temporary table needs to be created after the Package is executed, the verification event sends an error message, the Package stops running. In this case, latency verification must be used to check whether a delayed temporary table exists when it is referenced to a temporary table.

1. Delayed verification attributes

The delay verification attribute acts on the Package, Task, Container, and Connection Manager. By default, delay verification is disabled, which means that when the Package is executed, verify that the objects referenced by the Package, Task, container, and link manager are valid. If any objects do not exist, the verification fails and the Package stops running.

Verify the Package before running the Package to detect errors as soon as possible, so as to avoid wasting system resources to execute a Package that will inevitably fail and be properly configured, which will lead to performance optimization.

Ii. Verification Method

According to the verification execution order, the Package contains two types of verification methods:

PAckage Validation: During Package execution, the Package and all its components are verified at the Package level;

Component Verification: Component-level verification, including Task, Connection Manager, and Container. verify the validity of the referenced objects in the component before the component starts execution.

Verification is divided into two phases (Phrase). Package-level verification is performed first, and component-level verification is performed later:

Package-level verification includes component-level verification. If latency verification is disabled, component-level verification is performed twice, which is not a good choice when designing a Package, this increases the Package verification time and the time for each open Package to edit. When latency verification is enabled, Package-level verification is disabled, so that only the component-level verification is performed during the design-time of the Package. Note that in any situation, component-level verification cannot be disabled.

Generally, enabling latency verification for a developed Package is a good option, which reduces the overall execution time of the Package because the Package-level verification is skipped.

1. Disable latency verification.

To disable Package latency verification, set the value of DelayValidation to False for the Package. By default, Packag disables latency verification, as shown in:

After the Package starts to run, the first thing is to run Package Validation. After the Package-level verification is completed, verify the tasks contained in the Package. If an error is found during verification, the Package is not executed and an error is reported directly.

2. Enable latency Verification

To enable Package latency verification, set the Package's DelayValidation attribute value to True, as shown in:

When delayed verification is enabled, the Package runs on the specified component. If the verification fails, an error message is thrown to stop the running of the Package.

The Component Verification actually executes the component. If a staging table is created for the upper-level component, the downstream component references the temporary table, and no exception occurs, because when the component is verified, the temporary table already exists.

3. Hierarchical Structure of delayed verification attributes

Setting the DelayValidation attribute at the Package level does not prevent the initial verification process when the Package is opened. It only delays Package-level verification when the Package is running, which means the Package will continue to run, however, the alarm still appears when the Package is enabled. To prevent authentication when a Package is opened, you must set the DelayValidation attribute at the Task level to True.

Avoid verifying the link manager. Sometimes, it is a very time-saving choice. Sometimes it takes a long time to open a Package, probably because it references a remote data source, and the response of the data source is slow or cannot be linked. If we are developing a package and need to open it frequently, it may take a long time to wait for SSIS to verify a data source. In this case, setting the DelayValidation attribute to True at the task level can save a lot of time.

Example 1: The verification operation only verifies the integrity of the attribute.

Set the Package Level Attribute DelayValidation = False, and the Task Level Attribute DelayValidation = False

Shows the Package processing process. The Package-level Validation starts before the Validation of the Create Staging Table and ends after the Validation of the Insert Data. In fact, the Validation of the Insert Data is complete, the verification operation does not check a very simple syntax error. It is not found until the Task is actually executed. This indicates that the verification operation only verifies the integrity of the Task attribute, the effect is limited.

Example 2: Verify that the referenced object exists

Set the Package Level Attribute DelayValidation = False and the Task Level Attribute DelayValidation = False. In Data Flow Task, the ole db Source component uses selelct Data from a non-existing table dbo. delay_test:

When executing a Package, the "Package Validation Error" form is displayed in SSIS. This is an Error found during Package-level verification:

Set the Package Level Attribute DelayValidation = False, and the Task Data Flow Task attribute DelayValidation = True. Re-execute the Task. The Package is successfully executed.

Example 3: Enable package Verification

Set the Package Level Attribute DelayValidation = True and the Task Level Attribute DelayValidation = False. When a Package is opened, the Task Data Flow Task contains a red X number, when the attribute DelayValidation of Task Level is set to True, when the package is opened, the red X symbol on the Task Data Flow Task disappears. This indicates that the verification operation has started when the Package is opened, the Task-level verification attribute overwrites the upper-level verification.

The preceding three examples show that when the Package is opened, the Package is designed, and the Package is run, the SSIS engine verifies the Package and the Package verification operation is hierarchical, the latency verification at the underlying layer overwrites the latency verification at the upper layer.

The above SSIS latency verification method is all the content that I shared with you. I hope you can give us a reference and support the help house.