Tuesday, August 24, 2021

Cannot create Sql Source. Error: The value of the property 'Value' is invalid for the stored procedure parameter 'XXX'


Error/Issue:

Today got below error while Debug run of newly developed pipeline, in which at the end am logging audit details in a table via Stored procedure


The value of property is invalid for stored procedure


                             


Why it happened


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 'OperationsProcessed'




As stated in error message(highlighted in red above) the error says the value passed against 'OperationsProcessed ' parameter is incorrect

It was surprising as this parameter is defined as bit  in stored procedure so the value can be either 0 or 1 and upon rechecking the value - I see nothing wrong  i.e. 0 (zero)

bit to boolean in stored procedure parameter


When checked the datatype, there was no bit instead it was Boolean, it is implicitly done by ADF  - and this is the reason for the error .

What to do



So to fix the error, we need to provide valid Boolean values i.e. either true or false.

That's it, provided false as value instead of 0(zero) and that made the error go away.

You may ask, what value do you see in Table(stored procedure inserts in audit table)? 

-- It is 0 and not false 

So I assume, ADF implicitly handles this type conversion.

No comments:

Post a Comment

If you have any suggestions or questions or want to share something then please drop a comment