SSIS包临时表关联CTE执行报错，已试延迟验证仍未解决

问题背景 #

在SSIS包中使用全局临时表##StrategyMapInventory_2023结合CTE查询时，首次执行正常，后续执行触发元数据发现错误，调整DelayValidation属性后问题仍未解决。

可行解决方案 #

方案1：将查询逻辑封装为存储过程 #

SSIS的OLE DB源在解析多语句复杂SQL（含DECLARE、CTE）时，对临时表的元数据识别存在缺陷。将查询逻辑封装为存储过程后，SSIS只需识别存储过程的输出元数据，绕过临时表的解析问题。

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

CREATE PROCEDURE dbo.GetStrategyMapInventoryReport
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ReportGroup VARCHAR(250) = 'Strategy Map 2023'
        , @StartDate DATE = CONVERT(DATE, '1/1/2023')
        , @EndDate DATE = CASE WHEN GETDATE() < '1/1/2024' THEN GETDATE() ELSE '12/31/2023' END

    ;WITH ImportDetail AS
    (   SELECT ReportGroup = @ReportGroup
            , ReportSubGroup = ProgramGroup
            , MetricName
            , WeekID
            , RowNumber = ROW_NUMBER() OVER (PARTITION BY ProgramGroup, MetricName, PersonID ORDER BY Date)
            , PersonID
            , Date
        FROM ##StrategyMapInventory_2023
        WHERE [Date] >= @StartDate
        GROUP BY ProgramGroup, MetricName, WeekID, PersonID, Date)
 
    , Detail AS
     (   SELECT ReportGroup
                , ReportSubGroup
            , MetricName
            , WeekID
            , Amount = COUNT(DISTINCT(PersonID))
        FROM ImportDetail
        WHERE RowNumber = 1
        GROUP BY ReportGroup, ReportSubGroup, MetricName, WeekID)

    , AllReportGroups AS
    (   SELECT DISTINCT ReportGroup FROM Detail )

    , AllReportSubGroups AS
    (   SELECT DISTINCT ReportSubGroup FROM Detail )

    , Categories AS
    (   SELECT DISTINCT MetricName FROM ##StrategyMapInventory_2023 )

    , AllWeeksAndCategories AS
    (   SELECT w.ID, c.MetricName
        FROM reference.WeekStartEnd w
        CROSS JOIN Categories c
        WHERE w.WeekEnd > @StartDate
        AND w.WeekEnd < DATEADD(DAY,7,GETDATE()) )

    , AllWeeksCategoriesAndReportGroups AS
    (   SELECT r.ReportGroup, rs.ReportSubGroup, w.ID, c.MetricName
        FROM reference.WeekStartEnd w
        CROSS JOIN Categories c
        CROSS JOIN AllReportGroups r
        CROSS JOIN AllReportSubGroups rs
        WHERE w.WeekEnd > @StartDate
        AND w.WeekEnd < DATEADD(DAY,7,GETDATE()) )

    , ResultsByProgram AS
    (   SELECT ReportGroup = awc.ReportGroup
            , ReportSubGroup = awc.ReportSubGroup
            , MetricName = COALESCE(d.MetricName, awc.MetricName)
            , WeekID = awc.ID
            , Amount = COALESCE(d.Amount, 0)
        FROM AllWeeksCategoriesAndReportGroups awc
        LEFT OUTER JOIN Detail d
            ON awc.ReportGroup = d.ReportGroup
            AND awc.ReportSubGroup = d.ReportSubGroup
            AND awc.MetricName = d.MetricName
            AND awc.ID = d.WeekID )

    , AllProgramsDetail AS
    (   SELECT PersonID
            , Date
            , Num = ROW_NUMBER() OVER (PARTITION BY PersonID, MetricName ORDER BY PersonID, MetricName, Date)
            , ReportGroup = @ReportGroup
            , ReportSubGroup = 'All'
            , MetricName
            , WeekID
        FROM ##StrategyMapInventory_2023
        WHERE [Date] BETWEEN @StartDate AND @EndDate )
 
    , AllProgramsSource AS
    (   SELECT ReportGroup, ReportSubGroup, MetricName, WeekID, Amount = COUNT(*)
        FROM AllProgramsDetail
        WHERE Num = 1
        GROUP BY ReportGroup, ReportSubGroup, MetricName, WeekID )

    SELECT * FROM ResultsByProgram
    UNION ALL
    SELECT * FROM AllProgramsSource
    ORDER BY ReportGroup, ReportSubGroup, MetricName, WeekID
END

2. 在SSIS的OLE DB Source中调用存储过程：

EXEC dbo.GetStrategyMapInventoryReport

方案2：禁用外部元数据验证 #

关闭SSIS对OLE DB源的元数据自动验证，避免触发临时表的解析错误。

1. 打开OLE DB Source编辑器，输入原SQL语句后，直接点击“确定”（忽略元数据报错）。
2. 右键对应的Data Flow Task，选择“属性”。
3. 将ValidateExternalMetadata属性设置为False。

注意：需确保SQL返回的列结构与下游组件（如目标表）完全一致，否则运行时会出现列不匹配错误。

方案3：替换全局临时表为本地临时表 #

全局临时表会跨会话残留，导致后续执行时元数据解析冲突。改用本地临时表并保持会话一致性：

1. 修改Execute SQL Task中的SQL为本地临时表：

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

CREATE TABLE #StrategyMapInventory_2023 (
    PersonID VARCHAR(80)
    , CaseID VARCHAR(80)
    , Date date
    , ProgramGroup VARCHAR (1300)
    , ProgramName VARCHAR(1300)
    , MetricName VARCHAR (50)
    , Classification VARCHAR (10)
    , Timeframe  VARCHAR (50)
    , WeekID int
    , WeekStart DATE
    , WeekEnd DATE
);
INSERT INTO #StrategyMapInventory_2023
SELECT * FROM bi.StrategyMapInventory_2023

2. 修改Data Flow Task中OLE DB Source的SQL，将所有##StrategyMapInventory_2023替换为#StrategyMapInventory_2023。

3. 确保Execute SQL Task和Data Flow Task使用同一个OLE DB连接管理器，并将连接管理器的RetainSameConnection属性设置为True（保证临时表在Data Flow执行时仍存在）。

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