I used to loathe building data sources with temp tables. That is until I learned how to 'trick' SSIS with a simple work around.
Here is our data flow.
Here is the source query. The use of a temp table is unnecessary other than for demonstration.
When we preview the query in it's current state SSIS starts complaining about metadata.
Converting the query statement into a string and using the EXEC command allows us to use temp tables.
BEGIN EXEC('SELECT [OrderID] ,[CustomerID] ,[SalespersonPersonID] ,[OrderDate] ,[ExpectedDeliveryDate] INTO ##Temp FROM [WideWorldImporters].[Sales].[Orders]') WITH RESULT SETS NONE; EXEC(' SELECT * FROM ##temp') WITH RESULT SETS (( [OrderID] INT , [CustomerID] INT , [SalespersonPersonID] INT , [OrderDate] DATE , [ExpectedDeliveryDate] DATE )); END; DROP TABLE ##Temp;
The package runs successfully now.