Delay Validation Property in SSIS

Hello!

ETL can be very time consuming and also complicated process. In order that the business does not stands at cross roads when any critical and time consuming ETL process counters a failure, the package is validated during design time and also during execution time.

In this post let’s have a brief understanding of what Delay Validation Property in SSIS is and how this property can be manipulated to favor the business case under special considerations.

Design Time Validation

To understand more about design time validation, let’s create a package.

1_SQL_Server_Integration_Services_Delay_Validation_Property

Here I have two Execute SQL Tasks on my design pane. As you will see in the graphic above, 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.

In this case, the error discovered was that the task on the right has no connection manager defined.

This prompts the designer that a valid connection string should be defined for task execution. Such early signals are highly beneficial as they serve as an important indicator that something somewhere is incorrect and requires the attention of the designer.

The default value of delay validation is false. This means that all actions taking place on the designer pane will be validated and checked for their correctness. Here, we skipped the connection string and the development studio is smart enough to point that to designer.

Once a valid data is provided, the cross mark disappears, thereby ensuring that the task will not fail as far as the connection to server is concerned.

Please note during package execution, errors specific to EXECUTE SQL TASK like Timeout error is not managed by delay validation property. The designer will have to tweak the task accordingly.

On a lighter note, not specifying a connection string and setting the value for delay validation to true will also not make any sense until unless the designer decides to use expressions to specify connection details.

Run Time Validation

To understand more about run time validation, let’s create a package.

   

Here, we shall create a business case to archive files (you may consider the daily ETL loads) and move them into say a ”processed” folder after successful transfer to staging area.

Suppose that the daily ETL loads that the business receives from the transaction system are in form of a flat files and each file has a unique time stamp attached like (filename_date.txt). Now since the source is a varying entity, the name to source will only be resolved at run time i.e. when the package is executed. So this can be a brilliant case to study the usage of Delay Validation at run time.

We need a combination of following tasks for successful execution. Please note one may come up with an alternate design and also more efficient design. This is always welcome.

  • Foreach loop container(FEL)
  • File system task(FST) and variable of string type to store current file name

The foreach loop will enable the repeated execution of the identical behavior over multiple files and the file system task will enable the business to move the processed files to some alternate location.

After configuring the FEL, and the FST, you will notice a cross mark on the FST.

2_SQL_Server_Integration_Services_Delay_Validation_Property

Now, if you toggle the default value of Delay Validation and set it to true, the cross mark disappears. Which is precisely what we require, since the filename will be known the the executable to make the connection at run time.

Lastly, this is a wonderful property as long as it is not abused.

That’s all. Happy learning

 

Regards

Raunak Jhawar

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

6 Comments on “Delay Validation Property in SSIS”

  1. great work guys…was asked this in a tech interview. I gave the example of temp tables. But the one with flat files is just as amazing. will be adding you to FB #darkraijin #wm-elite

  2. great work guys…was asked this in a tech interview. I gave the example of temp tables. But the one with flat files is just as amazing.

  3. Glad you liked the content. Do write back on the forum what you would like to hear or read from us

  4. Perfectly explained. I think we can also use it for excel source/destinations when the files are created dynamically or are present on the location at run time only.

Leave a Reply

Your email address will not be published.