Many times while working with SSIS I have had issues where I would like to add a temp table to a stored procedure that is a source for my SSIS dataflow. One work around for this is create a temp table variable instead. The issue with this is if many rows are in this temp variable the performance can be severely degraded. So the other day I was searching for workarounds and found this link http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65112/.
Here Michael Cape gives a Wacky workaround which I actually really like! The Idea is to add a code snippet to the begining of your proc specify the proc output columns and data types.
Here is his example:
IF 1 = 2
SELECT CAST(NULL AS INT) AS ContactID,CAST(NULL AS NVARCHAR(50)) AS FirstName,
CAST(NULL AS NVARCHAR(50)) AS MiddleName, CAST(NULL AS NVARCHAR(50)) AS LastName,
CAST(NULL AS NVARCHAR(10)) AS Suffix, CAST(NULL AS NVARCHAR(50)) AS EmailAddress
I modified in my proc to actually be the select form the real base table and eliminated and hard coded the columns actually from my temp table:
So if my real query was like this
from employee e
join #temp t on t.employeeid=e.employeeid
I added a block to the begining of my query like this
,Salary = convert(decimal(10,2),1.00)
From employee e
The difference is that now if I change the EmployeeName column datatype to add more characters in the employee table I will not have to modify the proc for this to populate. Obviously if I need to change the temp table I will be in this proc anyway and will just need to replicate this "Hard Coded" column in here. Other than that this logic works like a champ.