0

I need to use a temp table and CTE in SQL task in my SSIS package. But I got the following errors

" Metadata discovery only supports temp tables when analyzing a single- statement batch "

I tried to refer SSIS Package not wanting to fetch metadata of temporary table but I don't know how to do it in my case

The query that I'm using is

--Creating multiple temp table to increase preforming
SELECT *
INTO #TEMP_1
FROM MYTABLE

SELECT *
INTO #TEMP_2
FROM MYTABLE

SELECT *
INTO #TEMP_3
FROM MYTABLE

--create a final result set
;WITH CTE AS (
SELECT * FROM #TEMP_1
UNION
SELECT * FROM #TEMP_2
UNION
SELECT * FROM #TEMP_3
)


--data I eventually need 
SELECT col1,col2,col3
FROM CTE 
WHERE some_condition

All those code is working fine in SSMS but failed when I load to SSIS.

Any suggestion?

NewbeeLoveCoding
  • 37
  • 6

1 Answers1

0

If you're working with temporary tables and you're on SQL Server 2012+, an option is to use EXECUTE with the RESULT SETS option.

Given your supplied query, I wrapped it with an Execute and specified the column names and types.

EXECUTE(N'
SELECT 1 AS col1, NULL AS col2, NULL AS col3, ''A'' AS col4 INTO #TEMP_1
SELECT NULL AS col1, 2 AS col2, NULL AS col3, ''B'' AS col4 INTO #TEMP_2
SELECT NULL AS col1, NULL AS col2, 3 AS col3, ''John'' AS col4 INTO #TEMP_3

--Creating multiple temp table to increase preforming

--create a final result set
;WITH CTE AS (
SELECT * FROM #TEMP_1
UNION
SELECT * FROM #TEMP_2
UNION
SELECT * FROM #TEMP_3
)
--data I eventually need 
SELECT col1,col2,col3
FROM CTE 
WHERE 1=1 AND col4=''John''
') WITH RESULT SETS
(
    (Column1 int, Column2 int, Column3 int)
);

The OLE DB Source component was able to interpret that metadata just fine and out comes my data.

enter image description here

This solution works because it's is dynamic SQL. I am creating a big string and passing it to the EXEC call and specifying the RESULT SET which allows SSIS to properly infer the metadata. If, as I suspect, you need to fool around with dynamic filters and such, then you can do some fancy string building early on and reduce the EXEC call.

-- Do whatever needs to be done to properly define your queries
-- add filtering, etc
-- Before you EXEC, I find printing the resulting SQL handy so I can manually run it through a parser looking for mistakes

DECLARE @DynamicSQL nvarchar(max) = N'
SELECT 1 AS col1, NULL AS col2, NULL AS col3, ''A'' AS col4 INTO #TEMP_1
SELECT NULL AS col1, 2 AS col2, NULL AS col3, ''B'' AS col4 INTO #TEMP_2
SELECT NULL AS col1, NULL AS col2, 3 AS col3, ''John'' AS col4 INTO #TEMP_3

--Creating multiple temp table to increase preforming

--create a final result set
;WITH CTE AS (
SELECT * FROM #TEMP_1
UNION
SELECT * FROM #TEMP_2
UNION
SELECT * FROM #TEMP_3
)
--data I eventually need 
SELECT col1,col2,col3
FROM CTE 
WHERE 1=1 AND col4=''John''
';

EXECUTE(@DynamicSQL) WITH RESULT SETS
(
    (Column1 int, Column2 int, Column3 int)
);

To embed a quote within the existing single quoted mess of statements, you need to double them - two single quotes, not a double

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • 1.Is letter N in the first lane a typo? cuz it gives me error msg when i run it 2.if the query has a string value, do i need double quote inside the EXECUTE? for eg: WHERE name = "JOHN"? – NewbeeLoveCoding Feb 09 '23 at 03:57
  • 1
    N' means nvarchar string. It's not a typo. And yes, you need to double quote strings inside a string. – siggemannen Feb 09 '23 at 14:07
  • @NewbeeLoveCoding Run what I built and you'll see it works. Then adapt it to your use case. Specific to your situation, this is dynamic SQL. To embed a quote within the existing single quoted mess of statements, you need to double them - two single quotes, not a double. – billinkc Feb 09 '23 at 14:51