Adding a numeric progressive column in a Data Factory copy activity

Question

Hi, in a Data Factory instance I need to implement a pipeline to read from an Oracle source and write to Synapse sink. I think to use a copy activity, but I need to add a numeric progressive column to write in the mapping tab. It seems that I cannot create a simple custom column as a numeric progressive or a columns resulting by a concatenation between two other columns.

Any suggests to add simple custom columns in a copy activity without using a mapping data flow?

Answer 1

Hi pmscorca, The copy activity's mapping tab doesn't directly support custom column creation, you can achieve this functionality using either a Data Flow activity or a combination of a lookup activity and a copy activity. Here are the two approaches:

1. Using a Data Flow Activity: Add a Data Flow activity to your pipeline. Within the Data Flow, create a source transformation for your Oracle data. Add a Derived Column transformation. Use expressions within this transformation to create the numeric progressive column and the concatenated column. Example for numeric progressive column: row_number() as ProgressiveColumn Example for concatenated column: concat(Column1, Column2) as ConcatenatedColumn Add a sink transformation to write the transformed data to your Synapse sink.

2. Using a Lookup Activity and Copy Activity: Add a Lookup activity to retrieve the maximum current value of the numeric progressive column from your Synapse table (if it exists). Store this value in an Azure Data Factory variable (maxValue). In the copy activity's source settings, use a query to select data from Oracle and add the numeric progressive column using a ROWNUM-like function, incrementing from the variable value.

Example: SELECT *, ROWNUM + @maxValue as ProgressiveColumn FROM YourOracleTable For concatenation, create the new column directly in the query. Example: SELECT *, Column1 || Column2 as ConcatenatedColumn FROM YourOracleTable Map the columns in the copy activity, including the newly added columns. --Hope This Helps you. Kindly Mark Accept-Answer if it resloves your query.