在 SSIS 流中使用临时表失败 [英] Using temporary tables in SSIS flow fails

问题描述

我有一个 ETL 过程，它从源数据库 (Oracle 10g) 中提取约 40 个表到 SQL Server(2014 开发人员版)登台环境.我的提取过程:

I have an ETL process which extracts ~40 tables from a source database (Oracle 10g) to a SQL Server (2014 developer edition) Staging environment. My process for extraction:

1. 确定暂存中的最新行
2. 从源中选择所有较新的行
3. 将结果插入#TEMPTABLE
4. 将#TEMPTABLE 的结果合并到 Staging

这适用于从本地 Visual Studio 和从 SQL Server 上的 SSISDB 执行的逐个包.

This works on a package by package basis both from Visual Studio locally and executing from SSISDB on the SQL Server.

不过，我将我的 Extract 作业分组到一个主包中，以便于执行和流向转换阶段.我的包中只有大约 5 个使用临时表，其他的都是 trunc 和 load，但是想将更多的内容移到这种方法中.当我运行主包时，使用临时表的任何内容都会失败.由于日志文件非常大，很难确定实际错误，但到目前为止，它告诉我的只是找不到 #TEMPTABLE 和/或状态为 VS_ISBROKEN.

However I am grouping my Extract jobs into one master package for ease of execution and flow to the transform stage. Only approximately 5 of my packages use temporary tables, the others are all trunc and load, but wanted to move some more to this method. When i run the master package anything using a temporary table fails. Because of pretty large log files, its hard to pinpoint the actual error but so far all it tells me is that the #TEMPTABLE can't be found and/or the status is VS_ISBROKEN.

我尝试过的事情:

1. 将所有相关组件设置为delay validation = false
2. 主包具有 ExecuteOutOfProcess = true
3. 增加的 tempdb 容量远远超出我的需要

我的一个想法是我的临时数据库连接上的 RetainSameConnection = true - 这可能是原因吗?我会尝试为每个连接创建单独的连接，但假设 ExecuteOutOfProcess 会为我处理这个问题.

A thought i had was the RetainSameConnection = true on my Staging database connection - could this be the cause? I would try to create separate connections for each, but assumed the ExecuteOutOfProcess would take care of this for me.

编辑

我创建了以下场景:

• 包 A(仅包含执行包任务引用的主包)
  • 包 B(使用临时表)
  • 套餐 C(无临时表)

  自行执行包 B 成功完成.所有临时表的使用都包含在这个包中 - 包 C 不需要查看包 B 创建的临时表.

  Executing Package B on it's own completes successfully. All temp table usage is contained within this package - there is no requirement for Package C to see the temp table created by Package B.

  执行包 C 成功完成.

  Executing Package C completes successfully.

  执行包 A，C 成功完成，B 失败.

  Executing Package A, C completes successfully, B fails.

  更新

  解决方法是为每个使用临时表的包创建一个包级连接，从而确保每个包都拥有自己的连接.我向 Microsoft 提出了一个连接问题，因为我相信当父包打开连接时，它应该在所有子包中继承和保留.

  The workaround was to create a package level connection for each package that uses temporary tables, thus ensuring that each package held its own connection. I have raised a connect issue with Microsoft as i believe that as the parent package opens the connection it should inherit and retain throughout any child packages.

  推荐答案

  针对您的案例的一些建议.

  Several suggestions to your case.

  1. 设置 RetainSameCoonection=true.这将允许您安全地使用 SSIS 包中的 TEMP 表.
  2. 不会使用 ExecuteOutOfProcess，它会增加您的 RAM 占用空间，因为每个子包都将在其进程中启动，并降低性能 - 增加进程启动延迟.这在 32 位环境中用于克服 2 GB 限制，但在 x64 上不再需要.
     子包执行不会从其父包继承连接对象实例，因此同一连接不会跨越所有子包.
  3. 带有临时表操作的 SSIS 包更难调试(不太明显)，所以要注意测试.
  1. Set RetainSameCoonection=true. This will allow you to work safely with TEMP tables in SSIS packages.
  2. Would not use ExecuteOutOfProcess, it will increase your RAM footprint since every Child pack will start in its process, and decrease performance - add process start lag. This used in 32-bit environments to overcome 2 GB limit, but on x64 it is no longer necessary.
     Child package execution does not inherit connection object instances from its Parent, so the same connection will not be spanned across all of your Child packages.
  3. SSIS Packages with Temp table operations are more difficult to debug (less obvious), so pay attention to testing.

  这篇关于在 SSIS 流中使用临时表失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！