• SSIS, SSRS, and many other tools are going to have some trouble with these. The problem is that SSIS does not actually execute the procedure when it is trying to get the meta-data, so the temp table does not actually exist to return the column information. This happens a lot with stored procedures.

    I typically handle this in one of two ways. The first is to use real tables rather than temp tables. This can cause some issues, but depending on the process you are trying to manage can sometimes be of benefit.

    The second option is to use a stored procedure and temp tables, but during development create a procedure stub that does nothing but select the column definitions. This usually looks like:

    [font="Courier New"]CREATE PROCEDURE MySSISProc

    @MyDate DATETIME

    AS

    SELECT CONVERT(INT,NULL) AS SalesOrderID

    , CONVERT(VARCHAR(20),NULL) AS CompanyName

    , CONVERT(DATETIME,NULL) AS OrderDate

    /*

    All other logic commented out

    */[/font]

    This will allow you to work with the meta-data. Then after deploying the package you can go put the actual procedure logic in. This gets risky because if your procedure logic has a little different meta-data, the SSIS package will error.