Issue with UPSERT Logic in Stored Procedure Execution in Fabric Pipeline

[KSB1234]

Hi,

I am implementing UPSERT logic in a Stored Procedure (SP) within a Fabric pipeline using the SP Activity. However, when the pipeline runs, the SP does not populate the correct values, whereas executing it manually produces the expected results. I need a solution to resolve this issue.

Note: I also tried using the Script Activity for SP execution, but it did not yield the correct result either.

Thanks,
Kavya Bhat

. 

[v-venuppu]

Hi @KSB1234 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @KSB1234 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @KSB1234 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Regards,

Rama U.

[AthmakuriRekha]

Hi @KSB1234 ,

When implementing UPSERT logic in a Stored Procedure (SP) within a Fabric pipeline, failing to pass the defined input parameters from the pipeline can lead to issues. If the parameters are not provided and the UPSERT script does not follow the necessary logic, it may fail to insert data into the destination tables.

Hope this asnwers your question.

Thanks!!

[KSB1234]

I am not getting any error , while i am using stored Procedure activity and i provided the stored procedure name , even though that sp activity not update and insert propery when the pipeline was sheduled

Thanks 

Kavya Bhat

[ahmetyilmaz]

Are your Store Procedure Parameters correct?
Example below works correctly for me.

CREATE PROCEDURE UpsertSales

    @SalesOrderNumber NVARCHAR(50),

    @SalesOrderLineNumber INT,

    @OrderDate DATE,

    @CustomerName NVARCHAR(255),

    @EmailAddress NVARCHAR(255),

    @Item NVARCHAR(255),

    @Quantity INT,

    @UnitPrice DECIMAL(18,2),

    @TaxAmount DECIMAL(18,2)

AS

BEGIN

    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM sales

               WHERE SalesOrderNumber = @SalesOrderNumber

                 AND SalesOrderLineNumber = @SalesOrderLineNumber)

    BEGIN

        UPDATE sales

        SET OrderDate = @OrderDate,

            CustomerName = @CustomerName,

            EmailAddress = @EmailAddress,

            Item = @Item,

            Quantity = @Quantity,

            UnitPrice = @UnitPrice,

            TaxAmount = @TaxAmount

        WHERE SalesOrderNumber = @SalesOrderNumber

          AND SalesOrderLineNumber = @SalesOrderLineNumber;

    END

    ELSE

    BEGIN

        INSERT INTO sales (SalesOrderNumber, SalesOrderLineNumber, OrderDate, CustomerName, EmailAddress, Item, Quantity, UnitPrice, TaxAmount)

        VALUES (@SalesOrderNumber, @SalesOrderLineNumber, @OrderDate, @CustomerName, @EmailAddress, @Item, @Quantity, @UnitPrice, @TaxAmount);

    END

END;

[ahmetyilmaz]

Hello, can you share the error you get when you run the pipeline to help? Are you sure you have set the store procedure parameter settings correctly in the pipeline? Can you share the input and output of the store procedure after the pipeline runs?