0

I have created a stored procedure that creates multiple temp tables for use in the sproc and then drops them at the end of the sproc. The problem I am encountering is I can execute the stored procedure just fine in management studio, but when I attempt to create an SSIS data flow that retrieves the rows returned by the sproc, it give me the following error:

Invalid object name '#tempRuns'

Is there something special I need to do in order for temp tables to work with SSIS in Azure?

| improve this question | |
  • The problem is you can run #tables in SSIS without doing a little extra work. – Zane Dec 5 '18 at 15:18
  • You can bypass this by using a table variable however I don't recommend doing that since the performance will be poor since table variables produce static estimated rows. – Zane Dec 5 '18 at 15:26
1

To do this you will need to use global temp tables, i.e. ##TempTable. Additionally, the TransactionOption property will need to be set to Required for the the parent container of all the objects involved. This will keep all of the SSIS components in the same transaction. Within the container, the tasks can use use the same Required level or Supported, which will inherit this property from the parent container. All the components involved can either be placed within a Sequence container with TransactionOption set accordingly to isolate them, or this can be applied at the package level.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.