在SSIS中使用临时表

Question

我正在SQL Server中的存储过程中使用临时表，我试图在OLE DB Source编辑器中使用该SP。我可以在Build Query按钮附带的查询生成器中看到数据输出返回的结果。但是，当我点击列选项卡时，出现以下错误。

- 标题：Microsoft Visual Studio

数据流任务[OLE DB Source [1]]中出现错误：SSIS错误代码DTS_E_OLEDBERROR。发生了一个OLE DB错误。 错误代码：0x80004005。有一个OLE DB记录可用。 来源：“Microsoft SQL Server Native Client 10.0 ” Hresult：0x80004005 描述：“无效的对象名称'##Payment'”。

数据流任务[OLE DB Source [1]]中出现错误：无法从数据源检索列信息。确保数据库中的目标表可用。

那是否意味着如果我想让SSIS使用它，我不能在SP中使用临时表？

- Jason M

---

@sriram认为您也可以查看以下链接以获取更多详细信息，这是一个技巧，我不知道为什么微软的人们让它变得更简单...-http://www.sqllike.com/using-temporary-tables-with-ssis-40.html - hakre

Answer 1

2020年11月更新。
本帖已被如何从SSIS执行存储过程以将其输出到文本文件中所取代，该帖描述了如何从SSIS运行存储过程。

exec mySproc WITH RESULT SETS ((i int))

请看 Troy Witthoeft 提供的解决方案。

旧答案
还有另一种解决方案在https://web.archive.org/web/20120915093807/http://sqlserverpedia.com/blog/sql-server-bloggers/ssis-stored-procedure-metadata中提到。看看第三个选项。 （2020年11月；更新链接）

引用： 向存储过程添加一些元数据和“set nocount on”，使用一个“短路if子句”（if 1 = 0），并在顶部添加一个虚假的select语句。 我尝试过将“set nocount on”省略掉，但不起作用。

CREATE PROCEDURE [dbo] . [GenMetadata] AS 
SET NOCOUNT ON 
IF 1 = 0 
    BEGIN
         -- Publish metadata 
        SELECT   CAST (NULL AS INT ) AS id , 
                CAST (NULL AS NCHAR ( 10 )) AS [Name] , 
                CAST (NULL AS NCHAR ( 10 )) AS SirName 
    END 

 -- Do real work starting here 
CREATE TABLE #test 
    ( 
      [id] [int] NULL, 
      [Name] [nchar] ( 10 ) NULL, 
      [SirName] [nchar] ( 10 ) NULL 
    ) 

- Henrik Staun Poulsen

---

这是标准解决方案，也是目前最容易实现的方法。没有必要创建其他数据库对象或避免使用存储过程。 - Eric Ness

从我所看到的来看，这是最好的解决方案。SET FMTONLY OFF 会降低性能，表变量并不总是适用的，不要去碰 DelayValidation，而且它可以在 SQL 2000 中使用（就像我不幸地发现的那样）。我发现你可以将虚拟查询放在存储过程之外，并放入源组件中，这样可以保持你的存储过程不受影响，更加简洁。例如：IF 1 = 0 BEGIN SELECT CAST(null as int) AS Foo END; EXEC udpMyProc 我在语句顶部没有使用 SET NOCOUNT（尽管我的存储过程确实设置了它）。 - Rhumborl

Answer 2

我在存储过程的开头使用了

SET FMTONLY OFF， 这将告诉客户端在不执行时不要处理行， 因为在解析存储过程时没有临时表，因此在解析时没有可用的列。

最终让我成功了 :)

- Jason M

---

我尝试过这个方法，但是当我在SSIS中运行包时，它会冻结并且需要很长时间才能开始处理。如果我删除SET FMTONLY OFF并切换回使用表变量而不是临时表，那么包就可以正常启动了。 - Matt Palmerlee

除非性能从不是问题，否则您要尽一切可能避免使用此功能。这可能会导致您的存储过程运行5次而不是1次。 - Jens

Answer 3

如果在BIDS中引发错误，则ajdams的解决方案将无效，因为它仅适用于从SQL Server Agent运行包时引发的错误。

主要问题是SSIS无法解析元数据。从其角度来看，##表不存在，因为它无法在预执行阶段返回对象的元数据。因此，您必须找到一种满足其要求的方式，即表已经存在。有几个解决方案：

1. 不要使用临时表。相反，创建一个工作数据库并将所有对象放入其中。显然，如果您正在尝试在您不是dbo（如生产服务器）的服务器上获取数据，则这可能不起作用，因此您不能依赖此解决方案。

2. 使用CTE代替临时表。如果源服务器是2005/2008，则可以使用此方法。如果源服务器是2000，则无法使用此方法。

3. 在单独的Execute SQL命令中创建##表。将连接的RetainSameConnection属性设置为True。对于数据流，请将DelayValidation设置为true。当您设置数据流时，通过在具有与最终输出相同的元数据的存储过程的顶部暂时添加SELECT TOP 0 field = CAST(NULL AS INT)来欺骗它。记得在运行包之前从存储过程中删除它。这也是在数据流之间共享临时表数据的巧妙技巧。如果您希望包的其余部分使用单独的连接以便可以并行运行，则必须创建另一个非共享连接。这样就避免了问题，因为在数据流任务运行时临时表已经存在。

选项3可以实现您的目标，但它很复杂，并且有一个限制，即您必须将create ##命令分离到另一个存储过程调用中。如果您有能力在源服务器上创建存储过程，则可能还有能力创建其他对象（如分段表），这通常是更好的解决方案。它还可以回避可能的TempDB争用问题，这也是一个理想的好处。

祝你好运，如果需要进一步指导如何实施第3步，请告诉我。

- Registered User

Answer 4

不是程序问题，而是权限问题。这篇文章应该会对你有所帮助：http://support.microsoft.com/kb/933835。

- ajdams

Answer 5

这些步骤对我很有帮助：

1. 将最终结果集写入表格。
2. 在新的查询编辑器窗口中将该表格脚本化为CREATE语句。
3. 删除除了定义列的开放和关闭括号之外的所有内容。
4. 将其包装在另一对括号中。

5. 重新组合调用存储过程的方式，从

   exec p_MySPWithTempTables ?, ?

变成

exec p_MySPWithTempTables ?, ? with result sets
(
    (
        ColumnA int,
        ColumnB varchar(10),
        ColumnC datetime
    )
)

- Irawan Soetomo

Answer 6

尽管有很多麻烦，我认为这可能并不值得。在数据库中创建一个真正的表，在加载之前/之后截断它。如果是数据仓库，多个表也不会有影响。这会为您提供设计时的SSIS工具，并意味着您不必担心临时表的复杂性。

如果您想保持分离，则只需在单独的模式中创建您的SSIS临时表。您可以使用权限使此模式对所有其他用户不可见。

CREATE SCHEMA [ssis_temp]

CREATE TABLE [ssis_temp].[tempTableName]

- AndyM

Answer 7

您可以使用表变量代替临时表，它会起作用。

- paranjai

---

是的，如果这个选项行不通，我会选择这个选项的。 :) - Jason M

但是临时表如何更好地帮助你呢？如果你计划使用临时表索引，情况可能会有所不同。 - paranjai

这是一个选项，但表变量没有可用的统计信息。 - Henrik Staun Poulsen