locked
Create Dynamic Linked Service in Azure Data Factory V2 RRS feed

  • Question

  • I have a scenario like copying data to blob locations from a Table to Blob locations.

    While reading from Table(differentiated by Regions). I used a LookUp to read Regions and ForEach activity to Push the data to respective blob location. Like below

    Table --> Regions/R1/File.Gz

    Table --> Regions/R2/File.Gz

    Now I have to read the respective Region file from blob and push to different Region specific databases.

    I am not able to embed another copy activity in the same pipeline because the destination database changes based on the Region.

    Is there a provision in ADF V2, to dynamically change the Linked Service or Connect to Linked Service at Run time?

    is there any way where single copy activity can connect multiple sink dataset one by one in same foreach or dynamically change the database connection in the sink of copy activity?

    The idea is to use same Linked Service and change the database name during run time. is it possible?

    Thursday, October 11, 2018 5:46 AM

Answers

All replies

  • Hi Kishore,

    ADF supports linked service parameterization now. 

    You may try parameterizing the database name of the sink database, and using it in the foreach activity.

    Ref doc: Parameterize linked services in Azure Data Factory


    Thursday, October 11, 2018 6:38 AM
  • Hey 

    Thanks for the reply.

    Yes, I have gone through the same article when tried to create new linked service. I am able to do this parameterization and complete my task.

    Thanks again :)

    Thursday, October 11, 2018 8:13 AM
  • Apparently, this parameterization only applies in database linked service, as far as i can tell this option doesn't exist in FTP and some other linked service in which I'm currently having difficulties right now.
    Wednesday, April 3, 2019 11:37 AM
  • How do you pass the value to the variable during the execution?
    Friday, June 14, 2019 7:17 AM
  • Hi Joe,

    As discussed by Bo Xiao, 

    ADF supports linked service parameterization now for the following data stores :

    • Azure SQL Database
    • Azure SQL Data Warehouse
    • SQL Server
    • Oracle
    • Cosmos DB
    • Amazon Redshift
    • MySQL
    • Azure Database for MySQL

    You may try parameterizing the database name of the sink database, and using it in the foreach activity.

    Ref doc: Parameterize linked services in Azure Data Factory 

    Friday, June 14, 2019 8:56 AM
  • Hi Josh,

    As you pointed out, parameterization  is only supported for the following data stores as of now :

    • Azure SQL Database
    • Azure SQL Data Warehouse
    • SQL Server
    • Oracle
    • Cosmos DB
    • Amazon Redshift
    • MySQL
    • Azure Database for MySQL

    Tuesday, June 18, 2019 8:45 AM
  • Hi Chirag Mishra,

          As given in the document here, Data Factory UI in the Azure portal supports only the data stores you have mentioned. But in the same document it is mentioned that  "For all other data stores, you can parameterize the linked service by selecting the Code icon on the Connections tab and using the JSON editor".

          So I think it must be possible. But when I'm trying with mongoDB linked service it is not working. I have created a new thread explaining the issue here.

    Monday, July 22, 2019 4:16 PM
  • Hi Kishore/Joe,

    Sorry for the delayed response. I hope your issue was resolved. If not, you can do the following to achieve the same (I am using a REST connection for example):

    • Create a Linked Service with some static values and save it.
    • In the Linked Services tab, click on the code icon (highlighted) of the Linked Service you just created :
    • Within properties, add an attribute "parameters" in the following form  :
    {
    "name": "RestServiceWithParameters",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
    "type": "RestService",
    "annotations": [],
    "typeProperties": {
    "url": "https://reqres.in/api/users?page=@linkedService.pageNo",
    "enableServerCertificateValidation": false,
    "authenticationType": "Anonymous"
    },
    "parameters": {
    "pageNo": {
    "type": "String"
    }
    }
    }
    }
    • You can also reference this parameter (pageNo) now as @linkedService.pageNo as shown in the above example. 
    • Create a dataset using this connection. You would be asked to pass a value for the Linked Service (pageNo). You can parameterize the dataset too. To do so, you can pass a value to the parameter as shown below :
    • You can add this parameter to the dataset (pageNum) by clicking on the add dynamic content while assigning value to the Linked Service parameter (pageNo). You will see a '+' icon to add a dataset parameter as shown below :
    • Voila, you are done. In your copy activity or any activity, you would now have to just pass a value to the dataset parameter (pageNum) which would pass that value to the Linked Service (pageNo) as shown below :

    Hope this helps.


    Monday, September 30, 2019 9:43 AM
  • Hi Kishore/Joe,

    Just wanted to check - was the above suggestion helpful to you? If yes, please consider upvoting and/or marking it as answer. This would help other community members reading this thread.
    Thursday, October 3, 2019 11:25 AM
  • Hi Kishore/Joe,

    We haven't heard from you in quite some time. Was your issue resolved? If yes, please consider upvoting/marking as answer the above suggestion. This would help other community members.

    Wednesday, October 9, 2019 10:31 AM