Delay validation properties in SSIS

Delay Validation Property is available on Task level, Connection Manager, Container and on Package level. By default the value of this property is set to false that means that when the package start execution, It validates all the Tasks, Containers, Connection Managers. If Connection Managers is not available then Package validation fails and Package stop execution.

Before executing the package it validate the connection manager if connection manager is not valid the package will get failed.

See the below example

Here I am taking two Execute SQL Tasks on my design pane. As we will see, one on the right has a small cross. That’s because the package was validated at design time to list potential agent that might break the routine execution of the package.

 

For the First Execute SQL task I have configure the required configuration. At the design time SSIS package validate the connection and configuration if it is not correct then it show the error.

 

Second Execute sql task show error. Let’s execute the package and see the result.

 

Package get failed with error package get failed during the task validation. Now I am setting the Delay validation properties true for task 2.

 

Now the error has gone because package not pre validates   the task. Now I am executing the package

 

First task is completed it is failed second task because still I am not configuring the connection. See the error

See the Error

 

See the execution result

1.       Task Execute SQL task

Task start validating and after completing the validation its start executing the task. Finally the validation is completed.

2.        See the task Execute sql task 2

Validation starts at run time and checks the connection and executes the task. Here we get the error like No connection manager is specified.

Use of Delay validation

We set the delay validation properties is true when we are creating the dynamic connections. Some time we need to run a bit of code in a container, or task that creates an object. If the package tries to validate it may fail because the object does not exist. By setting this properties as true the take validate the connection at run time.

 If we are using temp table in any execute sql task at control flow level then we need to avoid the early validation of the package because we know that temp table will available at run time only. To skip the early validation or default (design time validation) we need to set DELAY VALIDATION property of package to TRUE. This will allow the package to validate the entire component at runtime only.