Azure Data Factory - Save output of stored procedure activity to table

You can do this in ADF, but not directly within the Stored Procedure activity itself. The Stored Procedure activity is mainly for executing stored procedures in your database. It can take input parameters and capture output parameters or a return value, but it does not natively capture the full result set from the procedure into a dataset or table. If your goal is to persist the result set into another Azure SQL Database table, there are a couple of options to consider:

Option 1: Modify the stored procedure to insert its results directly into the target table. For example:

CREATE PROCEDURE dbo.CopyData
AS
BEGIN
    INSERT INTO TargetTable (Col1, Col2, Col3)
    SELECT Col1, Col2, Col3
    FROM SourceTable;
END

Then you just call it via the Stored Procedure activity — the data ends up in your table as part of the proc execution.

Option 2: Use a Copy Activity after stored procedure - if you want ADF to control the flow:

1. Stored Procedure activity can prepare the data (e.g., populate a staging table).
2. Copy Activity can then read from that staging table (or a view that selects from the stored procedure output) and copy into your destination table.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin