Create global temporary stored procedure or global temporary table

Question

Hi, In ADF I am using self-hosted to create linked service.

In SSIS, I know we can create global temporary stored proc & table.

I want to know can we do similar to that in ADF too.

When a pipeline executes, it needs to create global temporary stored proc & table.

I want to use this global temporary table for other activities under same pipeline.
If i run the code under script activity it executes but i am not getting the temp table if i use

another activity like copy in the same pipeline using same linked service.

simple code in SSIS:

-- Check if the global temporary stored procedure exists and drop it

IF OBJECT_ID('tempdb..##usp_employee') IS NOT NULL

DROP PROCEDURE ##usp_employee;

GO

-- Create the global temporary stored procedure

CREATE PROCEDURE ##usp_employee (@salary float, @design varchar(100), id int)

AS

BEGIN

-- Create a global temporary table

CREATE TABLE ##employee ( 

    id INT ,

    designation VARCHAR(100),

    salary float

);

END;

GO

Answer 1

@rookie_pookie

Thanks for reaching out to Microsoft Q&A

Creating global temporary stored procedures and tables in Azure Data Factory (ADF) is a bit different from SSIS. ADF does not maintain session state between activities, which means that temporary tables created in one activity are not accessible in subsequent activities within the same pipeline

However, you can use a workaround by creating a stored procedure that includes the creation of a global temporary table. Here’s how you can do it:

1. Create a Stored Procedure: Write a stored procedure that creates a global temporary table.
2. Execute the Stored Procedure: Use the stored procedure in your ADF pipeline.

Here’s an example of how you can achieve this:

-- Create the global temporary stored procedure
CREATE PROCEDURE ##usp_employee (@salary FLOAT, @design VARCHAR(100), @id INT)
AS
BEGIN
    -- Create a global temporary table
    CREATE TABLE ##employee (
        id INT,
        designation VARCHAR(100),
        salary FLOAT
    );

    -- Insert data into the global temporary table
    INSERT INTO ##employee (id, designation, salary)
    VALUES (@id, @design, @salary);
END;
GO

In your ADF pipeline, you can use the Stored Procedure Activity to call this procedure. However, remember that the global temporary table will only be available as long as the session that created it is active

If you need to use the data across multiple activities, consider using a physical table instead and dropping it at the end of your pipeline

Please refer the below helpful links

Microsoft Q&A on temporary tables in ADF

Microsoft Q&A on temporary table query error in COPY DATA in ADF

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Based on my knowledge , it is not possible. You would have to create a actual physical staging table which you can reuse in future activities if need be.