How to Pass JSON Data from Azure Blob to On-Prem SQL Server Stored Procedure and Capture Error Messages?

Rishikesh S Darwade 0

There is a requirement, where there is an Stored Procedure in On Prem SQL Server which return a table with errors/successfully processed records.

The SP is inserting the records provided to it via table type parameter.

I am having the data in Azure blob storage as JSON file and want to pass that to the Stored Procedure as table type parameter. I am currently doing this using Copy Data Activity but problem is in case of any errors, which SP returns, I am unable to grab those error messages and log those errors.

Could anyone suggest a solution for this scenario using Azure Data Factory (ADF) only, as I want to avoid involving any other dependencies?

Source :
User's image

SP :

User's image

J N S S Kasyap 80 Reputation points Microsoft Vendor

2025-02-17T08:54:50.0966667+00:00
Hi @Rishikesh S Darwade

In addition to @Achraf Ben Alaya,
Since you don't have visibility into the stored procedure's code and it returns a table of errors or success records, you need to capture this result and store it somewhere for logging.

Here's the detailed plan:

Execute Stored Procedure:

In ADF, use the Stored Procedure Activity and configure it to call the vendor’s SP.

Make sure you use the table variable (created from the previous Copy Data activity) as the input parameter for the stored procedure.

Capture the Result Table from SP:

If the SP returns a result set with error messages, you need to use an Execute SQL activity to query the result after the SP executes.

We can utilize an SQL query to fetch the results from the table where the stored procedure inserts the data.

Store Errors for Logging: You can use a Stored Procedure Output to store the result of the errors in a logging table in SQL Server or pass the error data to an Azure Blob Storage (or another logging destination) using an additional Copy Data Activity.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
J N S S Kasyap 80 Reputation points Microsoft Vendor

2025-02-18T01:21:05.9266667+00:00

Hi @Rishikesh S Darwade
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Rishikesh S Darwade 0

Hi J N S S Kasyap,

I am not using any variable in Copy Data Activity, I am not clear on what needs to be passed to the SP since the input is in json file at blob storage. Can you please guide with step by step process.

Also, I figured out that one of the Function App is calling that SP as below:
If there is any error, it returns the table.

{
                        command.CommandType = CommandType.StoredProcedure;

                        SqlParameter parameter = command.Parameters.AddWithValue(SP_Parameter_Name, dt);
                        parameter.SqlDbType = SqlDbType.Structured;
                        parameter.TypeName = SP_Parameter_Type;

                        sqlConnection.Open();

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.HasRows) // -> if there are any errors!
                            {
                                result.ErrTable.Load(reader); // Load reader to Error Table
                                result.Status = "Failed";
                            }
                            else
                                result.Status = "Success";

                        }
                    }
                }

J N S S Kasyap 80 Reputation points Microsoft Vendor

2025-02-19T02:16:44.38+00:00

Hi Rishikesh S Darwade

The structure of the table-valued parameter (TVP) passed from ADF through the Copy Data Activity must align with what the stored procedure expects. If there are any discrepancies in column names or data types between the TVP and the stored procedure, it could cause the procedure to fail silently or result in incorrect data processing.

Steps to achieve Pass JSON Data from Azure Blob to On-Prem SQL Server Stored Procedure and Capture Error Messages:
1.Use the Copy Data Activity to load data from a JSON file in Azure Blob Storage into a SQL Server staging table, ensuring the fields are mapped correctly to the corresponding columns.

2.The data from the JSON file in Blob Storage is now available in the SQL Server staging table, ready for further processing.

3.You can execute the vendor's stored procedure using the Stored Procedure Activity, passing a table-valued parameter (TVP) derived from the SQL Server staging table. The stored procedure will process the data and log any errors into an ErrorLogTable . The ErrorLogTable logs errors that occur during the stored procedure execution.

4.The Lookup Activity can be used to query the ErrorLogTable for unprocessed error records.It captures the logged errors, allowing you to handle or log them further as required.

I hope this information helps. Please do let us know if you have any further queries.
J N S S Kasyap 80 Reputation points Microsoft Vendor

2025-02-20T01:22:18.7766667+00:00

@Rishikesh S Darwade
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Achraf Ben Alaya 1,226 Reputation points MVP

2025-02-16T12:46:45.43+00:00

Hi ,

you can :

1- Capture error information by modifying your procedure to return error details ,liek a status or error message , you can use it as output

2-Log Errors in ADF : use ADF output Dataset to capture the results , you can use lookup activity or stored procedure acitivity to capture the results .

3- in your pipline you can use copy data acitivity to load the data from the blob into a temporary staging table ,once it's in the staging table you can pass it to the stored procedure as a table-valued paramter .

If the reply is helpful, please click Accept Answer and kindly upvote it. If you have additional questions about this answer, please click Comment.
Please sign in to rate this answer.
Rishikesh S Darwade 0 Reputation points

2025-02-17T04:12:20.5033333+00:00

The SP is not managed by us, its a Vendor's SP. We don't have visibility to its definition as well.

Can you elaborate with steps how can we capture result from the SP after passing the TableType Parameter?

Rishikesh S Darwade 0 Reputation points

2025-02-17T04:14:17.31+00:00

Hi @Achraf Ben Alaya
The SP is not managed by us, it's a Vendor's SP. We don't have visibility to its definition as well.

Can you elaborate with steps how can we capture result from the SP after passing the TableType Parameter?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to Pass JSON Data from Azure Blob to On-Prem SQL Server Stored Procedure and Capture Error Messages?

1 answer

Your answer