I have an excel file as source that needs to be copied into the Azure SQL database using Azure Data Factory.

The ADF pipeline needs to copy the rows from the excel source to SQL database only if it is already not existing in the database. If it exists in the SQL database then no action needs to be taken.

looking forward to the best optimized solution.

CodePudding user response：

You can achieve it using Azure data factory data flow by joining source and sink data and filter the new insert rows to insert if the row does not exist in the sink database.

Example:

1. Connect excel source to source transformation in the data flow.

Source preview:

2. You can transform the source data if required using the derived column transformation. This is optional.

3. Add another source transformation and connect it with the sink dataset (Azure SQL database). Here in the Source option, you can select a table if you are comparing all columns of the sink dataset with the source dataset, or you can select query and write the query to select only matching columns.

Source2 output:

4. Join source1 and source2 transformations using the Join transformation with join type as Left outer join and add the Join conditions based on the requirement.

Join output:

5. Using filter transformation, filter out the existing rows from the join output.

Filter condition: isNull(source2@Id)==true()

Filter output:

6. Using the Select transformation, you can remove the duplicate columns (like source2 columns) from the list. You can also do this in sink mapping by editing manually and deleting the duplicate rows.

7. Add sink and connect to sink dataset (azure SQL database) to get the required output.

CodePudding user response：

You should create this using a Copy activity and a stored procedure as the Sink. Write code in the stored proc (eg MERGE or INSERT ... WHERE NOT EXISTS ...) to handle the record existing or not existing.

An example of a MERGE proc from the documentation:

CREATE PROCEDURE usp_OverwriteMarketing
    @Marketing [dbo].[MarketingType] READONLY, 
    @category varchar(256)
AS
BEGIN
    MERGE [dbo].[Marketing] AS target
    USING @Marketing AS source
    ON (target.ProfileID = source.ProfileID and target.Category = @category)
    WHEN MATCHED THEN
        UPDATE SET State = source.State
    WHEN NOT MATCHED THEN
        INSERT (ProfileID, State, Category)
        VALUES (source.ProfileID, source.State, source.Category);
END

This article runs through the process in more detail.