Working with Validation in SSIS

Like This Blog 1

Added by Peter Avila June 13, 2012

SSIS validates packages to ensure that they will execute properly. SQL Server Validation occurs both as a package is opened in the BIDS design environment (Design-Time Validation) and just before the package executes (Run-Time Validation). Validation is very useful because it can help find errors in a package, usually with its connections, before it gets into trouble during run-time, but there are situations in which validation is not desirable. Here are three of the most common along with a recommended solution to address each:

Design-Time Validation

The following two examples illustrate problems validating a package when it is opened in BIDS.

1. The package contains many connections, and each time you open the package in BIDS, you must wait several minutes for the validation to complete. You know that the connections are set up properly and you would like to just have the package load rather than wait several minutes.

2. The package contains one or more connections to data sources and/or destinations that are temporarily unavailable during design time, resulting in errors and/or, again, lengthy load times when the package is opened.

SOLUTION to 1 and 2: Work offline. By telling BIDS that you are working offline before you open a package, BIDS prevents SSIS from validating the package as it is opened.

To work offline, you must first open a solution. Once the solution has opened, the SSIS menu in BIDS becomes available. Select Work Offline from that menu before you open the package.

Keep in mind that while you are offline you cannot execute the package. So, after the package opens, you can select the same option from the SSIS menu to go back to working online. This allows you to skip the validation and go back to working as you normally would have. Also, Work Offline is only available in design-time and has no effect on the package when it is executed outside of BIDS, such as in a SQL Server job, when executed using DTEXEC, and so on.

Run-Time Validation

Run-time validation occurs when the package executes, whether it executes in BIDS or outside of BIDS after deployed to a production environment. The following example illustrates problems validating a package when it is asked to execute. Note that validation occurs before the package executes.

3. Let’s suppose you have an Execute SQL task that creates a table. Following that task is a Data Flow task that contains a data source component that opens that table. The first time you execute the package, the table won’t yet exist; so, in the validation phase, SSIS will raise an error on the component that tries to access the as-of-yet non-existent table. Remember: The validation phase comes before the package executes.

In examples 1 and 2, above, the solution was to Work Offline, which can also help in this situation, but only in BIDS and not in a production environment! The problem with Work Offline is that it is an option in BIDS only and is not available (or desirable!) when the package executes in its production environment after it has been deployed.

SOLUTION: Delay the validation of the task or data flow component causing the validation error. Delaying validation tells SSIS to validate the task or data flow component when the package executes, rather than validate it in the validation phase that occurs just before the package executes. In this example, because the package is able to execute, the table is created, and by the time execution reaches the component that opens the table (the component that was causing a validation error), there will no longer be any reason for SSIS to raise an error since the table will be in place.

Validation can be delayed at the package level, the control flow level, or the component level (inside a data flow). To delay validation of an entire package, change the package’s DelayValidation property to True. Doing this will delay validation of all tasks and components in the package.

To delay validation of a control flow task such as an Execute SQL task, a Data Flow task, or other, change the task’s DelayValidation property to True. If the task is a Data Flow task, then all of its components will have their validations delayed, too.

Finally, to delay validation of a single component in a Data Flow, change its ValidateExternalMetaData property to False.

Enjoy!
Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