Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 2253391 - Last Review: July 7, 2010 - Revision: 1.0
The execution of a SQL Server Integration Services (SSIS) package stops responding when you enable DTC transactions for a package in Microsoft SQL Server
Consider the following scenario:
- You create a SQL Server Integration Services (SSIS) package in Microsoft SQL Server 2005, in SQL Server 2008, or in SQL Server 2008 R2.
- The SSIS package contains a data flow task and some other tasks.
- The TransactionOption property of the SSIS package is set to Required to use DTC transactions.
- The other tasks run in a DTC transaction before the execution of the data flow task.
- You add a component to the data flow task.
- The ValidateExternalMetadata property of a data flow component is set to True.
- The data flow task contains an OLE DB Destination component that has the data access mode set to Table or view, or to Table name or view name variable.
When you run the package in this scenario, the execution stops responding. Additionally, if you debug the SSIS package in Visual Studio, you receive messages that resemble the following in theÂ
Progress view:
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4001100A at Package: Starting distributed transaction for this container.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
This problem occurs because the connection in the data flow is not enrolled in the DTC transaction. This causes the execution of the sp_cursoropen stored procedure to be blocked. This is a design feature because a connection cannot be enrolled in a DTC transaction during the validation process. In the scenario that is described in the "Symptoms" section, the validation process of a data flow component is blocked when you run the package because the connection in the data flow task has not been enrolled in the DTC transaction.
To resolve this problem, use one of the following methods:
- Set ValidateExternalMetadata for all component in the data flow task to False
- Set the data access mode of the OLE DB Destination component to one of the following modes:
- Table or view – fast load
- Table name or view name variable – fast load
- SQL command
TheÂ
Table or View data access mode is blocked but other data access modes are not blocked because the different commands issued by the data provider for each data access mode. To obtain more details about this issue, use SQL Server Profiler to see the different commands issued by the data provider.
For more information about how to use SQL Server Profiler, visit the following Microsoft Developer Network (MSDN) website:
Using SQL Server Profiler
(http://msdn.microsoft.com/en-us/library/ms187929.aspx)
For more information about how to troubleshoot SSIS packages, visit the following Microsoft Developer Network (MSDN) website:
Troubleshooting Package Development
(http://msdn.microsoft.com/en-us/library/ms137625(SQL.90).aspx)
APPLIES TO
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Enterprise X64 Edition
- Microsoft SQL Server 2005 Workgroup Edition
- Microsoft SQL Server 2008 R2 Standard
- Microsoft SQL Server 2008 R2 Developer
- Microsoft SQL Server 2008 R2 Enterprise
- Microsoft SQL Server 2008 R2 Datacenter
- Microsoft SQL Server 2008 R2 Workgroup
- Microsoft SQL Server 2008 Standard
- Microsoft SQL Server 2008 Developer
- Microsoft SQL Server 2008 Enterprise
- Microsoft SQL Server 2008 Workgroup
| kbinfo kbexpertiseadvanced kbsurveynew KB2253391 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate