Welcome To TechBrothersIT: SSIS - What Is Delay Validation Property in SSIS Package

DelayValidation Property:

DelayValidation 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 and objects( Tables,Views, Stored Procedures etc.) used by them. If any object such as table or destination file etc. is not available then Package validation fails and Package stop execution.

By setting this property to True, We enforce our SSIS Package not to validate that Task, Connection Manager or entire Package at start but validate at run time. Let me explain with some real time examples

Real Time Examples for Using DelayValidation Property in SSIS Package:

Example 1: Make Use of Temp Table in SSIS Package

Let's say instead of creating permanent staging tables we decided to use temp tables in our ssis pacakge. We want to load the data in temp table from flat file source and then want to use this temp table in other tasks. Before we use temp table in Data Flow Task , we have to create it. As the temp table will be created by using Execute SQL Task before the Data Flow task and if we let the Delayvalidation=false, package will try to validate temp table in Data Flow Task. As Temp table will not be available at this point, Package will fail. To skip this part, we can set the DelayValidation property to True so Package will skip Validation at start point. By the time package will reach to Data Flow Task, The temp table will be created by Execute SQL Task in above step and it will validate and load successfully.

Here is blog post that you can use to practice above example.

Example 2: Create Excel File with DateTime

To create Excel file with Datetime, you have to create empty excel destination file and keep it as template. The steps involved are

1-- You copy the template file to required folder , while you copy the file you can rename with datetime.

2--You load the data into newly created Excel file with datetime by using Data Flow Task.

As the file will not be available at the time of Packate Start, Package will fail to validate the Connection manager and Data Flow Task. You can set the property DelayValidation=true for both by going to properties. By doing that you are skipping the pre-validation. By the Time, Package will reach to Data Flow Task to load the data, you would have created the file with datetime by using File System Task.

Example 3: How to Create Multiple Files Dynamically From a SQL Server Table

I have a post in which I read the data from table and then create file name dynamically by using data from table. As the file name will be created later and no file will be available at time or Package Start time, I have used the DelayValidation Property. Click Here to see the blog post.

To set the DelayValidation Property, You can right click on Task/Connection Manager and go to properties and set it to true. You can also click on any Task/ Connection Manager/ Container and hit F4 Key to go to properties.