Use two linked services in the copy activity in the source option

Question

I have two linked services, oracle1 and oracle2, and I want to make a query that relates two oracle1 tables and one oracle2 table with the data copy option in the source option in query mode. In other words use two linked services in the copy activity (Source). Is that possible is it possible?

Answer 1

Hi @Caicedo Chamorro Edwin Alexander ,

Thanks for approaching Microsoft Q&A forum.

We cannot use two linked services in a copy activity directly. You can try solving your problem by following the below steps :

Use one dataset may be pointing to oracle1 and then select query option and write cross query for another database table as well to join.

Step 1: If you want to perform a cross-database query between two Oracle databases (oracle1 and oracle2) using Azure Data Factory and database links, you can follow these steps:

Create Database Links:

In Oracle, you'll need to create database links in the oracle1 database to access the oracle2 database. You can use SQL statements similar to the following:

-- In oracle1, create a database link to oracle2
CREATE DATABASE LINK oracle2_link
CONNECT TO remote_user IDENTIFIED BY remote_password
USING 'remote_db_tns_entry';

Replace remote_user, remote_password, and remote_db_tns_entry with the appropriate credentials and connection details for accessing the oracle2 database.

Configure Azure Data Factory:

In Azure Data Factory, you can create a pipeline with a copy data activity to copy data from oracle1 to a destination. Follow these steps:

a. Create Linked Services:

Create linked services for both oracle1 and the destination database in Azure Data Factory. These linked services should contain the connection details for the respective databases.

b. Create Datasets:

Define datasets for the source views or tables in oracle1. Define a dataset for the destination where you want to copy the data.

c. Create Data Copy Activity:

Create a data copy activity within your Azure Data Factory pipeline. Configure the source dataset to reference the views or tables in oracle1. Configure the destination dataset to point to the target location.

d. Mapping and Transformations:

In the data copy activity, configure the mapping to specify how the data should be copied from the source to the destination. You can define transformations, column mappings, and other settings as needed.

e.Run the Pipeline:

Trigger or schedule the pipeline to execute the data copy activity. When the pipeline runs, it will copy data from oracle1 (via the database link) to the specified destination.

By creating a database link from oracle1 to oracle2 and using views in oracle1, you can simplify the data copy process within Azure Data Factory. This approach allows you to perform a cross-database query and copy data from multiple sources into a single destination.

Step 2 : You can also try loading oracle tables data into SQL tables using two copy activities. After this use the join query on the SQL tables.

Hope this helps. Do let us know if you have any further queries. Thank you.