Tip

Open SSIS packages without validation using these SQL properties

When you open a SQL Server Integration Services (SSIS) package in SSIS Designer or add components to a package, SSIS by default checks the data sources used by the various components. This process for SSIS package validation ensures that the external metadata is valid. If the metadata is not valid, you'll receive warning or error messages pointing to the problem. At times, you might want to override the default behavior and open the SSIS package without validation. SSIS supports two properties -- DelayValidation and ValidateExternalMetadata – that let you control the validation process on individual components.

Creating a basic SSIS package

A common scenario in which you might want to control the validation process is when your SSIS package creates database objects and then accesses those objects from within the same package. Figure 1 shows an SSIS package that, among other things, creates a table, populates it and then accesses it.

    Requires Free Membership to View


Figure 1: An SSIS package creates and populates a table and then accesses it. (Click on image for enlarged view.)

To support this package, I first created a set of tables, all of which are the same except for the names.

SELECT c.FirstName, c.MiddleName,
c.LastName, e.HireDate
INTO HumanResources.EmpHireDates1
FROM Person.Contact c
INNER JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID

SELECT * INTO HumanResources.EmpHireDates2
FROM HumanResources.EmpHireDates1

SELECT * INTO HumanResources.EmpHireDates4
FROM HumanResources.EmpHireDates1

The package starts with an Execute SQL task that truncates the EmpHireDates2 and EmpHireDates4 tables. The first Data Flow task retrieves data from EmpHireDates1 and inserts it into EmpHireDates2. After the Data Flow task, an Execute SQL task creates the EmpHireDates3 table based on data pulled from EmpHireDates2. I used the following Transact-SQL statement within this task:

SELECT * INTO HumanResources.EmpHireDates3
FROM HumanResources.EmpHireDates2

The second Data Flow task retrieves data from EmpHireDates3 and inserts it into EmpHireDates4. The Execute SQL task that follows this Data Flow task then drops EmpHireDates3.

As you can see in Figure 1, the second Data Flow task shows a validation warning (the yellow triangle). This is because the EmpHireDates3 table does not yet exist, but the table is still referenced by a data flow component. Figure 2 shows the Data Flow tab for this Data Flow task. Notice that the OLE DB source, which references the EmpHireDates3 table, shows an error.


Figure 2: Data Flow tab for the Data Flow task. (Click on image for enlarged view.)

If you were to run this SSIS package, you would receive a validation error message (shown in Figure 3) that indicates there is an error in the data flow. Because SSIS by default validates each data source in a package before it runs the package, the validation fails because EmpHireDates3 does not exist, even though the package creates that table.


Figure 3: Receiving a validation error message when opening the SSIS package. (Click on image for enlarged view.)

The DelayValidation property

Not surprisingly, SSIS has a workaround. Here's how to open the SSIS package without validation using the DelayValidation property. Each executable within a package, as well as the package executable itself, supports the DelayValidation property. By default, this property is set to False. However, if you set the value to True, SSIS will run the package and delay validating that executable until it runs. If you refer back to Figure 1, you'll see the DelayValidation property in the Properties window. This is the DelayValidation property for the package executable. If you set it to True, the package will run as expected.

Note that setting the DelayValidation property at the package level does not prevent the initial validation process when you open the package. It simply delays any package-level validation when you run the package. That means that the package will still run, as in the example I showed you, but the warning will still appear when you open the package. This is important if you also want to prevent the validation when the package opens. In this case, you should set the DelayValidation property to True at the task level.

One reason it's useful to avoid validating your data sources when you open an SSIS package is that sometimes the process can take an enormous amount of time. It could be because of a remote data source, a data source that is slow to respond or a number of other reasons. If you're in the process of developing a package and you find yourself opening that package often, you can spend an inordinate amount of time waiting for SSIS to validate a source. In such cases, setting the DelayValidation property to True at the task level can be one of the most time-saving steps you take.

The ValidateExternalMetadata property

The DelayValidation property is only available to executables. In other words, you can set the property only on tasks, containers and the package itself. The property is not available to individual data flow components. However, data flow components

More SQL Server Integration Services (SSIS) features:

 such as the OLE DB Source component support the ValidateExternalMetadata property. If you refer back to Figure 2, you'll see the property in the Properties window as it appears for the OLE DB Source component.

By default, the ValidateExternalMetadata property is set to True, so SSIS validates the external metadata whenever you open the package, add a component or run the package. You can override this behavior by setting the property to False, and, as a result, SSIS will not validate the data source until it runs that component.

The ValidateExternalMetadata property has a more granular approach to managing the SSIS data source validation process. However, this property, when set to True, helps you avoid locking issues when your package is using transactions. For this reason, it might be better to use the DelayValidation property at the task level.

Whichever property you use is a valuable asset when developing any SSIS package, particularly when the validation processes are extremely slow. Validating packages in advance can also serve a valuable purpose and end up saving time, so be sure to use these properties judiciously.

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at  http://www.rhsheldon.com.

This was first published in May 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.