Andy Brownsword disappears in a flash:
When handing data we can make use of temporary tables to aid with separation or performance. However, they don’t always play nice with Integration Services packages.
If we set a source to call a procedure returning the contents of a temporary table we’ll see an error like below:
Read on for three options. It’s been a while, but I vaguely recall that you can use global temp tables (such as ##Results) and it will work, as those persist and are available to all sessions so long as there is some open session using them.