Handling Output Parameter for Identity Field in Stored Procedure with ADF

Anonymous — Mon, 29 Jul 2024 15:06:47 GMT

Hi everyone,

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!

Re: Handling Output Parameter for Identity Field in Stored Procedure with ADF

NandanHegde — Mon, 29 Jul 2024 16:13:32 GMT

You can use Lookup activity or script activity to execute a Stored procedure with an output value.

SP activity is only for SP execution with no output value

topic Handling Output Parameter for Identity Field in Stored Procedure with ADF in Pipelines

Handling Output Parameter for Identity Field in Stored Procedure with ADF

Re: Handling Output Parameter for Identity Field in Stored Procedure with ADF