Go Validation of SSIS

Delay Validation Property in SSIS

Hello!

ETL can be very time consuming and also complicated process. In order then the business does is stands at cross Roads if any critical and time consuming ETL process counters a fail Ure, the package is validated during design time and also during execution time.

In this post let ' s has a brief understanding of what Delay Validation property in SSIS are and how the property can be MA Nipulated to favor the business case under special considerations.

Design Time Validation

To understand more on design time validation, let's create a package.

Here I have both Execute SQL Tasks on my design pane. As you'll see in the graphic above, one on the right have a small cross. That's because the package is validated at design time to list potential agent so might break the routine execution of The package.

The error discovered was and the task on the right have no Connection manager defined.

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

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

Once a valid data is provided, the cross mark disappears, thereby ensuring the task would not fail as far as the Conne Ction to server is concerned.

Please note during package execution, errors specific to the EXECUTE SQL TASK like Timeout error was not managed by delay valid ation property. The designer'll has to tweak the task accordingly.

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

Run Time Validation

To understand more on run time validation, let's create a package.

Here, we shall create a business case to archive files (your may consider the daily ETL loads) and move them into say a "PR Ocessed "folder after successful transfer-staging area.

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

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

• Foreach Loop Container (FEL)
• File System Task (FST) and variable of string type to store current file name

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

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

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 would be known" the "Executable" and "the connection at run time" .

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

Go Validation of SSIS