排查 SSIS Integration Runtime 中的包执行问题Troubleshoot package execution in the SSIS integration runtime

适用于:是 Azure 数据工厂否 Azure Synapse Analytics(预览版)APPLIES TO: yesAzure Data Factory noAzure Synapse Analytics (Preview)

本文描述了在执行 SSIS Integration Runtime 中的 SQL Server Integration Services (SSIS) 包时可能遇到的最常见错误,This article includes the most common errors that you might find when you're executing SQL Server Integration Services (SSIS) packages in the SSIS integration runtime. 并描述了潜在的原因,以及解决这些错误的操作。It describes the potential causes and actions to solve the errors.

在何处可以找到用于故障排除的日志Where to find logs for troubleshooting

使用 Azure 数据工厂门户检查 SSIS 包执行活动的输出。Use the Azure Data Factory portal to check the output of the SSIS package execution activity. 输出包括执行结果、错误消息和操作 ID。The output includes the execution result, error messages, and operation ID. 有关详细信息,请参阅监视管道For details, see Monitor the pipeline.

使用 SSIS 目录 (SSISDB) 检查执行活动的详细日志。Use the SSIS catalog (SSISDB) to check the detail logs for the execution. 有关详细信息,请参阅监视正在运行的包和其他操作For details, see Monitor Running Packages and Other Operations.

常见错误、原因和解决方法Common errors, causes, and solutions

错误消息:“连接已超时”或“服务在处理请求时遇到错误。Error message: "Connection Timeout Expired" or "The service has encountered an error processing your request. 请重试。”Please try again."

下面是可能的原因和建议的操作:Here are potential causes and recommended actions:

  • 数据源或目标过载。The data source or destination is overloaded. 检查数据源或目标的负载,并查看它是否有足够的容量。Check the load on your data source or destination and see whether it has enough capacity. 例如,如果使用的是 Azure SQL 数据库,而该数据库可能会超时,请考虑纵向扩展。For example, if you used Azure SQL Database, consider scaling up if the database is likely to time out.
  • SSIS Integration Runtime 与数据源或目标之间的网络不稳定,当连接跨区域或者是在本地与 Azure 之间建立的时尤其如此。The network between the SSIS integration runtime and the data source or destination is unstable, especially when the connection is cross-region or between on-premises and Azure. 执行以下步骤,在 SSIS 包中应用重试模式:Apply the retry pattern in the SSIS package by following these steps:
    • 确保 SSIS 包在失败时可以重新运行,且不产生负面影响(例如数据丢失或数据重复)。Make sure your SSIS packages can rerun on failure without side effects (for example, data loss or data duplication).
    • 在“常规”选项卡上配置“执行 SSIS 包”活动的“重试”和“重试间隔”。 在“常规”选项卡上设置属性Configure Retry and Retry interval of Execute SSIS Package activity on the General tab. Set properties on the General tab
    • 对于 ADO.NET 和 OLE DB 源或目标组件,请在 SSIS 包或 SSIS 活动的连接管理器中设置 ConnectRetryCountConnectRetryIntervalFor an ADO.NET and OLE DB source or destination component, set ConnectRetryCount and ConnectRetryInterval in Connection Manager in the SSIS package or SSIS activity.

此问题通常意味着无法从 SSIS 集成运行时访问数据源或目标。This issue usually means the data source or destination is inaccessible from the SSIS integration runtime. 原因可能各不相同。The reasons can vary. 请尝试以下操作:Try these actions:

  • 确保正确传递数据源或目标的名称/IP。Make sure you're passing the data source or destination name/IP correctly.
  • 确保已正确设置防火墙。Make sure the firewall is set properly.
  • 确保在数据源或目标为本地源或目标的情况下正确配置虚拟网络:Make sure your virtual network is configured properly if your data source or destination is on-premises:
    • 可以通过在同一虚拟网络中预配 Azure VM,来验证问题是否与虚拟网络配置有关。You can verify whether the issue is from virtual network configuration by provisioning an Azure VM in the same virtual network. 然后检查是否可以从 Azure VM 访问数据源或目标Then check whether the data source or destination can be accessed from the Azure VM.
    • 若要更详细地了解如何将虚拟网络与 SSIS Integration Runtime 配合使用,可参阅将 Azure-SSIS Integration Runtime 加入虚拟网络You can find more details about using a virtual network with an SSIS integration runtime in Join an Azure-SSIS integration runtime to a virtual network.

