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
BEGIN
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
END

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

Select e.Employee,

EmployeeName,

t.salary

from employee e

join  #temp t on t.employeeid=e.employeeid

I added a block to the begining of my query like this

IF 1=2

BEGIN

Select Employee

,EmployeeName

,Salary = convert(decimal(10,2),1.00)

From employee e

END

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.