SSIS DelayValidation Myth

     Over the past 4 days I have addressed what appears to be a common misconception regarding the DelayValidation property in SSIS.  The core of the misunderstanding is that by setting the DelayValidation property to true will improve performance since no pre-validation will be done on the container in which the property is set, but only runtime validation. 

     This is not the case at all.  The DelayValidation property does exactly what would be expected.  It stops pre-validation, but does not stop runtime validation.  When the default value of false is used in the DelayValidation property then the package, container, and or task will not be be validated upon execution of the package but rather upon runtime of the executable.  Regardless of the property setting validation will only occur once on each executable.

     To better demonstrate this I have two packages both of which have 35 data flow tasks all connected with OnSuccess precedent constraints.  The first package I have left each task with the default value of false for each tasks DelayValidation property.  By setting a break point in the first tasks Pre-Execute event and executing the package you can clearly see in the Progress tab that EVERY task, including the package, is validated.

image      The second package is identical except the DelayValidation property is set to true.  Again by setting a break point in the first tasks Pre-Execute event handler and executing you observe that only the package was validated.

image     A follow up question usually asked about package validation is if validation always occurs then why is there even a DelayValidation property on a package if it is going to validate once it is executed?  This can be an important property when a package contains an execute package task.

     By stepping through the package it is visible that each task is validated when the property is set to True, but ONLY during Pre-Execution.  In order to properly address this I have spent a great deal of time experimenting with the DelayValidation property to see of execution time differs between all tasks being set to True and False and have results have been mixed.  I somewhat expected this result since there is NO WAY around validation and time and resources for validating any executable is going to be affected by activity at the time of validation.

     So why use the DelayValidation property?  That question is easily answered.  Consider a complex package where staging tables are created in execute SQL tasks before being used in downstream data flow tasks.  During validation resolution is done to insure that the referenced objects exist.  If the task DelayValidation is set to false then the package execution immediately fails.  Since the referenced tables don’t exist in the data flow tasks, yet, validation fails at the task,  By changing the DelayValidation property to False on the appropriate tasks package execution will succeed, the objects will be created first in the execute SQL tasks, and upon runtime validation the data flow tasks succeed.

     The SSIS packages referenced in this post can be downloaded from my site here.

Leave a Reply

Hit Counter provided by Sign Holders