I’m currently working on a data migration task using Azure Data Factory (ADF) and encountering an issue with handling an output parameter that is also an identity field in a SQL Server stored procedure.

Objective:

Migrate data from a JSON file stored in Azure Blob Storage to a SQL Server table using a stored procedure.

Context:

Source: JSON file stored in Azure Blob Storage.

ADF Activities:Lookup1 Activity:

Successfully fetches data from the JSON file.

Stored Procedure Activity: Uses a stored procedure ([dbo].[Trip]) to insert data into the SQL Server table.

Stored Procedure Details:The stored procedure inserts records into the table and generates an ID field as an identity column.

The ID field is also defined as an output parameter in the stored procedure.

Issue: The Stored Procedure activity fails due to the output parameter.

How can I handle the output parameter (which is also an identity field) in the stored procedure when using Azure Data Factory? Is there a workaround to successfully execute the stored procedure and retrieve the generated identity value?

Any insights or suggestions would be greatly appreciated.

Thank you!

Tried lookup activity, Stored procedure activity, copy data activity without luck