SSIS SQL任务使用临时表与CTE报错，请求解决方案

错误原因 #

SSIS的元数据发现机制无法处理多语句批量中的临时表——它需要在解析阶段明确知晓最终结果集的结构，但你的脚本拆分为多段（先创建临时表，再执行CTE查询），导致SSIS无法正确识别输出的元数据结构。

解决方法 #

方法1：显式定义结果集元数据（推荐） #

在脚本开头添加SET NOCOUNT ON避免额外行数干扰元数据识别，再用WITH RESULT SETS强制指定最终结果集的列名和数据类型，让SSIS直接使用你定义的元数据，无需自动发现。

修改后的代码示例：

SET NOCOUNT ON;

-- 创建临时表
SELECT * INTO #TEMP_1 FROM MYTABLE;
SELECT * INTO #TEMP_2 FROM MYTABLE;
SELECT * INTO #TEMP_3 FROM MYTABLE;

-- CTE和最终查询
WITH CTE AS (
    SELECT * FROM #TEMP_1
    UNION
    SELECT * FROM #TEMP_2
    UNION
    SELECT * FROM #TEMP_3
)
SELECT col1, col2, col3
FROM CTE 
WHERE some_condition 
WITH RESULT SETS (
    (
        col1 INT, -- 替换为实际列的数据类型
        col2 VARCHAR(50), -- 替换为实际列的数据类型
        col3 DATETIME -- 替换为实际列的数据类型
    )
);

注意：需将示例中的数据类型替换为MYTABLE对应列的真实类型。

方法2：用表变量代替临时表 #

表变量的元数据在编译阶段就已确定，SSIS可直接识别。将临时表替换为表变量，即可绕过元数据发现的限制。

修改后的代码示例：

-- 定义表变量（需完全匹配MYTABLE的列结构）
DECLARE @TEMP_1 TABLE (col1 INT, col2 VARCHAR(50), col3 DATETIME, ...); -- 补全所有列及类型
DECLARE @TEMP_2 TABLE (col1 INT, col2 VARCHAR(50), col3 DATETIME, ...);
DECLARE @TEMP_3 TABLE (col1 INT, col2 VARCHAR(50), col3 DATETIME, ...);

-- 填充表变量
INSERT INTO @TEMP_1 SELECT * FROM MYTABLE;
INSERT INTO @TEMP_2 SELECT * FROM MYTABLE;
INSERT INTO @TEMP_3 SELECT * FROM MYTABLE;

-- CTE和最终查询
WITH CTE AS (
    SELECT * FROM @TEMP_1
    UNION
    SELECT * FROM @TEMP_2
    UNION
    SELECT * FROM @TEMP_3
)
SELECT col1, col2, col3
FROM CTE 
WHERE some_condition;

注意：必须显式定义表变量的所有列和对应数据类型，不能像临时表那样用SELECT * INTO隐式创建。

方法3：拆分逻辑为多个SQL任务（备选） #

若坚持使用临时表，可将临时表的创建与填充放在一个SQL任务，CTE查询放在另一个任务，但需将连接管理器的RetainSameConnection属性设为True，确保两个任务共享同一数据库连接，临时表不会被销毁。此方法会增加包的复杂度，仅作为备选方案。

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