错误消息:“ADO NET 源无法获取连接 '...'”,Error message: "ADO NET Source has failed to acquire the connection '...'" 并显示“无法创建托管的连接管理器。”with "Could not create a managed connection manager."

潜在的原因是在包中使用的 ADO.NET 提供程序未安装在 SSIS Integration Runtime 中。The potential cause is that the ADO.NET provider used in the package isn't installed in the SSIS integration runtime. 可以使用自定义安装程序来安装该提供程序。You can install the provider by using a custom setup. 自定义 Azure SSIS Integration Runtime 的安装中可以找到有关自定义安装的更多详细信息You can find more details about custom setup in Customize setup for the Azure-SSIS integration runtime.

错误消息:“找不到连接 '...'”Error message: "The connection '...' is not found"

旧版 SQL Server Management Studio (SSMS) 中的某个已知问题可能导致此错误。A known issue in older versions of SQL Server Management Studio (SSMS) can cause this error. 如果此包包含的自定义组件(例如 SSIS Azure Feature Pack 或合作伙伴组件)未安装在使用 SSMS 来执行部署的计算机上,则 SSMS 会删除该组件,导致此错误。If the package contains a custom component (for example, SSIS Azure Feature Pack or partner components) that isn't installed on the machine where SSMS is used to do the deployment, SSMS will remove the component and cause the error. SSMS 升级到已解决该问题的最新版本。Upgrade SSMS to the latest version that has the issue fixed.

错误消息:“SSIS 执行程序退出代码: -1073741819。”Error message:“SSIS Executor exit code: -1073741819.”

  • 可能的原因和建议的操作:Potential cause & recommended action:
    • 出现此错误的原因可能是,以多线程并行执行多个 Excel 源或目标时,Excel 源和目标遭到限制。This error may be because of the limitation for Excel source and destination when multiple Excel sources or destinations are executing in parallel in multi-thread. 可通过以下方式解决此限制:将 Excel 组件更改为按顺序执行,或将其分隔到不同的包中,并在将 ExecuteOutOfProcess 属性设置为 True 的情况下,通过“执行包任务”来触发执行。You can workaround this limitation by change your Excel components to execute in sequence, or separate them into different packages and trigger through "Execute Package Task" with ExecuteOutOfProcess property set as True.

错误消息:“磁盘上没有足够的空间”Error message: "There is not enough space on the disk"

此错误表示 SSIS Integration Runtime 节点中的本地磁盘空间已用尽。This error means the local disk is used up in the SSIS integration runtime node. 请检查你的包或自定义安装是否占用了大量的磁盘空间:Check whether your package or custom setup is consuming a lot of disk space:

  • 如果包占用了磁盘,包执行完成后会释放磁盘空间。If the disk is consumed by your package, it will be freed up after the package execution finishes.
  • 如果自定义安装占用了磁盘,则你需要停止 SSIS Integration Runtime,修改脚本,然后再次启动 Integration Runtime。If the disk is consumed by your custom setup, you'll need to stop the SSIS integration runtime, modify your script, and start the integration runtime again. 为自定义安装指定的整个 Azure Blob 容器将复制到 SSIS Integration Runtime 节点,因此,请检查该容器中是否包含任何不必要的内容。The whole Azure blob container that you specified for custom setup will be copied to the SSIS integration runtime node, so check whether there's any unnecessary content under that container.

