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.
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.
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!
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.
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!
No comments:
Post a Comment