KB2142496 - FIX: A SQL Server 2008 R2 or SQL Server 2008 Integration Services package does not run in SQL Server Business Intelligence Development Studio when you use a shared SQL configuration table

Symptoms

Consider the following scenario:

• You create a SQL Server Integration Services (SSIS) package in Microsoft SQL Server 2008 R2 or Microsoft SQL Server 2008.

• You use an Execute Package task to run a child package within the SSIS package.
  
  Note This SSIS package will be cited as a parent package in the rest of this article.
  
  

• You use the SQL Server configuration type to create a configuration table for the parent package.

• You share the same configuration table with the child package.

• The configuration table contains lots of configuration entries for existing objects in the parent package. However, the object paths in certain configuration table entries do not exist in the child package. For example, when you use a shared configuration table for the parent package, certain variables, connection managers, tasks, or other objects that are referenced in the shared configuration table do not exist in all child packages.

• You run the parent package in SQL Server Business Intelligence Development Studio (BIDS).

In this scenario, the parent package does not run. Additionally, you receive one of the following error messages:

• The connection "<Connection name>" is not found. This error is thrown by Connections collection when the specific connection element is not found.
  

• The package path referenced an object that cannot be found: "<Path>". This occurs when an attempt is made to resolve a package path to an object that cannot be found.

Notes

• In addition to the error messages that are mentioned in the "Symptoms" section, other error messages may also occur.

• Even if you set the SupressConfigurationWarnings setting to True, this issue still occurs.

• This issue does not occur in Microsoft SQL Server 2005 Integration Services.
  

Cause

This issue occurs because of an error in SQL Server 2008 R2 Integration Services.

Specifically, when the shared configuration is applied to child packages through the Execute Package task, all configured objects have to exist in the package. Otherwise, the Execute Package task will throw an error. This error is typically treated as a warning. But in this case, this warning causes the Execute Package task to report failure. Therefore, the execution of the parent package fails.

Resolution

Workaround

To work around this issue, use one of the following methods:

Method 1

Do not use the same configuration filter in a shared configuration table for multiple packages. Specify a different configuration filter for each package. When you do this, the package objects are referenced for only one package in the configuration table.

Method 2

Add a dummy object to the problematic package. When you do this, the package path for the object exists in the package.

For example, assume that you have an SSIS package that is named Pkg1. Pkg1 contains a connection manager that is named named CM1. You also have a SSIS package that is named Pkg2. Pkg2 contains a connection manager that is named CM2. In this scenario, these packages share a same configuration filter in a shared configuration table. To avoid this issue, you add a dummy connection manager that is named CM2 to Pkg1. You also add a dummy connection manager that is named CM1 to Pkg2.

More Information

After you apply this hotfix, when you run the package from the DtsDebugHost.exe debugger itself in Business Intelligence Development Studio (BIDS) at design time, BIDS still returns the same warning message that is described in the "Symptom" section. However, the package will succeed.

When you run the package outside the debugger by using DTExec.exe or other runtimes, the package will succeed without any warning message. Additionally, you receive the following message that means the configurations were successfully applied to the child packages:

The package is attempting to configure from SQL Server using the configuration string
""ServerName.DbName";"[dbo].[SSIS Configurations]";"Connections";".

For more information about understanding Integration Services Package configurations, visit the following Microsoft website:

Understanding Integration Services Package Configurations

If you experience a similar problem in SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:

974559 FIX: Error message when you run a SQL Server 2008 Integration Services package that uses a configuration table in SQL Server Business Intelligence Development Studio: "The connection "<Connection Name>" is not found"

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

For more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems


For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:

822499New naming schema for Microsoft SQL Server software update packages
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates