ADF SQL Connection string error in stored procedure activity

RoelV 26 Reputation points
2022-04-08T06:44:12.553+00:00

Hi,

I have an ADF pipeline that fails on a stored procedure activity. Error:

"Cannot create Sql Source. Please double check the connection string, stored procedure are set with correct format. Error: The connection string provided is invalid."
191291-image.png

I try to call a stored procedure in a Azure SQL database. All stored procedure calls are failing. It works fine on the Development environment, only after being deployed on the Acceptance environment it fails there.
The stored procedures called are in the same linked service as all other database activities, like source/sinks in a dataflow, which are working fine.
In the Stored Procedure activity I can only specify the linked service, and I selected the stored procedure from the dropdown.
191273-image.png

Any ideas?

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,525 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee
    2022-04-08T09:51:45.057+00:00

    Hi @RoelV ,

    Thank you for posting query in Microsoft Q&A Platform.

    As per my understanding you are getting mentioned error with stored procedure activity. Please correct me if I am wrong.

    This kind of errors when user does something wrong with stored procedure parameters while calling stored procedure from ADF.

    Below are few possible misses user may do, which result in this kind of error. Kindly recheck them.

    • User may miss to add parameter names and values inside store procedure activity, which has to supply to store procedure.
    • User may added parameters and values inside Stored procedure activity, but either value or data type selected wrongly.

    Similar issue along with resolution mentioned at below link. Kindly check it once.
    https://www.tech-findings.com/2021/08/Cannot-create-Sql-Source-The-value-of-the-property-Value-is-invalid-for-the-stored-procedure-parameter-XXX.html

    Hope this helps. Please let us now if any further queries.

    -------------

    Please consider hitting Accept Answer. Accepted answers help community as well.

  2. roelv 6 Reputation points
    2022-04-13T11:06:34.187+00:00

    Hi @ShaikMaheer-MSFT

    I already solved it. I was using the wrong connect string format.

    I switched this connection string on ADF deployment from:

    "Server=XXXX.database.windows.net; Authentication=Active Directory Managed Identity; Database=ICVRDB"

    To:
    "Data Source=tcp:XXXX.database.windows.net,1433;Initial Catalog=ICVRDB;Connection Timeout=30"

    ...and it worked. The first format worked fine for using the database linked service in dataflows, but not in Stored Procedure and Copy Data activities.

    Thanks for your help.