Creating an SSIS package that uses a stored procedure

We're trying to make an SSIS package where it'll launch a stored procedure and capture the contents in a flat file. This will have to run every night, and the new file should overwrite the existing file.

This wouldn't normally be a problem, as we just plug in the query and it runs, but this time everything was complicated enough that we chose to approach it with a stored procedure employing temporary tables. How can I go about using this in a DTS package? I tried going the normal route with the Wizard and then plugging in EXEC BlahBlah.dbo... It did not care for that:


The Statement could not be parsed. Additional information: Invalid
object name '#DestinyDistHS'. (Microsoft SQL Server Native Client
10.0)

Can anyone guide me in the right direction here?

Thanks.

If you are calling the temp table from a different task try this

https://stackoverflow.com/questions/5631010/how-to-create-a-temporary-table-in-ssis-control-flow-task-and-then-use-it-in-dat

StackExchange Database Administrators Q#64989, answer score: 2