Shulou(Shulou.com)05/31 Report--

This article is about how to implement deferred verification in SSIS. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Validation is an event that is triggered first when the Package executes, which prevents the SSIS engine from executing a Package or Task with exceptions. Deferred validation (DelayValidation) delays the verification operation until the Package is actually running (run-time). When deferred verification is disabled, once the verification operation returns an error (Error) or warning (Warning), Package will not continue. When updating a large amount of data, you usually need to use a staging table to load the remote data locally, which requires creating a staging table (staging table) after Package execution; after Package execution, delete the staging table when deferred validation is disabled. When deferred validation is disabled, the verification operation detects the existence of the staging table before the Package executes, and because the staging table needs to be created after the Package execution, the validation event issues an error message and the Package stops running, in which case deferred validation must be used to defer the existence of the staging table when it is actually referenced to the staging table.

First, delay the verification of attributes

Deferred validation properties act on Package,Task, container (Container) and link manager (Connection Manager). By default, deferred validation is disabled, which means that when Package executes, it first verifies whether the objects referenced by Package,Task, container and link manager are valid, and if any object does not exist, validation fails and Package stops running.

Before the execution of Package, verifying Package can find errors as soon as possible and avoid wasting system resources to execute a Package that is bound to fail. Reasonable configuration will lead to performance optimization.

Second, verification method

According to the order in which validation is performed, Package includes two types of authentication methods:

Package Validation: when Package executes, it first verifies Package and all its components, which is Package-level verification

Component validation: component-level validation, including Task, Connection Manager, and Container, verifies the validity of referenced objects in the component before it starts execution.

Verification is divided into two phases (Phrase), first at the Package level and then at the component level:

Package-level validation includes component-level validation, and if deferred validation is disabled, component-level validation is performed twice, which is not a good choice when designing Package, which increases Package validation time and each time Package is opened for editing. Enabling deferred validation disables Package-level validation, so that only component-level validation is performed at Package design time (design-time). Note that component-level validation cannot be disabled under any circumstances.

In general, enabling deferred validation for an Package that has completed development is a good choice, which reduces the overall execution time of the Package because Package-level validation is skipped.

1, disable delayed verification

To disable deferred verification of Package, you need to set the DelayValidation attribute value of Package to False. By default, Packag disables deferred verification, as shown in the following figure:

Then after the Package starts running, the first thing is to start executing the Package Validation. When the Package-level verification operation is completed, start to verify the various Task contained in the Package, and so on. If the verification finds an error, the Package will not be executed and will report the error directly.

2, enable delayed authentication

To enable delayed verification of Package, you need to set the DelayValidation attribute of Package to True, as shown in the following figure:

When deferred validation is enabled, Package executes until it is run to a specific component, and if component validation fails, an error message is thrown to stop Package from running.

Component validation occurs when the component actually executes the component. If the upstream component creates a new staging table and the downstream component references the staging table, no exception occurs because the staging table already exists when the component validates it.

Third, the hierarchical structure of delayed verification attributes

Setting the DelayValidation property at the Package level does not prevent the initial validation process when the Package is opened, it just delays the Package-level validation when the package is running, which means that the Package will continue to run, but the alarm still occurs when the Package is opened. If you want to prevent validation when Package is turned on, you must set the DelayValidation property at the Task level to True.

Avoiding validating the link manager is sometimes a very time-saving option for development. Sometimes the process of opening a Package takes a long time, which may be because it references a remote data source that is slow to respond or cannot be linked. If we are developing a package and need to open it frequently, it may take a long time for SSIS to validate a data source. In such cases, setting the DelayValidation property to True at the task level can save us a lot of time.

Example 1, the validation operation only verifies the integrity of the property

Set the property DelayValidation=False of the property DelayValidation=False,Task Level of Package Level

The processing flow of Package is shown in the following figure. The Package-level Validation starts before the Validation of Create Staging Table and ends after the Validation of Insert Data. You can see that the verification of Insert Data is completed from the figure. In fact, the verification operation did not check a very simple syntax error, and the error was not found until the real implementation of Task, which shows that the verification operation will only verify the integrity of the Task attribute, and the effect is limited.

Example 2, the validation operation verifies that the referenced object exists

Set the property DelayValidation=False of the property DelayValidation=False,Task Level of Package Level, where the OLE DB Source component selelct data from a table dbo. Delay _ test that does not currently exist:

When executing Package, SSIS pops up the "Package Validation Error" form, which is the error found by validation at the Package level:

Set the property of Package Level, the property of DelayValidation=False,Task Data Flow Task, DelayValidation=True, re-execute, and Package executes successfully.

Example 3, turn on the verification of package

Set Package Level's property DelayValidation=True,Task Level's property DelayValidation=False, when opening Package, Task Data Flow Task is marked with a red X sign, when setting Task Level's property DelayValidation=True, when opening package, the red X symbol on Task Data Flow Task disappears, which means that when opening Package, the verification operation has already started, and the Task-level verification attribute will override the upper layer verification.

The above three examples show that when opening Package, designing Package and running Package, the SSIS engine will verify Package, and the verification operation of Package is superior hierarchical structure, and the underlying delay verification will override the delay verification of the upper layer.

Thank you for reading! This is the end of this article on "how to achieve deferred verification in SSIS". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.