在SSIS中使用临时表

相关文章推荐

斯文的紫菜汤 · MySQL学习笔记 - 6 - ...· 1 年前 ·

温文尔雅的杨桃 · Get-AzureRmDataLakeSto ...· 1 年前 ·

博学的烤土司 · Python图片转二进制 ...· 1 年前 ·

眼睛小的桔子 · HTTP/2 h2 ...· 1 年前 ·

潇洒的吐司 · 从SQLAlchemy的“缓存”问题说起 - 简书· 1 年前 ·

在数据流任务[OLE DB源[1]]出错。SSIS错误代码 dts_e_oledberror。发生了一个OLE DB错误。错误代码。 0x80004005.有一条OLE DB记录可用。来源。"Microsoft SQL 服务器本地客户端 10.0" 结果: 0x80004005 描述:"无效的对象名称'##Payment'"。

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

这是否意味着我不能在SP中使用临时表，如果我想让它被SSIS消费的话？

1 个评论

hakre ：

@sriram认为你也可以查看下面的链接以了解更多细节，这是一个技巧，我不知道为什么微软的人让它更简单...... - sqllike.com/using-temporary-tables-with-ssis-40.html

visual-studio-2008

sql-server-2008

ssis

Jason M

发布于 2009-10-17

7 个回答

Henrik Staun Poulsen

发布于 2009-10-22

0 人赞同

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，还提到了另一个解决方案。请看选项3。
(2020年11月；更新链接)
在存储过程中添加一些元数据和 "set nocount on"，并添加一个 "短路的if子句"（如果1=0），以及在顶部添加一个假的选择语句。  我测试了一下，试图不加 "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 
-- Do real work starting here 
CREATE TABLE #test 
      [id] [int] NULL, 
      [Name] [nchar] ( 10 ) NULL, 
      [SirName] [nchar] ( 10 ) NULL


          
           
            
             
              这是标准的解决方案，也是迄今为止最容易实现的。  不需要创建其他数据库对象或避免使用存储过程。


          
           
            
             
              就我所见，这是最好的解决方案。
              
               
                SET FMTONLY OFF
               
              
              会降低性能，表变量并不总是合适的，不需要搞什么
              
               
                DelayValidation
               
              
              ，而且它可以在SQL 2000中使用（因为我很不幸地发现了）。我发现你可以把假查询放在存储过程之外，放到Source组件中，这样可以保持你的过程不被触动，而且更干净。 例如：
              
               
                IF 1 = 0 BEGIN SELECT CAST(null as int) AS Foo END; EXEC udpMyProc
               
              
              我没有要求在语句的顶部
              
               
                SET NOCOUNT
               
              
              （尽管我的过程确实设置了它）


         
          
           
            
             设置fmtonly关闭 
在程序开始时设置fmtonly off，这将告诉我们在程序没有被执行时不要向客户端处理行。
当它没有被执行的时候 
因为在解析SP的时候没有临时表，所以在解析的时候没有可用的列。
            
            
             这让我终于可以工作了 :)


         
          
           
            
             Matt Palmerlee
            
            ：


          
           
            
             
              我试过这样做，但当我在SSIS中运行软件包时，它冻结了，并且在开始处理之前要花很长时间。  如果我去掉SET FMTONLY OFF，并切换回表变量而不是临时表，包就能正常启动。


          
           
            
             
              你要不惜一切代价避免使用这个方法，除非性能从来不是问题。这可能导致你的存储过程运行多达5次而不是一次。


        
         
          
           
            
            
             Registered User
            
           
           
            发布于
            
            2009-10-22


         
          
           
            
             如果错误是在BIDS中发生的，那么ajdams的解决方案将不起作用，因为它只适用于从SQL Server Agent中运行软件包时发生的错误。
            
            
             主要问题是，SSIS正在努力解决元数据。  从它的立场来看，##表并不存在，因为它在预执行阶段无法返回对象的元数据。  所以你必须找到一种方法来满足它对表已经存在的要求。  有几个解决方案。
            
            
             
              不要使用临时表。  相反，创建一个工作的数据库，把你所有的对象放在里面。  显然，如果你想在一个不是dbo的服务器上获取数据，如生产服务器，这可能是行不通的，所以你不能依赖这个解决方案。
             
            
            
             
              使用CTE而不是临时表。  如果你的源服务器是2005/2008，这就可以了。  如果源服务器是2000，这就没有帮助。
             
            
            
             
              在一个单独的Execute SQL命令中创建##表。  将连接的RetainSameConnection属性设置为True。  为数据流设置DelayValidation为true。  当你设置数据流时，通过在存储过程的顶部临时添加一个SELECT TOP 0字段=CAST(NULL AS INT)来伪造它，这个存储过程的元数据与你的最终输出相同。  记得在运行包之前将其从存储过程中删除。  这也是在数据流之间共享临时表数据的一个方便的技巧。  如果你想让包的其他部分使用单独的连接，以便它们可以并行运行，那么你必须创建一个额外的非共享连接。  这就回避了这个问题，因为临时表在数据流任务运行时已经存在。
             
            
            
             选项3可以实现你的目标，但它很复杂，而且有一个限制，即你必须将创建###命令分离到另一个存储过程调用中。  如果你有能力在源服务器上创建存储过程，那么你可能也有能力创建其他对象，如暂存表，这通常是一个更好的解决方案。  它还可以回避可能出现的TempDB争用问题，这也是一个理想的好处。
            
            
             祝你好运，如果你需要关于如何实施第3步的进一步指导，请告诉我。


         
          
           
            
             不是的，这是一个权限问题。这应该有助于你。
            
            
             
              http://support.microsoft.com/kb/933835


         
          
           
            
             对于所有涉及的麻烦，我认为这可能是不值得的。在数据库中创建一个真正的表，并在加载之前/之后截断它。如果是用于数据仓库，即使你有一个或两个额外的表也没有关系。这为你提供了设计时的SSIS工具，意味着你不必担心临时表的复杂性。
            
            
             如果你想把事情分开，那么就在一个单独的模式中创建你的SSIS临时表。你可以使用权限来使所有其他用户看不到这个表。
            
            CREATE SCHEMA [ssis_temp]
CREATE TABLE [ssis_temp].[tempTableName]


        
         
          
           
            
             
              
              
               Irawan Soetomo
              
             
             
              发布于
              
              2009-10-22


         
          
           
            
             
              
               这些步骤帮助了我。
              
              
               将最终的结果集写进一个表格。
              
              
               在一个新的查询编辑器窗口中，将该表脚本为CREATE。
              
              
               除定义列的开括号和闭括号外，删除所有东西。
              
              
               将其包入另一对支架。
              
              
               
                重新构思你的SP的调用，从
               
               
                exec p_MySPWithTempTables ? , ?
               
              
              exec p_MySPWithTempTables ?, ? with result sets
        ColumnA int,
        ColumnB varchar(10),
        ColumnC datetime