locked
Parameterized Linked Service RRS feed

  • Question

  • Hi All,

    I'm trying to parameter my Sink Linked Service. I know how to do it (see 2nd pic below) by individual parameters. However, does anyone know how to use a parameterized connection string also? You can see mine in the 2nd pic however I would rather send this in than have to parse it every time in my ADF Pipeline.

    Thanks for any info that you can provide.

    Mike

    Mike Kiser


    Mike Kiser


    • Edited by EMKISER Tuesday, August 27, 2019 3:46 PM
    Tuesday, August 27, 2019 3:24 PM

Answers

  • Hi Mike,

    In this case, you would just have to specify something similar to the following for the connection string :

    { "name": "AzureSqlDatabase", "properties": { "type": "AzureSqlDatabase", "typeProperties": { "connectionString": { "value": "@{linkedService().ConnectionString}", "type": "SecureString" } }, "connectVia": null, "parameters": { "ConnectionString": { "type": "String" } } } }

    Hope this helps.

    Wednesday, August 28, 2019 9:51 AM
  • Hi Mike,

    To parse your connection string, you can do the following :

    • Set a variable and assign it the value of the whole connection string (let's call this variable "conn").
    • Chain a set variable activity to the first set variable activity. In this set variable activity, set an "Array" type variable (let's call it "splitConn" and assign it @split(variables('conn'), ';').
    • You will get the following value stored in splitConn :

      [ "Server=tcp:sfzwns5gla.database.windows.net", "Database=SVCutterOil", "user id=shawncutter", "password=*****", "Persist Security Info=False", "Asynchronous Processing=True", "\"" ]
    • You now have everything stored in this form and you can optionally assign it to different variables since the structure of this array would always remain constant.
    • Eg- To save the value of database name into a variable "Db", you would use the expression @split(variables('q')[1], '=')[1].

    Hope this helps.

    Thursday, August 29, 2019 6:40 AM
  • Also, to parameterize the linked service from the UI, you can use "add dynamic content" in the desired fields. A good example is explained in the doc.

    Hope this helps.

    Thursday, August 29, 2019 6:43 AM
  • Glad to be of help Mike. Let me know if you face any problems with any other aspects of your pipeline.

    Feel free to also ask questions on Azure Key Vault linked service. 

    • Marked as answer by EMKISER Sunday, September 1, 2019 11:08 PM
    Friday, August 30, 2019 7:52 AM

All replies

  • Hi Mike,

    In this case, you would just have to specify something similar to the following for the connection string :

    { "name": "AzureSqlDatabase", "properties": { "type": "AzureSqlDatabase", "typeProperties": { "connectionString": { "value": "@{linkedService().ConnectionString}", "type": "SecureString" } }, "connectVia": null, "parameters": { "ConnectionString": { "type": "String" } } } }

    Hope this helps.

    Wednesday, August 28, 2019 9:51 AM
  • Hi Chirag,

    Thanks for your reply. so I have to parse my connection string then? How can I do what you have in json above through the UI?

    I also guess I could use a @split to split the Parameters up, but I can't find a good example for that.

    Thanks again

    Mike


    Mike Kiser


    • Edited by EMKISER Wednesday, August 28, 2019 7:05 PM
    Wednesday, August 28, 2019 4:27 PM
  • Hi Mike,

    To parse your connection string, you can do the following :

    • Set a variable and assign it the value of the whole connection string (let's call this variable "conn").
    • Chain a set variable activity to the first set variable activity. In this set variable activity, set an "Array" type variable (let's call it "splitConn" and assign it @split(variables('conn'), ';').
    • You will get the following value stored in splitConn :

      [ "Server=tcp:sfzwns5gla.database.windows.net", "Database=SVCutterOil", "user id=shawncutter", "password=*****", "Persist Security Info=False", "Asynchronous Processing=True", "\"" ]
    • You now have everything stored in this form and you can optionally assign it to different variables since the structure of this array would always remain constant.
    • Eg- To save the value of database name into a variable "Db", you would use the expression @split(variables('q')[1], '=')[1].

    Hope this helps.

    Thursday, August 29, 2019 6:40 AM
  • Also, to parameterize the linked service from the UI, you can use "add dynamic content" in the desired fields. A good example is explained in the doc.

    Hope this helps.

    Thursday, August 29, 2019 6:43 AM
  • Wow, thanks Chirag! That is exactly what I have been struggling with. You are awesome!! I am going to start work on the ConnectionString as you laid out in your message!

    Thanks so much! I'll let you know how it goes. My pipeline is fairly complex and this is the only remaining piece for it to work end to end.

    The only enhancement after that is to store the connection string in the Key Vault. The Lookup I am getting to get the ConnectionString for a particular customer is taking way too long. I am hoping storing it in the Key Vault will speed the processing up by not having to call a stored proc in the pipeline to get my ConnectionString. I may have some questions on storing the ConnectionString in the Key Vault and accessing it in my parameterized Linked Service.

    Thanks again for all of your help! It is greatly appreciated.

    Mike Kiser


    Mike Kiser

    Thursday, August 29, 2019 2:29 PM
  • Glad to be of help Mike. Let me know if you face any problems with any other aspects of your pipeline.

    Feel free to also ask questions on Azure Key Vault linked service. 

    • Marked as answer by EMKISER Sunday, September 1, 2019 11:08 PM
    Friday, August 30, 2019 7:52 AM
  • Thanks so much for the superb knowledge! My next and LAST challenge is to put the Connection Strings in the Key Vault and access them and then split them like we did above. Then my ADF Application is ready for final QA and Testing!!

    Thanks again!
    Mike


    Mike Kiser

    Sunday, September 1, 2019 11:10 PM