In SSIS, have you ever tried to use a store procedure which uses a temp table to generate its output. When you try to use a procedure like this in SSIS's OLE DB Source data flow source, there won't be any columns listed. Consider this simple stored procedure which outputs some data from AdventureWorks' Contact table. The procedure simply dumps some columns into a temporary table, and then selects rows from the temp table.

CREATE PROC dbo.TestSSISTempTable AS
SET NOCOUNT ON
SELECT ContactID, FirstName, MiddleName, 
LastName, Suffix, EmailAddress

INTO #Contact
FROM Person.Contact

SELECT ContactID, FirstName, MiddleName, 
LastName, Suffix, EmailAddress
FROM #Contact

When I try us this proc in an SSIS data flow I don't get any columns listed, which means I can't complete the mapping of source columns to destination columns.

There are three techniques which can be implemented to work around this issue. One is good (sorta), one is bad, and one is downright wacky.

The Bad

The easiest way to fix this is to add a simple SET statement at the beginning of the stored procedure. Adding the statement SET FMTONLY OFF at the start of the original procedure will allow the column information to come through in the column listing of the OLE DB Source control.

So, what's so bad about this technique? In a word, bad performance.

This was the first technique I tried, when I was working with an SSIS package that was part of a daily job run. The problem I encountered was that my stored procedure didn't perform very well, sometimes running for hours. However, every time I ran the procedure with Query Analyzer it would never take more than 5 minutes. And most times, it ran in less than 1 minute.

As I was researching this, I found the explanation for this in the article "Coping with No Column Names in the SSIS OLEDB Data Source Editor" By Paul Ibison. According to this article this technique will cause the procedure to execute 5 times! Yikes! After, reading this, I too verified this. Indeed, my stored procedure ran 5 times. Well, as you can image this definitely has a negative impact on performance. Furthermore, a more potentially dangerous issue is if the stored procedure did any updating or inserting into a table. Those would be done 5 times!

The Good (Sorta)

The next technique that I tried was to convert the temp table to a table variable. This too worked, although it was a little harder to implement than the first technique. However, it avoided the issue with running the procedure 5 times. However, performance can be an issue with this technique as well. Sometimes, table variable perform as well or better than temp tables. However, sometimes temp tables are better performers. SQL Server doesn't maintain any statistics for table variables. This means that every time they are used in a query they are always table-scanned.
Another problem with this is that apparently all temp tables have to be converted into table variables. Suppose your stored procedure has 3 temp tables, and only one of them provides the output. Simply converting that one temp table to a table variable doesn't seem to work. When I tried implementing this technique, I couldn't get it to work unless I converted all temp tables to table variables.

The Wacky

This last technique is definitely weird. Again, as I was researching this issue, I found Jamie Thomson's blog entry called SSIS: Using stored procedures inside an OLE DB Source component

Here, Jamie states that a stored procedure doesn't have any metadata for the OLE DB provider to retrieve. He further states that the OLE DB provider has to make a "best guess" by taking the metadata of the first select statement in the stored procedure. However, if the stored procedure doesn't have a good query for the OLE DB provider to use then it can't guess. Furthermore, Jamie goes on to reference Adam Machanic's blog entry, Stored procedures are not parameterized views

Here Adam states that stored procedures don't provide an output contract. However,I started to wonder if I couldn't trick the OLE DB provider into inferring an output contract for a stored procedure. The idea is pretty simple. I put a "no-op" select statement at the top of my procedure which structurally mimicked the output of the stored procedure. Therefore, revisiting the previous stored procedure, I added the IF 1 = 2 code block in the following example.

CREATE PROC dbo.TestSSISTempTable AS
SET NOCOUNT ON
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

SELECT ContactID, FirstName, MiddleName, 
LastName, Suffix, EmailAddress

INTO #Contact
FROM Person.Contact

SELECT ContactID, FirstName, MiddleName, 
LastName, Suffix, EmailAddress
FROM #Contact

Sure enough, this worked! There are a few issues you have to keep in mind when using this technique. You have to make sure that every column in the output query is accounted for in the "contract" query. You also have to make sure that the columns have the right data type as the output columns. Any columns which are in the output query and not in the "contract" query won't be available in SSIS. Furthermore, any column in the "contract" query but not in the output query WILL be available in SSIS, but the SSIS package will fail when you run the package, and you have mapped a column which doesn't really exist in the output.

All in all, I like this technique because it doesn't impose the unnecessary overhead of repetitive executions that the SET FMTONLY OFF technique did. I also didn't have to convert all my temp tables to table variables, thereby exposing the procedure to a potential performance bottleneck. Creating the "contract" query required a little more work than simply adding a SET statement, and required a little less work than converting temp tables to table variables. The result was a stored procedure which still performed well and didn't require any reworking of the core components.