Supported data providers for Oracle in Azure Data Factory SSIS IR

1
votes

We are trying to use Azure Data Factory SSIS Integration Runtime service to run a set of existing SSIS packages (basically to transfer data from oracle to SQL Server).

However, when deploying, there is a validation error saying "Package xxx: Connection xxx contains unsupported provider."

We tried using the following two providers in the SSIS packages, both of them were reported as unsupported:

  1. "Oracle Provider for OLE DB"
  2. "OracleClient Data Provider"

What are the supported providers for accessing Oracle in ADF with SSIS IR?

2
oraclessisazure-data-factoryazure-data-factory-2
Hi Gayan Dasanayake, can my answer be helpful for you? – Leon Yue
@LeonYue we tried to put all components into the same subnet but it didn't resolve the issue. We had to customize the SSIS IR and install the required drivers. – Gayan Dasanayake

2 Answers

0
votes

When connecting to Oracle using a provider such as "Oracle Provider for OLE DB" we need to install that to the node(s) running SSIS IR. This can be done by customizing the SSIS IR using the portal.

https://docs.microsoft.com/en-us/azure/data-factory/how-to-configure-azure-ssis-ir-custom-setup

Steps summary:

  1. Download and edit the sample script (main.cmd) provided in the above link (from the publicpreview container)
  2. Upload it with the ODAC122010Xcopy_x64.zip file to an Azure Blob Container.
  3. Get a "Shared Access Signature" uri for this folder.
  4. Shutdown SSIS IR.
  5. Go to customize the SSIS IR and provide the link copied.
  6. Restart SSIS IR.
-1
votes

Your SSIS package is access on-premise local Oracle DB.

As you know, Azure can not connect to on-premise resource directly. For example, when we need access the local SQL server, we must use Self-host integration runtime.

When deploy it to Azure and run the package in Data Factory, you will get the error.

We can get the reason from the document: Azure-SSIS Integration Runtime:

Azure-SSIS IR network environment

Azure-SSIS IR can be provisioned in either public network or private network. On-premises data access is supported by joining Azure-SSIS IR to a Virtual Network that is connected to your on-premises network.

The solution: Join an Azure-SSIS integration runtime to a virtual network:

If your SSIS packages access data stores/resources that allow only specific static public IP addresses and you want to secure access to those resources from Azure-SSIS IR, you can bring your own public IP addresses for Azure-SSIS IR while joining it to a virtual network and then add an IP firewall rule to the relevant resources to allow access from those IP addresses.

I'm sorry I can't test it for you because I don't Oracle environment.

Hope this helps.