How to Pass API Response to Azure SQL Table via Stored Procedure

Question

I have a Azure Data Factory pipeline that uploads data to Salesforce and then gets the response back to Azure Data Factory. I am trying to get the response from the failed job and store in my failed records table in Azure SQL. I am using Stored Procedure activity to pass on the values to the table. The issue I am having is that the stored Procedure activity is getting the correct input for response but when it passes the value to SQL it just insert ".

I previously had the failed_records_details column set as Varchar(max) and then tried to change it to NText and see if SQL would accept the string value passes on from the API response. But I haven't had any luck so far. Any help would be really appreciated.

Joseph Xu Joseph Xu · Accepted Answer · 2021-04-21T09:47:24

Update
OP changed his table structure to varchar(max) and it worked.

After many tests, I found the answer. I used OPENJSON to process in stored procedure.

This is my table:

CREATE TABLE [dbo].[product](
    [PRODUCT_ID] [int] NULL,
    [PRODUCT_TYPE] [int] NULL,
    [PRODUCT_NAME] [varchar](50) NULL,
    [PRODUCT_TITLE] [varchar](255) NULL,
    [PRODUCT_PIC] [varchar](255) NULL,
    [CREATE_TIME] [datetime] NULL,
    [UPDATE_TIME] [datetime] NULL,
    [PRODUCT_INTRO] [text] NULL,
    [PRODUCT_FEATURE_ID] [varchar](255) NULL,
    [PRODUCT_PARAM] [varchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

This is my API response:

{
    "productId": 2,
    "productTypeId": 2,
    "productName": "AD2",
    "productTitle": "TopAir AD215-1000A",
    "productPic": "/img/productPic/1593684705859user.jpg",
    "createTime": "2020-06-05 09:17:31",
    "updateTime": "2020-06-05 09:17:31",
    "productFeatureId": "",
    "productParam": "/img/productPic/param/1593685548627a32415ca9a21f6f9a1d99b2731f224b5d319c424.jpg",
    "productIntro": "",
    "productType": null,
...

This is my stored procedure, the api request as a string type input parameter.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspProduct] @product NVARCHAR(MAX)
AS  

BEGIN TRY 
        INSERT INTO dbo.product(PRODUCT_ID,PRODUCT_TYPE,PRODUCT_NAME,PRODUCT_TITLE,PRODUCT_PIC,CREATE_TIME,UPDATE_TIME,PRODUCT_INTRO,PRODUCT_FEATURE_ID,PRODUCT_PARAM)
                SELECT * FROM OPENJSON(@product)
                    WITH(
                        PRODUCT_ID int '$.productId',
                        PRODUCT_TYPE int '$.productTypeId',
                        PRODUCT_NAME varchar(50) '$.productName',
                        PRODUCT_TITLE varchar(255) '$.productTitle',
                        PRODUCT_PIC varchar(255) '$.productPic',
                        CREATE_TIME datetime '$.createTime',
                        UPDATE_TIME datetime '$.updateTime',
                        PRODUCT_INTRO varchar(255) '$.productIntro',
                        PRODUCT_FEATURE_ID varchar(255) '$.productFeatureId',
                        PRODUCT_PARAM varchar(255) '$.productParam'

                    )
END TRY  
BEGIN CATCH 
    PRINT ERROR_MESSAGE ( )   
END CATCH
;

Add dynamic content @string(activity('Web1').output). After repeated testing, here we must convert API response from object type to string type.
This is the input of the Stored procedure1 activity.
The debugging results are as follows:

I can see the API response was inserted into the table.

How to Pass API Response to Azure SQL Table via Stored Procedure

1 Answers