Azure SQL Database
An Azure relational database service.
ADF Pipeline
Error
Stored Procedure
JSON Code of Lookup Activity
Parameters of Stored Procedure
Dynamic Content of RunID
Stored Procedure Error
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(9.6, 83%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMR%3C/text%3E%3C/svg%3E)
Madhusudan Reddy Mandala
1
Reputation point
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 'RunID'
Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
{count} votes
-
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator2021-08-16T11:52:27.847+00:00 Hey,
Based on the error message it seems that the RunId which you are passing as parameter to the SP activity is not properly configured.
Can you please share the code and image -
Madhusudan Reddy Mandala 1 Reputation point
2021-08-16T12:11:26.543+00:00 
-
Madhusudan Reddy Mandala 1 Reputation point
2021-08-16T12:14:03.82+00:00 
This
-
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator
2021-08-16T12:57:51.71+00:00 Can you share your overall ADF image? and json code if possible?
It seems that the dynamic code which you have written seems to be the concern :Ideally we either use dynamic SQL and create a conact query or use the other parameters in query as below:
Select * from table where col1 = '@{activity('Act1').output.value[0].x}'
Sign in to comment
3 answers
Sort by: Most helpful
-
-
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator
2021-08-16T13:38:19.697+00:00 -
Madhusudan Reddy Mandala 1 Reputation point
2021-08-16T13:49:50.277+00:00 Still Getting same Error
Error
-
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator
2021-08-17T05:46:22.54+00:00 Hey,
As suggested by Ryan, can you please add that logic in another lookup activity and check for its output?
And pass that into Stored procedure as in input parameter ?But on a side note will suggest to just pass the @activity('lookupactivity').output.firstrow.RunId as SP paremeter and handle the if else scenario within the SP itself rather than handling it as parameter level
Sign in to comment -
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(224.00000000000003, 77%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERA%3C/text%3E%3C/svg%3E)
Ryan Abbey 1,186 Reputation points2021-08-16T20:45:04.4+00:00 I'd suggest putting all that logic in to a "Lookup Activity" stored procedure (not sure if you're doing a stored procedure or select max in that activity) so that the correct ID is returned
PS what if this process is kicked off twice concurrently, they'd both end up with ID 1000?
-
Madhusudan Reddy Mandala 1 Reputation point
2021-08-17T05:26:47.99+00:00 First i have created a table and also created a stored procedure in Azure SQL Database after that created pipeline in that pipeline i added one copy activity and two stored procedures one stored procedure for if copy activity fail then it will store the details of pipeline activity, one stored procedure for if copy activity success then it will store the details of pipeline activity.
I want when first time run the pipeline the RunID column inserted with value is 1000, When Execute pipeline second time the RuID column inserted with values 1001 and whenever the pipeline executed the RunID also increase +1 of the previous RunID value.
Note:- Do not use IDENTITY() in that table
Sign in to comment -
