This is a fairly common issue with SSIS (at least in versions before SSIS 2012 - I haven't yet tested this on 2012). When using a stored proc as a data source, if that sproc uses temp tables at all, chances are you're going to have some issues using it in SSIS. To make a long story short, SSIS will try to validate the upstream data prior to execution, and if the temp table doesn't yet exist, that validation will often fail.

There are a few workarounds to this, none of which are very pretty. The easiest solution is usually to replace the temp table with a physical staging table (one that is persisted in the database), and just trunc/reload this staging table in the sproc.

If you can't change the sproc, you might consider executing the sproc in an Execute SQL Task and load those results into a staging table directly by doing an INSERT INTO <staging table> EXEC <sproc name>.

Hope this helps...