在SSIS中使用Temp表

2020年11月更新。
How to EXEC a stored procedure from SSIS to get its output to text file 它描述了如何从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 请看选项3。 (2020年11月;更新链接)

报价: 在存储过程中添加一些元数据和“set nocount on”,并在顶部添加一个“shorted if子句”(如果1=0)和一个伪select语句。我试着把“set no count 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 
    ) 

我用过

关闭FMTONLY 在过程开始时,这将告诉客户端不要处理行

我终于开始工作了:)

如果在BIDS中出现错误,则ajdams解决方案将无法工作,因为它仅适用于从SQL Server代理运行包时出现的错误。

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

1. 不要使用临时桌子。相反,创建一个工作数据库并将所有对象放入其中。显然,如果你试图在一个服务器上获取数据,而不是像生产服务器那样的dbo,这可能行不通,所以你不能依赖这个解决方案。

3. 在单独的Execute SQL命令中创建##表。将连接的RetainSameConnection属性设置为True。将数据流的DelayValidation设置为true。当您设置数据流时,通过在存储过程的顶部临时添加一个SELECT TOP 0字段=CAST(NULL AS INT)来伪造它,该存储过程与您的最终输出具有相同的元数据。在运行包之前,请记住从存储过程中删除此项。这也是在数据流之间共享临时表数据的一个方便的技巧。如果你想让包的其余部分使用单独的连接,以便它们可以并行运行,那么你必须创建一个额外的非共享连接。这避免了问题,因为在数据流任务运行时临时表已经存在。

选项3实现了您的目标,但它很复杂,并且有局限性,您必须将create##命令分离到另一个存储过程调用中。如果您有能力在源服务器上创建存储过程,那么您可能也有能力创建其他对象,如暂存表,这通常是一个更好的解决方案。它还避免了可能的TempDB争用问题,这也是一个可取的好处。

祝你好运,如果你需要关于如何实施步骤3的进一步指导,请告诉我。

不,这是权限问题。这将帮助您:

http://support.microsoft.com/kb/933835

如果你想把东西分开,那么只需在单独的模式中创建SSIS临时表。您可以使用权限使此schema对所有其他用户不可见。

CREATE SCHEMA [ssis_temp]

CREATE TABLE [ssis_temp].[tempTableName]

1. 将最终结果集写在表格中。
2. 将该表作为CREATE脚本写入新的“新建查询编辑器”窗口。
3. 删除除定义列的开括号和闭括号之外的所有内容。

5. 建议从以下位置呼叫您的SP

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

您可以使用表变量而不是临时表。这会奏效