Question 104738 in Azure Question posted in
The official documentation to get you started can be found here.

Azure Data Factory – Null parameter in Stored Procedure activity

I have a Stored Procedure activity in my Azure Data Factory pipeline that recalls the following stored procedure on Azure Synapse:

CREATE PROCEDURE schema.procedure_name @portafoglio INT [...]

The activity in configured in order to use a variable (portafoglioElab), previously set in the pipeline:

enter image description here

My problem is how to pass a Null value as input parameter to the procedure via the Azure Data Factory Pipeline in the Store Procedure activity.
I’ve tried by setting the variable portafoglioElab with null, "null", None, @coalesce(null) but I’m not finding the right value corresponding to sql NULL.

This is the step of the pipeline where I set the variable:
enter image description here

It is a step inside the IF condition of the whole pipeline:
enter image description here

Indeed I’m getting this error:

Cannot create Sql Source. Please double check the connection string, stored procedure are set with correct format. Error: The value of the property ‘Value’ is invalid for the stored procedure parameter ‘portafoglio’.

Can please someone help me?
Thanks in advance

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I've found a solutions with the dynamic content.

    It is not only possible to put dynamic content in the value of the parameter, but even the parameter itself cam be dynamic.

    enter image description here

    I solved after many tests with this value:

    @json(concat('{"portafoglio": {"value": ', variables('portafoglioElab'), '}}'))

    In this way I can handle null and not null values. Pay attention that the documentation is wrong, the content has to be a dictionary, not a string.


  2. 0

    Pass -1 or some other sentinel and handle it in the proc

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search