Working with Validation in SSIS
Working with Validation in SSIS
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
You May Also Like
BIDS, DelayValidation, Design-Time Validation, ExternalMetaData, Run-Time Validation, SQL, SQL 2008, SQL Server, SQL Server Validation, SSIS
A Simple Introduction to Cisco CML2
0 3698 0Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2
Cable Testers and How to Use them in Network Environments
0 667 1This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments
OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj
0 247 1In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.
See what people are saying...