错误消息:“无法从主节点检索资源。Error message: "Failed to retrieve resource from master. Microsoft.SqlServer.IntegrationServices.Scale.ScaleoutContract.Common.MasterResponseFailedException:代码: 300004。Microsoft.SqlServer.IntegrationServices.Scale.ScaleoutContract.Common.MasterResponseFailedException: Code:300004. 说明: 加载文件 "***" 失败。”Description:Load file "***" failed."

  • 可能的原因和建议的操作:Potential cause & recommended action:
    • 如果 SSIS 活动正在从文件系统(包文件或项目文件)执行包,而使用你在 SSIS 活动中提供的包访问凭据无法访问项目、包或配置文件,则会出现此错误If the SSIS Activity is executing package from file system (package file or project file), this error will occur if the project, package or configuration file is not accessible with the package access credential you provided in the SSIS Activity
      • 如果使用 Azure 文件:If you are using Azure File:
        • 文件路径应以 \\<storage account name>.file.core.chinacloudapi.cn\<file share path> 开头The file path should start with \\<storage account name>.file.core.chinacloudapi.cn\<file share path>
        • 域应是“Azure”The domain should be "Azure"
        • 用户名应为 <storage account name>The username should be <storage account name>
        • 密码应为 <storage access key>The password should be <storage access key>
      • 如果使用本地文件,请检查是否正确配置了 VNet、包访问凭据和权限,以便 Azure-SSIS 集成运行时可以访问你的本地文件共享If your are using on-premises file, please check if VNet, package access credential and permission are configured properly so that your Azure-SSIS integration runtime can access your on-premises file share

错误消息:“连接中指定的文件名 '...' 无效”Error message: "The file name '...' specified in the connection was not valid"

  • 可能的原因和建议的操作:Potential cause & recommended action:
    • 指定的文件名无效An invalid file name is specified
    • 确保在连接管理器中使用 FQDN(完全限定的域名)而不是短时间Make sure you are using FQDN (Fully Qualified Domain Name) instead of short time in your connection manager

错误消息:“无法打开文件 '...'”Error message: "Cannot open file '...'"

当包执行在 SSIS Integration Runtime 中的本地磁盘内找不到文件时,将发生此错误。This error occurs when package execution can't find a file in the local disk in the SSIS integration runtime. 请尝试以下操作:Try these actions:

  • 不要在 SSIS Integration Runtime 中执行的包内使用绝对路径。Don't use the absolute path in the package that's being executed in the SSIS integration runtime. 改用当前执行工作目录 (.) 或临时文件夹 (%TEMP%)。Use the current execution working directory (.) or the temp folder (%TEMP%) instead.
  • 如果需要在 SSIS Integration Runtime 节点上保留某些文件,请根据自定义安装中所述准备文件。If you need to persist some files on SSIS integration runtime nodes, prepare the files as described in Customize setup. 执行完成后,系统会清理工作目录中的所有文件。All the files in the working directory will be cleaned up after the execution is finished.
  • 改用 Azure 文件来存储 SSIS Integration Runtime 节点中的文件。Use Azure Files instead of storing the file in the SSIS integration runtime node. 有关详细信息,请参阅使用 Azure文件共享For details, see Use Azure file shares.

错误消息:“数据库 'SSISDB' 已达到大小配额”Error message: "The database 'SSISDB' has reached its size quota"

可能的原因是,在 Azure SQL 数据库或 SQL 托管实例中创建的 SSISDB 数据库已达到其配额。A potential cause is that the SSISDB database created in Azure SQL Database or in SQL Managed Instance has reached its quota. 请尝试以下操作:Try these actions:

错误消息:“数据库的请求限制是 ...,现已达到该限制。”Error message: "The request limit for the database is ... and has been reached."

如果在 SSIS Integration Runtime 中同时运行许多的包,可能会发生此错误,因为 SSISDB 已达到请求限制。If many packages are running in parallel in the SSIS integration runtime, this error might occur because SSISDB has hit its request limit. 请考虑增加 SSISDB 的 DTC 来解决此问题。Consider increasing the DTC of SSISDB to resolve this issue. 有关详细信息,可参阅逻辑服务器的 SQL 数据库限制You can find details in SQL Database limits for an logical server.

错误消息:“SSIS 操作失败并出现意外的操作状态: ...”Error message: "SSIS Operation failed with unexpected operation status: ..."

