question

MadhusudanReddyMandala-4641 avatar image
0 Votes"
MadhusudanReddyMandala-4641 asked NandanHegde-7720 commented

Stored Procedure 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 'RunID'


azure-data-factoryazure-sql-database
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

123575-logic.png


123550-e1.png


0 Votes 0 ·
logic.png (9.4 KiB)
e1.png (19.8 KiB)

![123604-1111.png][1]

This
[1]: /answers/storage/attachments/123604-1111.png

0 Votes 0 ·
1111.png (46.3 KiB)
Show more comments
MadhusudanReddyMandala-4641 avatar image
0 Votes"
MadhusudanReddyMandala-4641 answered

ADF Pipeline

123587-adf-pipeline.png


Error

123610-adf-pipeline-2.png


Stored Procedure

123635-adf-pipeline-25.png


JSON Code of Lookup Activity

123565-json-of-lookup-activity.png

Parameters of Stored Procedure

123489-adf-pipeline-3.png


Dynamic Content of RunID

123627-dynamic-parameter-of-runid.png



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered NandanHegde-7720 commented

Hey,

PFB the sample code:
Declare @Test varchar(255)
Set @Test= '@{pipeline().DataFactory}'
Select @Test


123641-issue.png

Add the value in quotes as Ids are Guid values and close the parameter with } .
In case if your RunId is some internal int value no need to add it in quotes, just terminate the bracket



issue.png (36.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Still Getting same Error


123628-capture.png


Error


123589-error.png


0 Votes 0 ·
capture.png (50.1 KiB)
error.png (53.6 KiB)
NandanHegde-7720 avatar image NandanHegde-7720 MadhusudanReddyMandala-4641 ·

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

0 Votes 0 ·
RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 answered MadhusudanReddyMandala-4641 commented

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·