Azure Data Factory - On premise integration runtime for Multiple on-premise db servers.

Gabri 30 Reputation points
2023-03-22T10:36:10.3433333+00:00

Hi.

I have the following scenarios:

I have a pipeline that consumes data from an on-premises SQL server through an Integration runtime installed on that server. So far, so good.

The thing is that the company has n system with the same database schema in different on-premise servers, and I want to re-use the same pipelines, datasets, and dataflows.

I know I can parametrize the linked service but those servers are in different networks => I can't reuse the same integration runtime.

My initial Idea was to create an integration run-time by the on-premise server (this is done), but now I face the problem that I can't dynamically tell the pipeline which integration runtime to use.

I can't use the approach of replacing it in the pipelines, because those are **not different environments. (**In fact, I have it in place for my UAT, and PROD environment.)

I could achieve this if I make the on-premise servers visible within the on-premise infrastructure and then yes, make parametrize the linked server, but I don't like the idea of forcing on-premise architecture (related to all the servers) because of that issue.

Is there any idea? Thank you in advance!

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,532 questions
0 comments
Accepted answer
  1. ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee
    2023-03-23T06:21:26.7266667+00:00

    Hi Gabri,

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

    Dynamically passing IR to linked service or dynamically passing linked service to Dataset is not possible at this moment. Hence, if same IR can access both the environments then you can achieve it. But as you rightly said it demands your existing environments architecture to change. If you are not okay to do this, then only way is creating another set of linked services and clone existing pipelines & datasets and use these new linked services there. That way, you are creating whole set of pipelines pointing to another environment.

    I passed this feedback of allowing dynamic IR selection or linked service selection in datasets to Internal team. I would also encourage you to have a feedback item for same in below portal. Product teams consider these feedback items in to feature road map.

    https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c?query=hubspot

    Hope this helps. Please let me know if any further queries.

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

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 15,216 Reputation points
    2023-03-22T12:54:14.0366667+00:00

    In your case, you may need to use the dynamic mapping feature of Azure Data Factory.

    Try to create a new dataset for your source, and in the Integration Runtime tab, select AutoResolveIntegrationRuntime

    where you need to add a new Set Variable activity.

    Then in the source of the dataset, under the connection tab, select the integration runtime and use the following dynamic expression @variables('integrationRuntimeName')

    To follow the best practices, you need to have a separate integration runtime for each on-premises server you want to connect to. However, the pipeline, dataset, and dataflow definitions across all of these integration runtimes by using dynamic mapping can be reused from an environment to another.