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.
[IMAGE]
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 ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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.
[IMAGE]
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.
[IMAGE]
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 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:
[IMAGE]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 (including SQL Server) as well as business intelligence design and implementation. He is also the author of the novel Dancing the River Lightly. You can find more information at www.rhsheldon.com.