该错误主要是由某个暂时性问题导致的,因此请尝试重新运行包执行。The error is mostly caused by a transient problem, so try to rerun the package execution. 执行以下步骤,在 SSIS 包中应用重试模式:Apply the retry pattern in the SSIS package by following these steps:

  • 确保 SSIS 包在失败时可以重新运行,且不产生负面影响(例如数据丢失或数据重复)。Make sure your SSIS packages can rerun on failure without side effects (for example, data loss or data duplication).
  • 在“常规”选项卡上配置“执行 SSIS 包”活动的“重试”和“重试间隔”。 在“常规”选项卡上设置属性Configure Retry and Retry interval of Execute SSIS Package activity on the General tab. Set properties on the General tab
  • 对于 ADO.NET 和 OLE DB 源或目标组件,请在 SSIS 包或 SSIS 活动的连接管理器中设置 ConnectRetryCountConnectRetryIntervalFor an ADO.NET and OLE DB source or destination component, set ConnectRetryCount and ConnectRetryInterval in Connection Manager in the SSIS package or SSIS activity.

错误消息:“没有任何活动的辅助角色。”Error message: "There is no active worker."

此错误通常表示 SSIS Integration Runtime 处于不正常状态。This error usually means the SSIS integration runtime has an unhealthy status. 请在 Azure 门户中检查状态和详细错误。Check the Azure portal for the status and detailed errors. 有关详细信息,请参阅 Azure-SSIS Integration RuntimeFor more information, see Azure-SSIS integration runtime.

错误消息:“集成运行时无法升级,最终将会停止工作,因为我们无法访问你为自定义安装提供的 Azure Blob 容器。”Error message: "Your integration runtime cannot be upgraded and will eventually stop working, since we cannot access the Azure Blob container you provided for custom setup."

当 SSIS Integration Runtime 无法访问针对自定义安装配置的存储时,将发生此错误。This error occurs when the SSIS integration runtime can't access the storage configured for custom setup. 请检查提供的共享访问签名 (SAS) URI 是否有效且未过期。Check whether the shared access signature (SAS) URI that you provided is valid and hasn't expired.

错误消息:“Microsoft OLE DB Provider for Analysis Services。Error message: "Microsoft OLE DB Provider for Analysis Services. ‘Hresult:0x80004005 说明:’COM 错误:COM 错误: mscorlib;某个调用的目标引发了异常”'Hresult: 0x80004005 Description:' COM error: COM error: mscorlib; Exception has been thrown by the target of an invocation"

一种潜在原因是为 Azure Analysis Services 身份验证配置了已启用 Azure 多重身份验证的用户名或密码。One potential cause is that the username or password with Azure Multi-Factor Authentication enabled is configured for Azure Analysis Services authentication. SSIS Integration Runtime 不支持这种身份验证。This authentication isn't supported in the SSIS integration runtime. 尝试使用服务主体进行 Azure Analysis Services 身份验证:Try to use a service principal for Azure Analysis Services authentication:

  1. 请根据使用服务主体进行自动化中所述准备服务主体。Prepare a service principal as described in Automation with service principals.
  2. 在连接管理器中,配置“使用特定的用户名和密码”:将“AppID”设为用户名,将“clientSecret”设为密码。 In Connection Manager, configure Use a specific user name and password: set AppID as the username and clientSecret as the password.

错误消息:使用托管标识时发生错误“ADONET 源无法获取连接 {GUID} 并出现以下错误消息:用户 'NT AUTHORITY\ANONYMOUS LOGON' 登录失败”Error message: "ADONET Source has failed to acquire the connection {GUID} with the following error message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" when using a managed identity

如果参数 ConnectUsingManagedIdentityTrue,请确保不要将连接管理器的身份验证方法配置为“Active Directory密码身份验证”。Make sure you don't configure the authentication method of Connection Manager as Active Directory Password Authentication when the parameter ConnectUsingManagedIdentity is True. 可将其配置为“SQL 身份验证”,设置了 ConnectUsingManagedIdentity 时会忽略此配置。You can configure it as SQL Authentication instead, which is ignored if ConnectUsingManagedIdentity is set.

错误消息:“0xC020801F 出现在...,OData 源 [...]:无法从运行时连接管理器获取托管连接”Error message: "0xC020801F at ..., OData Source [...]: Cannot acquire a managed connection from the run-time connection manager"

