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.
Comments