求助：SSIS中使用OLE-DB源临时表遇问题，已设置DelayValidation与RetainSameConnection仍未解决

I’ve wrestled with this exact SSIS temp table headache plenty of times— let’s walk through the most reliable fixes to get your workflow back on track:

1. Confirm Connection Scope & RetainSameConnection Setup #

First, make every task that touches the temp table (creation and querying) uses the exact same Connection Manager. The RetainSameConnection=True setting only applies to the specific connection it’s enabled on— if your OLE DB Source uses a different connection than the Execute T-SQL Task, the temp table will be invisible to it.

Double-check that RetainSameConnection is set to True on that shared connection manager (not just individual tasks).

2. Validate Task Order & T-SQL Syntax #

• Your temp table creation task must run before the Data Flow Task that queries it. Verify precedence constraints are set correctly— no parallel execution paths that could let the Data Flow run early.
• Ensure your CREATE TABLE syntax is complete and error-free. For example:

  CREATE TABLE #TempOrder (
      OrderID INT PRIMARY KEY,
      ProductName VARCHAR(100) NOT NULL,
      OrderDate DATETIME
  )
  

  If you’re inserting data right after creation, include that in the same T-SQL task (or a follow-up task using the same connection) to confirm the table is populated as expected.

3. Extend DelayValidation to the Entire Data Flow #

You’ve set DelayValidation=True on the OLE DB Source, but don’t forget to enable it on the Data Flow Task itself too. SSIS often runs validation at the container level, so even if the source is delayed, the Data Flow might still try to check metadata before the temp table exists.

4. Fix Metadata Retrieval for Temp Tables #

SSIS struggles with temp table metadata during design time because the table doesn’t exist until runtime. Try these workarounds:

Option A: Use a Table Variable (Small Datasets) #

Swap the local temp table for a table variable. SSIS can infer metadata from table variables at design time, avoiding validation errors entirely:

DECLARE @TempOrder TABLE (
    OrderID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    OrderDate DATETIME
);
-- Insert data into the table variable
INSERT INTO @TempOrder
SELECT OrderID, ProductName, OrderDate FROM SourceOrders;

Note: Table variables have performance limits for large datasets, so stick with this for small to medium volumes.

Option B: Force Metadata Retrieval with Dynamic SQL #

If you need to keep using a temp table, use dynamic SQL in your OLE DB Source query to bypass design-time validation. Add SET FMTONLY OFF to ensure SSIS grabs runtime metadata:

SET FMTONLY OFF;
SELECT * FROM #TempOrder;

Alternatively, wrap the query in an EXEC statement:

EXEC('SELECT OrderID, ProductName, OrderDate FROM #TempOrder');

After setting this, open the OLE DB Source editor, go to the Columns tab, and click the Refresh button to manually update metadata.

5. Debug to Confirm Temp Table Exists #

Add a Script Task right after your Execute T-SQL Task to verify the temp table is created and accessible:

• Use an ADO.NET connection to run SELECT COUNT(*) FROM #TempOrder
• Log the result to the SSIS log or a message box to confirm the table exists and has data.
  This helps rule out issues where the temp table isn’t actually being created due to syntax errors or permission gaps.

6. Try Global Temp Tables (Last Resort) #

If local temp tables still fail, switch to a global temp table (prefix with ## instead of #). But be warned: global temp tables are visible to all sessions, so they’ll cause conflicts if multiple instances of your package run at the same time. Only use this for single-instance deployments.

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