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.