SSIS Invalid Object Name Temp Table Error – Easily Fixed

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

ssis invalid object name temp table

“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.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

3 Comments

  1. Why do I have to manually create a global temp table on SSMS as opposed to creating it directly from an Execute SQL Task in SSIS? I feel that this defeats the purpose for automation purposes. How do you get SSIS to find the global temp table without having to manually create it on SSMS?

  2. And what if I want to run it daily from a SQL job? Will it work normally then or do I have to create a global temp manually each time?

  3. I’ve managed to figure this out. SSIS terminates connection whenever it executes each task sequentially. To resolve this, on your connection manager properties, set RetainSameConnection to true. Then set DelayValidation to true on your connection manager properties as well as your data flow task properties. This will enable SSIS to use the same connection and therefore be able to query your global temp table and you won’t have to manually create a global temp table on SSMS.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.