How to access tables from two databases in one ADF Script?

Question

I have two databases Employee and Employee_Archive, each one has a table 'dbo.Employee'.

I was planning to delete Employee.dbo.Employee if the records already in Employee_Archive.dbo.Employee.

I tried to use Script activity, I also tried to create a sproc to do the delete, but looks like these two databases were not linked.

DELETE e 
FROM [dbo].[Employee] e 
JOIN Employee_Archive.[dbo].[Employee] d 
    on e.EmployeeId = d.EmployeeId

Could any please tell me how to make this come true. I think it's not hard, but I'm new to the ADF.

Answer 1

Which database are you using? In case if it is sql server, you can create linked server between those 2 and cross query the database. In case if it is Azure sql database, you can use elastic query to cross query database.

Based on the above scope, you can use a script task in the main database and execute your query.

The other way would be to use dataflow within adf wherein you can use 2 sources and get the distinct list of employees that are common and need to be deleted