在SSIS数据流任务中使用临时表与CTE时遇错误求助

执行的SQL查询 #

IF OBJECT_ID('tempdb..##tempdb') is not null
drop table ##tempdb

select * into ##tempdb from Account_Master

;with cte
as
(
    select Beneficiary_ID, Account_Number, Account_ID
    from Beneficiary_Master
)
select beneficiary_id, account_number, Account_Type
from cte a
inner join ##tempdb b
    on a.account_id = b.Account_ID

报错信息 #

Exception from HRESULT: 0xC020204A Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. 发生OLE DB错误。错误代码: 0x80004005。存在可用的OLE DB记录。来源: "Microsoft SQL Server Native Client 11.0" HRESULT: 0x80004005 描述: "无法确定元数据，因为语句'with cte as ( select Beneficiary_ID, Account_Number, Account_ID from Beneficiary_Master )select'使用了临时表。元数据发现仅在分析单语句批处理时支持临时表。"
Data Flow Task [OLE DB Source [1]]错误：无法从数据源检索列信息。请确保数据库中的目标表可用。

错误原因 #

SSIS的OLE DB源组件仅支持在单语句批处理中识别临时表的元数据，当前查询包含多语句（临时表删除、创建、CTE查询），导致组件无法解析最终查询的返回列结构。

解决方案 #

方案1：改写查询移除临时表（推荐）

直接关联原表替代临时表，将查询简化为单语句批处理，SSIS可正常识别元数据：

;with cte
as
(
    select Beneficiary_ID, Account_Number, Account_ID
    from Beneficiary_Master
)
select 
    a.beneficiary_id, 
    a.account_number, 
    b.Account_Type
from cte a
inner join Account_Master b
    on a.account_id = b.Account_ID

方案2：用存储过程封装查询

1. 在SQL Server创建存储过程：

CREATE PROCEDURE dbo.GetBeneficiaryAccountData
AS
BEGIN
    IF OBJECT_ID('tempdb..##tempdb') IS NOT NULL
        DROP TABLE ##tempdb

    SELECT * INTO ##tempdb FROM Account_Master

    ;WITH cte AS
    (
        SELECT Beneficiary_ID, Account_Number, Account_ID
        FROM Beneficiary_Master
    )
    SELECT beneficiary_id, account_number, Account_Type
    FROM cte a
    INNER JOIN ##tempdb b
        ON a.account_id = b.Account_ID
END

2. 在SSIS的OLE DB源中选择「SQL命令」，输入调用语句：

EXEC dbo.GetBeneficiaryAccountData

3. 切换到「列」选项卡，SSIS会自动从存储过程返回结果获取元数据。

方案3：拆分任务执行

1. 添加执行SQL任务，单独执行临时表创建逻辑：

IF OBJECT_ID('tempdb..##tempdb') IS NOT NULL
    DROP TABLE ##tempdb

SELECT * INTO ##tempdb FROM Account_Master

2. 在后续Data Flow Task的OLE DB源中，仅执行CTE关联查询：

;with cte
as
(
    select Beneficiary_ID, Account_Number, Account_ID
    from Beneficiary_Master
)
select beneficiary_id, account_number, Account_Type
from cte a
inner join ##tempdb b
    on a.account_id = b.Account_ID

注意：需保证执行SQL任务与Data Flow Task的执行顺序正确，全局临时表##tempdb在包执行周期内持续存在。

内容的提问来源于stack exchange，提问作者Vishal Mishr