Are you having problems using #temp tables with SSIS?
Using a stored procedure that accesses a temp table in a dataflow for SSIS is not as straight forward as it should be.
Because the table does not exist at runtime the data source container will throw an error like the one below.
Example of SSIS Invalid Object Name Temp Table Error
“Invalid object name ‘#temp'”
To fix this annoying error you will need to make SSIS “think” that the temporary table is there, and by doing a couple of easy things you can get this sorted.
1./Use a global temp table in your stored proc e.g ## instead of #. Alter and execute to update your stored proc
2./Manually create the global temp table in your SSMS and keep the connection connected.
3./Open and save the data flow task.
Now that SSIS can recognize the presence of the temp table the error will disappear.
That’s it. Your SSIS invalid object name temp table issue is resolved – easily.