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.
