SSIS Lift And Shift to ADF - Connectivity

Evan S 5 Reputation points
2023-01-17T16:58:41.7633333+00:00

I'm trying to migrate an SSIS job that transfers data from a linked server to a database on-prem through Azure Data Factory. I have had numerous authentication issues surrounding connecting to the on-prem SQL server and have gone through the documentation 100's of times, still no luck. Do I have to migrate the data to Azure or can I still keep the databases on-prem and just execute the SSIS job from ADF to run the job on-prem.

TIA

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,234 questions
{count} votes

1 answer

Sort by: Most helpful
  1. AnnuKumari-MSFT 34,011 Reputation points Microsoft Employee
    2023-01-18T10:22:19.3266667+00:00

    Hi Evan S ,

    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    As I understand your question, you are facing issue while trying to run SSIS package in ADF. Please let me know if that is not the case.

    It is possible to use Azure Data Factory to execute an SSIS job that transfers data directly between a linked server and an on-prem SQL Server. Instead of migrating the data to Azure, you can keep the databases on-prem and use ADF to run the job.

    To do this, you will need to set up a self-hosted integration runtime in ADF, which allows ADF to connect to on-prem resources. Once the self-hosted IR is set up, you can use it to run the SSIS package on the on-prem SQL Server.

    There are a few things that you need to keep in mind to make this work:

    • The self-hosted IR needs to be able to access both the linked server and the on-prem SQL Server

    • You will need to configure the ADF pipeline to use the self-hosted IR

    • You will need to ensure that the linked server and the on-prem SQL Server have the appropriate authentication and security settings to allow ADF to connect to them

    If you still have authentication issues, it is possible that there is a misconfiguration in your settings, or that the ADF pipeline is not using the correct authentication method. It's also possible that your on-prem resources are not accessible from the internet, in that case, you can use a VPN or ExpressRoute to connect ADF with your on-prem resources.

    For more details, kindly check out the following resources:

    Run an SSIS package with the Execute SSIS Package activity in Azure portal

    Azure SSIS Integration Runtime And Running SSIS Package In Adf


    Hope it helps. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.