DelayValidation Properties for SSIS

One, DelayValidation property

True if validation of the package is delayed until run time. False if the package was validated, and errors and warnings are returned before the package is actually executed. False May prevent the package from running if errors or warnings occur , exceed the Maximumerrorcount property.

DelayValidation property was available on Task level, Connection Manager, Container and on package level. By default the value of this property is set to false that means if the package start execution, It validates all t He Tasks, Containers, Connection Managers and objects (Tables,views, Stored procedures etc.) used by them. If any object such as table or destination file etc. was not available then the package validation fails and package stop Execu tion.

By setting the This property to True, We enforce the SSIS package does not have validate that Task, Connection Manager or entire Pack Age at start but validate at run time.

Validating the before it runs is a-a-finding some errors before execution starts. However, it processes the package to find errors, and if no errors is found, the package runs. Because this goes through the package, and validating a package increases the amount of processing in the package, So should is used only when necessary.

Second, the validation way of SSIS

SSIS uses the V Alidation Determines whether the package will fail at run time, it uses two types of authentication, the first is the package validation, validates the packages and all the components they contain before starting the package, and the second is component Validation,task, Connection Manager and container have component validation to validate all components in the package before starting to execute the component.

If the package is Delayvalidation=false, the package validation occurs before the package is executed and the package is not executed. If Delayvalidation=true, then the first step after the package executes is to carry out the package Validation and then component Validation.

Component validation is after the package, the component executes the previous validation, if the upper component creates a new staging table, the dirty component references the staging table, and no exception occurs. This is because the staging table already exists when the component is validation.

By default Run-time validation occurs in and phases:

1) package-level validation (or ' early validation ')-occurs when the Runtime calls the Excecute method of a package. Everything in the package gets validated.

If validation succeeds and the package commences it execute phase we come to

2) Component-level validation (or ' late validation ')-occurs when the Runtime calls the Execute method for each and every Task within the package.

This means is the default, all tasks within a package is validated twice!

As well as affecting the Design-time validation of the package, ' Delay validation = True ' Disables the Early valida tion (package-level) phase so, only component-level validation occurs at Run-time. NOTE that it's not possible to disable Component-level validation.

In general, delaying validation of any your components once you ' ve finished development and was ready for deploy is a good Option. It reduces overall package execution time (as early validation are skipped) and makes opening the package for editing less Painful (as you don ' t need-to-wait for all of the components to validate). Obviously, your may has scenarios where you want early validation to occur which are why I say in general so don ' t blindly Delay validation without first considering the effect it would have a your package

Third, when the package is opened, SSIS will validation

Setting the DelayValidation property at the package level does not prevent the initial authentication process when the package is opened. It just delays the package-level validation when we run the package. This means that the package will continue to run, but the alert still appears when we open the package. If we also want to prevent authentication when the package is opened, you must set the DelayValidation property on the task level to true.

One reason to avoid validating data sources is that sometimes we take a long time to open the SSIS package. This may be because this is a remote data source, the response from the data source is slow, or other reason. If we are developing a package and we need to open the package frequently, it may take a long time for SSIS to validate a data source. In such cases, setting the DelayValidation property at the task level to true will save us a lot of time.

Example 1, set the properties of the package level Delayvalidation=false,task level property Delayvalidation=false

Create a Package,control Flow design

The SQL statements for Task Create Staging table and task Insert Data are respectively

--Task Create Staging Tableif object_id('dbo.delay_test') is  not NULL    Drop Tabledbo.delay_testCreate Tabledbo.delay_test (IDint)--Task Insert data,the table Dbo.delay_test1 does not existInsert  intoDbo.delay_test1VALUES(1)

Strangely, package-level validation starts before task Create Staging table validation, ends with the validation of task Insert data, and can see the Execute SQL The validate of the task validation the SQL statement, but does not actually check for a very simple error. The error was not found until it was executed.

I guess the reason is that the execute SQL task does not early Validation and starts Validation each time until the task executes.

Modify the SQL statement for task Insert Data to two task execution success.

-- Task Insert Data Insert  into dbo.delay_test VALUES (1)

Example 2, set the property of the package level to Delayvalidation=false,task level property Delayvalidation=false, An exception occurs when you SELELCT data from a table dbo.delay_test that does not currently exist in the OLE DB source component in the Data Flow task.

When you execute packge, the form of the package Validation error appears, which is the Validation on the package level

To set the properties of the package level Delayvalidation=true,task the properties of the Data Flow Task delayvalidation=false, re-execute the package success

Example 3, open the validation of the package

Set the properties of the package level delayvalidation=true,task the property of level Delayvalidation=false, when you open the package, the task Data Flow task has a red X on it, When you set the property delayvalidation=true for a task level, the task Data Flow task does not have a red X on the package when you open it.

DelayValidation Properties for SSIS