大网数据广告活动
数据库与应用安全

The execution of a SSIS package stops responding

· 52CE 我爱测

Describes a problem in which the execution of a Microsoft SSIS package stops responding if you enable DTC transactions for the package and enable the ValidateExternalMetadata property for a component in a data flow task.

This article helps you resolve the problem in which the execution of a Microsoft SQL Server Integration Services (SSIS) package stops responding if you enable DTC transactions for the package and enable the ValidateExternalMetadata property for a component in a data flow task.

Original product version:   SQL Server
Original KB number:   2253391

Symptoms

Consider the following scenario:

  • You create an SSIS package in SQL Server.
  • 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.

Cause

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.

Resolution

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

More information

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.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 09/02/2020
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。