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

Today, I will talk to you about how to achieve delayed verification in SSIS. Many people may not know much about it. In order to let you know more, Xiaobian summarizes the following contents for you. I hope you can gain something according to this article.

1. Delayed validation attributes

Delayed validation properties apply to Package, Task, Container, and Connection Manager. By default, delayed validation is disabled, which means that when Package executes, the objects referenced by Package, Task, Container, and Connection Manager are validated first. If any objects do not exist, validation fails and Package stops running.

Before the Package starts to execute, verify that the Package can detect errors early, avoid wasting system resources to execute a Package that will definitely fail, and properly configure it, which will lead to performance optimization.

II. Verification method

In the order in which validation is performed, a Package contains two types of validation:

Package Validation: When executing a Package, first validate the Package and all the components it contains, which is Package level validation;

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

Validation is divided into two phases, 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 a Package, increasing the validation time of the Package and increasing the time it takes to open the Package for editing each time. Enabling deferred validation disables Package level validation so that only Component level validation is performed at Package design-time, noting that Component level validation cannot be disabled in any case.

In general, it is a good choice to enable deferred validation for a completed Package, which reduces the overall execution time of the Package because it skips Package-level validation.

1, disable delayed validation

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

Then after the Package starts running, the first thing is to start executing Package Validation. When the Package level verification operation is completed, start verifying each Task contained in the Package. If the verification finds an error, the Package will not be executed and an error will be reported directly.

2, Enable delayed validation

To enable delayed validation of a Package, you need to set the DelayValidation property value of the Package to True, as shown in the following figure:

When deferred validation is enabled, the Package will continue to execute until it runs to a specific component, and if the component validation fails, an error message will be thrown to stop the Package from running.

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

Third, the hierarchy of deferred validation attributes

Setting the DelayValidation property at the Package level does not prevent the initial validation process when the Package is opened, it simply delays Package level validation when the Package is run, which means that the Package will continue to run, but the alarm will still appear when the Package is opened. If you want to prevent validation when opening a Package, you must set the DelayValidation property at the Task level to True.

Avoiding validation link managers is, sometimes, a very time-saving development option. Sometimes the process of opening a Package can take a long time, possibly because it references a remote data source that is slow to respond or not linked. If we are developing a package and need to open it frequently, we may have to wait 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, Validation only verifies the integrity of attributes

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

Package processing flow is as shown in the figure below. Package level Validation starts before Validation of Create Staging Table and ends after Validation of Insert Data. You can see from the figure that validation of Insert Data is completed. In fact, validation operation does not detect a very simple syntax error until Task is actually executed. This shows that validation operation only verifies the integrity of Task attributes, and the effect is limited.

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

Set the Package Level property DelayValidation=False, Task Level property DelayValidation=False, in Data Flow Task, OLE DB Source component selelct data from a table dbo.delay_test that does not currently exist:

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

Set Package Level attribute DelayValidation=False, Task Data Flow Task attribute DelayValidation=True, re-execute, Package executed successfully.

Example 3: Open package validation

Set Package Level attribute DelayValidation=True, Task Level attribute DelayValidation=False. When Package is opened, Task Data Flow Task has a red X on it. When Task Level attribute DelayValidation=True is set, the red X on Task Data Flow Task disappears when Package is opened. This means that validation has started when Package is opened, and validation attributes at Task Level will overwrite validation at upper level.

The above three examples illustrate that the SSIS engine validates a Package when it is opened, designed, and run, and the Package validation operation is

After reading the above, do you have any further understanding of how to implement delayed verification in SSIS? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

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.