How to create temp tables in SQL to be used in several ADF activities?

1
votes

I need to create a global temp table in my SQL Server while executing an Azure Data Factory pipeline. This table will be used in several activities.

I already tried several approaches including one using the Stored Procedure activity targeting the sys.sp_executesql SP and the CREATE TABLE statement as the parameter. With this approach the table is actually created, but it's automaticaly dropped a second later, I don't understand why.

This is the script used to create the temp table:

CREATE TABLE ##tempGL
(
    GLAccount NVARCHAR(15),
    GLSubAccount NVARCHAR(15)
)

So, how can I create a SQL Server temp table from an Azure Data Factory Pipeline activity that persists until I dropped it?

1
sql-serverazure-data-factory-2
Can you post your create table script? – scsimon
A temporary table only persists during the session that it was created in. If you use sp_executesql then it'll be created within the context of the (dynamic?) SQL your running and as soon as that batch is completed, the temporary table will be dropped. You'll need to create the temporary table before you use sp_executesql and then it will persist until the outer session closes instead. db<>fiddle to demonstrate. – Larnu
@scsimon I just updated the question with the script. – Gustavo Vargas
I can't replicate your behaviour there: db<>fiddle. We're missing something here. – Larnu
@Larnu yes, this scenario usually works in SQL Server, but this time, the scenario is different because it's running in an Azure Data Factory pipeline, so there's something there dropping my table after the activity execution. – Gustavo Vargas

1 Answers

3
votes

I have been struggling with this myself. Apparently this is by design (see quote below from Microsoft employee) and it is not possible to achieve this using Azure Data Factory even though the documentation mentions that it is possible.

That is by design. We won’t keep connection between 2 activities. If you use a real table instead of temporary table. Then you will get the expected result. The suggestion is don’t used temporary table in ADF if the data need more than 1 activities to access.

https://github.com/MicrosoftDocs/azure-docs/issues/35449#issuecomment-517451867

The reason this happens is the session is dropped when a pipeline activity ends, which causes the temporary table to also be dropped.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017#temporary-tables

Hopefully Microsoft fixes this at some point and makes it possible to use temporary tables across activities with Azure Data Factory.

I have raised this as a suggestion for Azure here https://feedback.azure.com/forums/270578-data-factory/suggestions/38287108-persist-global-temporary-tables-between-activities

For anyone reading this that might want his feature please upvote that suggestion.