The metadata could not be determined because statement 'insert into

Question

I have a select statement, where I have created 2 temp tables and doing an insert into select before taking the data from those temp tables creating a join between them. This final select is what I want the metadata to be. In ssms it runs fine, in ssis I don't know why its throwing that error. Query is as such:

CREATE TABLE #Per (PerID bigint NOT NULL......)
CREATE TABLE #Pre (PerID bigint NOT NULL, IsWorking.......)
INSERT INTO #Per SELECT .... FROM .....
INSERT INTO #Pre SELECT .... FROM .....
SELECT * FROM #Per per LEFT JOIN #Pre pre ON per.PerID = pre.PerID

I have tested all the statements to make sure they work and the query as a whole and it returns me the data, but ssis is throwing the error:

The metadata could not be determined because statement 'INSERT INTO #Per SELECT ...... uses a temp table.".
Error at project_name [646]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

Answer 1

try using a table variable instead something like:

DECLARE @Per TABLE (PerID bigint NOT NULL......)
DECLARE @Pre TABLE (PerID bigint NOT NULL, IsWorking.......)
INSERT INTO @Per SELECT .... FROM .....
INSERT INTO @Pre SELECT .... FROM .....
SELECT * FROM @Per per LEFT JOIN @Pre pre ON per.PerID = pre.PerID

Should work fine

Answer 2

If you are working on SSIS 2012 or later versions, then it uses system stored procedure sp_describe_first_result_set to fetch the metadata of the tables and it does not support temporary tables.
But you can use other options like table variables and CTEs.