一个可能的原因是,未在 SSIS 集成运行时中启用 OData 源所需的传输层安全性 (TLS)。One potential cause is that the Transport Layer Security (TLS) is not enable in SSIS integration runtime which is required by your OData source. 可以使用自定义设置在 SSIS 集成运行时中启用 TLS。You can enable TLS in SSIS integration runtime by using Customize setup. 有关更多详细信息,请参见无法通过 SSIS 连接 Project Online Odata自定义 Azure-SSIS 集成运行时的设置More detail can be found at Can't connect Project Online Odata from SSIS and Customize setup for the Azure-SSIS integration runtime.

错误消息:“由于出现错误,请求操作 guid 为 ... 的暂存任务失败:无法调度暂存操作,错误消息如下:Microsoft.SqlServer.IntegrationServices.AisAgentCore.AisAgentException:无法加载数据代理。”Error message: "Request staging task with operation guid ... fail since error: Failed to dispatch staging operation with error message: Microsoft.SqlServer.IntegrationServices.AisAgentCore.AisAgentException: Failed to load data proxy."

请确保 Azure-SSIS 集成运行时配置了自承载集成运行时。Make sure your Azure-SSIS integration runtime is configured with Self-Hosted integration runtime. 有关更多详细信息,请参见在 ADF 中将自承载 IR 配置为 Azure-SSIS IR 的代理More detail can be found at Configure Self-Hosted IR as a proxy for Azure-SSIS IR in ADF.

错误消息:“暂存任务状态:已失败。Error message: "Staging task status: Failed. 暂存任务错误:ErrorCode:2010, ErrorMessage:自承载集成运行时...脱机”Staging task error: ErrorCode: 2010, ErrorMessage: The Self-hosted Integration Runtime ... is offline"

请确保已安装并启动了自承载集成运行时。Make sure your Self-Hosted integration runtime is installed and started. 有关更多详细信息,请参阅创建和配置自承载集成运行时More detail can be found at Create and configure a self-hosted integration runtime

