Why DelayValidation and ValidateExternalMetadata is important

i came across with a question of why setting "DelayValidation" and "ValidateExternalMetadata" are important.

For those who are new to SSIS. "DelayValidation" only can be found to all the component at Control Flow.




However "ValidateExternalMetadata" only can be found to all the component at Data Flow.


Now lets play with the setting with the monitoring tools SQL Profiler.

Scenario 1 - Execute package with default setting

Total number of prepare execution = 3.

Scenario 2 - Execute package with "DelayValidation" set to True.

Total number of prepare execution = 2.

Scenario 3 - Execute package with "DelayValidation" set to True and "ValidateExternalMetadata" to False

Total number of prepare execution = 1.

Apart from this, there are a huge drawback if the setting are leave default when the package are open from BIDS or SSDT. You may need to wait for decade for the package to be open as BIDS and SSDT will validate all the component in the package.

Take note to the square box bottom left of the picture.