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

Article ID: 2142496 - View products that this article applies to.
Microsoft distributes Microsoft SQL Server 2008 R2 and Microsoft SQL Server 2008 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 and Microsoft SQL Server 2008 fix release.
Expand all | Collapse all

On This Page

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.


Back to the top | Give Feedback

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.

Back to the top | Give Feedback

RESOLUTION

Cumulative update information

SQL Server 2008 Service Pack 2

The fix for this issue was first released in Cumulative Update 3 for SQL Server 2008 Service Pack 2. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2498535
(http://support.microsoft.com/kb/2498535/LN/ )
Cumulative update package 3 for SQL Server 2008 Service Pack 2
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2402659
(http://support.microsoft.com/kb/2402659/LN/ )
The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2 was released
Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 2 hotfix to an installation of SQL Server 2008 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

SQL Server 2008 Service Pack 1

The fix for this issue was first released in Cumulative Update 12 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2467236
(http://support.microsoft.com/kb/2467236/LN/ )
Cumulative update package 12 for SQL Server 2008 Service Pack 1
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
970365
(http://support.microsoft.com/kb/970365/LN/ )
The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released
Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.
The fix for this issue was first released in Cumulative Update 3. For more information about how to obtain this cumulative update package for SQL Server 2008 R2, click the following article number to view the article in the Microsoft Knowledge Base:
2261464
(http://support.microsoft.com/kb/2261464/ )
Cumulative update package 3 for SQL Server 2008 R2
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
981356
(http://support.microsoft.com/kb/981356/ )
The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released
Back to the top | Give Feedback

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.
Back to the top | Give Feedback

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
(http://msdn.microsoft.com/en-us/library/cc895212.aspx)


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
(http://support.microsoft.com/kb/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"

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Back to the top | Give Feedback

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
(http://support.microsoft.com/kb/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:
822499
(http://support.microsoft.com/kb/822499/ )
New 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
(http://support.microsoft.com/kb/824684/ )
Description of the standard terminology that is used to describe Microsoft software updates
Back to the top | Give Feedback

Properties

Article ID: 2142496 - Last Review: March 21, 2011 - Revision: 5.0
APPLIES TO
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Workgroup
Keywords: 
kbqfe kbfix kbexpertiseadvanced kbsurveynew KB2142496
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top