SSIS Package Validation in the Catalog

Built into the SQL Server Integration Services catalog is the ability to run a validation without actually executing the package. Running a package validation in the SSIS catalog performs a high-level check against the underlying metadata to check for common points of failure (especially those related to data flows). SSIS package validation is not designed to capture every metadata issue, but it can help identify some issues to avoid runtime surprises.

In this brief post, I’ll demonstrate how to validate a package stored in the SSIS catalog, and how the results of that validation can be used.

SSIS Package Validation

SSIS package validation has been around for a while; in fact, even in versions prior to the SSIS catalog, the dtexec.exe utility could be used to perform validation. In the SSIS catalog, it’s made even better in that it can be automated as part of a T-SQL script, and the result of the validation can be queried from the SSIS catalog logging tables.

Validation in the GUI

To validate a package stored in the SSIS catalog, right-click on the package and choose Validate… as shown below.

This action brings up the Validate Package dialog box, which you may notice looks almost identical to the Execute Package dialog.

Just like the Execute Package dialog, kicking off the validation prompts the user to visit the logging report from that operation.

Again, the output report looks very similar to that of a package execution, although there is typically less output in this report since no actual execution took place. The report shows the status of the validation along with any output messages.

Validation Via T-SQL

Like many other operations in the SSIS catalog, the validation can be invoked using T-SQL as well as through the GUI. Personally, I prefer the T-SQL approach anyway; although you have to be careful about syntax, using script is much easier to automate and test than the GUI operations (and clicking the Script button in the UI above will create the script for you anyway). To kick off the validation of a package using T-SQL, you’ll use the [catalog].[validate_package] stored procedure as shown below.

You may notice that the syntax is similar to that used when executing a package via T-SQL. However, in a validation, no parameters are passed, and unfortunately there is no way to force [catalog].[validate_package] to run synchronously.

The results of the validation can be retrieved using the view [catalog].[validations]. Status is reported in the status_id column using the same values used by package executions: 7 represents success, 4 indicates failure, etc. It is worth noting that there is also a stored procedure to perform a similar validation on an entire project ([catalog].[validate_project]), but in my opinion, validating an entire project has far less benefit than checking an individual package since packages – not projects – are invoked for ETL operations.

What is Validated?

When the SSIS package validation is performed, it will check some of the package metadata. I want to reiterate that it doesn’t check everything, nor does a successful validation guarantee a successful package execution. The scope of validation is concentrated mostly on data flow components, looking for unexpected changes in column metadata, missing tables, or invalid connections. A package validation will also check that a needed connection is present, though it will usually not indicate an error if that connection is offline. A package validation normally does not fail when a source file is missing or a directory path is invalid, either.

So for all of the things that a package validation doesn’t do, what value does it offer? Here’s an ideal use case: If you work in an environment where metadata is prone to change, and you’re weary of being awakened with VS_NEEDSNEWMETADATA errors in the middle of the night, running package validations can help to identify those issues before the package is actually executed. Running a validation against a package is also very useful in automated testing scenarios, in which you perform a validation and then check the status of that validation before going more deeply into the process.

Conclusion

Running an SSIS package validation on those assets stored in the SSIS catalog can help to identify some metadata errors before the package is actually executed. Although a validation is not a silver bullet, it can bring to the surface some common failures outside of the execution process.