I came across the problem of using temp tables in stored procedures and selecting a result set from the temp table to return in SSIS 2012. I think it is very common using temp tables in stored procedures and to know that SQL server 2012 does not provide the same support for temp tables was a little bit frustrating for me first time. I couldn’t simply use the old procedures because they had temp tables. I did research only to know that microsoft has almost forgotten about this piece but there are work around. Let me start with an example. Assume I have a stored procedure created like below
Using 2smart4schooldb
GO
Create Procedure dbo.GetStudentInfo(@StudentID INT)
AS
BEGIN
IF OBJECT_ID (‘Tempdb..#Std’) IS NOT NULL
DROP TABLE #Std
CREATE TABLE #Std(FirstName VARCHAR(20), LastName VARCHAR(20), StudentID INT)
INSERT INTO #Std
SELECT FirstName, LastName FROM dbo.Student
Where StudentID = @StudentID
SELECT FirstName, LastName @StudentID As StudentID FROM #Std
END
In SSIS 2005 & 2008, you can easily call this stored procedure with statement EXEC dbo.GetStudentInfo ? and pass the studentid variable to get information for a certain student. But in 2012, this will fail and give you error saying it can not find the metadata because you used temp table in the stored procedure. This problem is due to the fact that SSIS 2012 executes a stored procedure to find the metadata information of the result set to be returned and this procedure does not have support for temp tables, weird.
So in other to avoid this problem, you have to perform some dirty tricks. The one l like is specifying the metadata information in SISS of the result set when calling the stored procedure. So for our example above, l will call the stored procedure like below
EXEC dbo.GetStudentInfo ? WITH RESULT SETS (
(FirstName VARCHAR(20), LastName VARCHAR(20), StudentID INT)
)
This saves me headache faster and easier than other workarounds so l will recommend it to anyone who came across this problem during upgrade to SQL server 2012. I believe SQL server 2014 has addressed this issue already but I’m not using sql 2014 in my development work yet.
I hope this helps someone.
