How to get max of a given column from ADF Copy Data activity

Question

I've configured a copy data activity with an on-premise SQL Server as the source and ADLS Gen2 as the sink. A control table is in place to capture tableName, watermarkDateColumn, and watermarkDatetime for extracting incremental data from the source database.

Once the data is pulled and loaded into the sink, I aim to retrieve the maximum value of the watermarkDateColumn within my dataset. Is it possible to obtain this information from @activity('copyActivity1').output?

Note that I am restricted from using an additional lookup activity to query the source table for the max(watermarkDateColumn) within the pipeline.

Answer 1

those links may help you

https://learn.microsoft.com/en-us/answers/questions/872597/filter-copied-data-from-copy-activity-in-azure-dat

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-aggregate#:~:text=The%20Aggregate%20transformation%20defines%20aggregations,by%20existing%20or%20computed%20columns

as summary there are several ways to do it, one of the methods is

To retrieve the maximum value of a specific column from the output of a Copy Data activity in Azure Data Factory when transferring data from an on-premise SQL Server to ADLS Gen2, you can use the following approach:

Aggregate Transformation in Data Flow

• Another method is to use a Data Flow activity after the Copy Data activity.
• Use the Aggregate transformation in the Data Flow to define the MAX aggregation on your specified column. This allows you to directly compute the maximum value of the watermarkDateColumn​​.