错误消息:“暂存任务错误:ErrorCode:2906, ErrorMessage:包执行失败,输出: {"OperationErrorMessages":“错误:所请求的 OLE DB 提供程序 ... 未注册。Error message: "Staging task error: ErrorCode: 2906, ErrorMessage: Package execution failed., Output: {"OperationErrorMessages": "Error: The requested OLE DB provider ... is not registered. 如果未安装 64 位驱动程序,请在 32 位模式下运行包...”If the 64-bit driver is not installed, run the package in 32-bit mode..."

请确保包中 OLE DB 连接器使用的相应提供程序已正确安装在自承载集成运行时计算机上。Make sure the corresponding provider used by your OLE DB connectors in your package are installed on Self-Hosted integration runtime machine properly. 有关更多详细信息,请参见在 ADF 中将自承载 IR 配置为 Azure-SSIS IR 的代理More detail can be found at Configure Self-Hosted IR as a proxy for Azure-SSIS IR in ADF

错误消息:“暂存任务错误:ErrorCode:2906, ErrorMessage:包执行失败,输出: {"OperationErrorMessages":“错误:System.IO.FileLoadException:无法加载文件或程序集 'Microsoft.WindowsAzure.Storage, Version=..., Culture=neutral, PublicKeyToken=31bf3856ad364e35' 或它的某一个依赖项。Error message: "Staging task error: ErrorCode: 2906, ErrorMessage: Package execution failed., Output: {"OperationErrorMessages": "Error: System.IO.FileLoadException: Could not load file or assembly 'Microsoft.WindowsAzure.Storage, Version=..., Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. 找到的程序集清单定义与程序集引用不匹配...”The located assembly's manifest definition does not match the assembly reference.'..."

一个可能的原因是自承载集成运行时未正确安装或升级。One potential cause is your Self-Hosted integration runtime is not installed or upgraded properly. 建议下载并重新安装最新的自承载集成运行时。Suggest to download and reinstall the latest Self-hosted integration runtime. 有关更多详细信息,请参阅创建和配置自承载集成运行时More detail can be found at Create and configure a self-hosted integration runtime

错误消息:“请求元数据时必须建立连接。Error message: "A connection is required when requesting metadata. 如果正在脱机工作,请取消选中‘SSIS’菜单上的‘脱机工作’,以启用连接”If you are working offline, uncheck Work Offline on the SSIS menu to enable the connection"

错误消息:“暂存任务状态:已失败。Error message: "Staging task status: Failed. 暂存任务错误:ErrorCode:2906, ErrorMessage:包执行失败,输出: {"OperationErrorMessages":"SSIS Executor exit code: -1.\n", "LogLocation": "...\SSISTelemetry\ExecutionLog\...", "effectiveIntegrationRuntime": "...", "executionDuration": ..., "durationInQueue": { "integrationRuntimeQueue": ... }}"Staging task error: ErrorCode: 2906, ErrorMessage: Package execution failed., Output: {"OperationErrorMessages": "SSIS Executor exit code: -1.\n", "LogLocation": "...\SSISTelemetry\ExecutionLog\...", "effectiveIntegrationRuntime": "...", "executionDuration": ..., "durationInQueue": { "integrationRuntimeQueue": ... }}"

请确保在自承载集成运行时计算机上安装了 Visual C++ 运行时。Make sure Visual C++ runtime is installed on Self-Hosted integration runtime machine. 有关更多详细信息,请参见在 ADF 中将自承载 IR 配置为 Azure-SSIS IR 的代理More detail can be found at Configure Self-Hosted IR as a proxy for Azure-SSIS IR in ADF

意外触发多个包执行Multiple Package executions are triggered unexpectedly

  • 可能的原因和建议的操作:Potential cause & recommended action:
    • ADF 存储过程活动或 Lookup 活动用于触发 SSIS 包执行。ADF stored procedure activity or Lookup activity are used to trigger SSIS package execution. t-sql 命令可能会遇到暂时性问题,并触发重新运行,这将导致多个包执行。The t-sql command may hit transient issue and trigger the rerun which would cause multiple package executions.
    • 请改用 ExecuteSSISPackage 活动,以确保除非用户在活动中设置重试计数,否则包执行不会重新运行。Use ExecuteSSISPackage activity instead which ensures package execution won't rerun unless user set retry count in activity. 可以在 https://docs.azure.cn/data-factory/how-to-invoke-ssis-package-ssis-activity 中找到详细信息Detail can be found at https://docs.azure.cn/data-factory/how-to-invoke-ssis-package-ssis-activity
    • 优化 t-sql 命令,以便能够通过检查是否已触发执行来重新运行Refine your t-sql command to be able to rerun by checking if an execution has already been triggered

包执行时间太长Package execution takes too long

下面是可能的原因和建议的操作:Here are potential causes and recommended actions:

  • 在 SSIS Integration Runtime 中计划了过多的包执行。Too many package executions have been scheduled on the SSIS integration runtime. 所有这些执行将在队列中等待发生。All these executions will be waiting in a queue for their turn.
  • SSIS Integration Runtime 已停止或处于不正常状态。The SSIS integration runtime is stopped or has an unhealthy status. 若要了解如何检查 SSIS Integration Runtime 状态和错误,请参阅 Azure-SSIS Integration RuntimeTo learn how to check the SSIS integration runtime status and errors, see Azure-SSIS integration runtime.

我们还建议在“常规”选项卡上设置超时:在“常规”选项卡上设置属性We also recommend that you set a timeout on the General tab: Set properties on the General tab.

包的执行性能不佳Poor performance in package execution

请尝试以下操作:Try these actions:

  • 确保 SSIS Integration Runtime 与数据源和目标位于同一区域。Make sure the SSIS integration runtime is in the same region as the data source and destination.

  • 将包执行的日志记录级别设置为“性能”,以收集执行中每个组件的持续时间信息。Set the logging level of package execution to Performance to collect duration information for each component in the execution. 有关详细信息,请参阅 Integration Services (SSIS) 日志记录For details, see Integration Services (SSIS) logging.

  • 在 Azure 门户中检查 IR 节点性能:Check IR node performance in the Azure portal:

    • 有关如何监视 SSIS Integration Runtime 的信息,请参阅 Azure-SSIS Integration RuntimeFor information about how to monitor the SSIS integration runtime, see Azure-SSIS integration runtime.
    • 可以查看 Azure 门户中数据工厂的指标来查找 SSIS Integration Runtime 的 CPU/内存历史记录。You can find CPU/memory history for the SSIS integration runtime by viewing the metrics of the data factory in the Azure portal. 监视 SSIS Integration Runtime 的指标Monitor metrics of the SSIS integration runtime