Is it possible to add "Additional Columns" to Azure Data Factory in Copy Activity using "add dynamic content"?

Question

Is it possible to add "Additional Columns" to Azure Data Factory in Copy Activity using "add dynamic content"?

Gieger, Alfred 141

In an old thread (https://learn.microsoft.com/en-us/answers/questions/435405/it-is-possible-to-add-additional-columns-to-azure) this question has already been asked, but I want to resume the discussion and extend the question a bit.

I have the problem, that I would like to add a column to my copied data (with a source dataset linked to SAP), which contains the query timestamp, or in other words, the timestamp of when the copy activity is executed. I have found a way to achieve that, if the column name for this column is fixed:

User's image

As you can see, my activity is quite generic, it should be usable for various tables. Now it could happen, that my source table already has a column "query_datetime" with a different meaning (I know, that is very improbable for SAP sources, but the idea should also be applicable to other types of data sources). In that case I would have to choose a different name. So I would like to pass the name of that additional column in a pipeline parameter.

If I click into the field with the name of the column, the typical "Add dynamic content" appears, which leads me to the expression editor. But this expression is supposed to be of an array type, because it apparently does not refer only to the name of one particular additional column, but to a complete list of additional columns. Is there a way to populate this expression (using array variables, lookup-activity results or anything else). I did not find anything about the type of the elements of the required array in the documentation.

Note: I do not want to generate the entire pipeline programatically, but just the name of the additional column.

Thx

Parthiban Malan Prando 0 Reputation points

2023-12-11T11:53:30.8466667+00:00

@Gieger, Alfred - I am also trying similar solution for my client but using "data flow". From the above screenshot, it seems like the dataset has been created based on "SAP CDC Connector" linked service. Is my guess correct? If yes, then how are you able to use it in a "copy activity"? Just curious to know...
Gieger, Alfred 141 Reputation points

2023-12-11T17:00:18.08+00:00

We have created a linked service for the CDC connector, a data set using the linked service and a pipeline using the dataset in a copy activity. This seems to be straight forward, and it is. However, I know that in the documentation (at least some time ago), Microsoft claimed, that you would have to create a dataflow. To be honest, I do not really understand, what's the purpose of that dataflow. Anyway, this pattern for a CDC connector interface does not work for all SAP CDS views, which we tried to connect to. We are somehow struggling with 2 of the about 15 queries, maybe this is due to the fact, that we are using the copy activity.
Parthiban Malan Prando 0 Reputation points

2023-12-12T10:09:01.1333333+00:00

Thanks for the info, @Gieger, Alfred. Strangely for me I am not able to create a CDC Connector linked service/dataset based on a SAP CDS view. It is allowing me to create based on a SAP Table only! Anyways, thanks again.

Accepted answer

2 additional answers

Your answer

Parthiban Malan Prando 0 Reputation points

2023-12-11T11:53:30.8466667+00:00

@Gieger, Alfred - I am also trying similar solution for my client but using "data flow". From the above screenshot, it seems like the dataset has been created based on "SAP CDC Connector" linked service. Is my guess correct? If yes, then how are you able to use it in a "copy activity"? Just curious to know...
Gieger, Alfred 141 Reputation points

2023-12-11T17:00:18.08+00:00

We have created a linked service for the CDC connector, a data set using the linked service and a pipeline using the dataset in a copy activity. This seems to be straight forward, and it is. However, I know that in the documentation (at least some time ago), Microsoft claimed, that you would have to create a dataflow. To be honest, I do not really understand, what's the purpose of that dataflow. Anyway, this pattern for a CDC connector interface does not work for all SAP CDS views, which we tried to connect to. We are somehow struggling with 2 of the about 15 queries, maybe this is due to the fact, that we are using the copy activity.
Parthiban Malan Prando 0 Reputation points

2023-12-12T10:09:01.1333333+00:00

Thanks for the info, @Gieger, Alfred. Strangely for me I am not able to create a CDC Connector linked service/dataset based on a SAP CDS view. It is allowing me to create based on a SAP Table only! Anyways, thanks again.

Answer 1

@Gieger, Alfred Welcome to Microsoft Q&A forum and thanks for reaching out here.

Thank you for sharing detailed information about the experience you have gone through. I agree that there is not enough clarity or samples in the public document on how to utilize dynamic content feature in additional column feature in copy activity of ADF.

But as called out in the documentation, the dynamic content more helpful when you want to define the value of column that is defined in additional column section.

User's image

The below setting of dynamic content is useful when you want to pass static column names and their value from pipeline parameters or pipeline variables at runtime as shown in below example.

User's image

For example, I have a parameter that holds the additional columns and their value to be passed dynamically to the source settings,

Parameter details:
Name: additionalColumns

DefaultValue: [{"name": "<column1>", "value": "<value1>"}, {"name": "<column2>", "value": "<value2>"}]

User's image

In Copy activity source configuration, I can just refer to above additionalColumns parameter using dynamic content to define the additional columns and their values that I would like to copy to the respective sink data store.

User's image

And when you do a preview of source data it looks like below: including the additional columns defined using pipeline parameters.

User's image

Hope this clarifies. If you have any additional feedback regarding this feature or if you have any improvement suggestions, I recommend you to please submit your detailed feedback in IDEAS forum here : https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c All the feedback shared in this forum are actively monitored and reviewed by respective product owners.

Kindly share the feedback link once it is posted so that we can also share it with respective product owners to further review it.

Also, we received your feedback that earlier answer provided by Subashri on the thread was not helpful.
Kindly let us know what we could have done better to improve the answer and make your engagement experience good. We are here to help you and strive to make your experience better and greatly value your feedback.

I have provided a detailed answer which clear steps on the feature usage, for which you are looking for.

If you wish, you may consider re-surveying/rating for the engagement you received on the thread. Your feedback is very important to us.

Looking forward to you reply. Much appreciate your feedback!

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Subashri Vasudevan 11,226

Hi Gieger, Alfred

you want to decide the name of the additional column based on the source columns. For instance, if the source column contains query_datetime column already, we need to name the additional column as query_datetime_derived may be?

I doubt we can do that in copy activity additional column.

IMO, It is better to choose the name of the additional column in such a way that its not quite obvious to be found in any of the sources.

@Nandan Hegde please correct if i am wrong.

Gieger, Alfred 141 Reputation points

2023-09-05T17:50:52.3266667+00:00

Thank you for your suggestion, but I wonder, why they have placed this "Add dynamic content" link there, and why at all they check the type of the value I enter, if that feature is not available anyway.

Answer 3

Gieger, Alfred 141

Your explanation was exactly what I was looking for. I have learned from it more than I needed, for example, that setting a default value of a parameter is a practical way to generate a static array of objects. Unfortunately it does not yet completely solve my problem, because I still don't know how to generate an array of objects with dynamic values, which I could pass as an actual value for the parameter, in your example "additionalColumns". I would like to read the name of the additional column from a configuration file, and the value by evaluating an expression like "@utcnow()".

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-09-11T23:11:32.63+00:00

@Gieger, Alfred Glad that was helpful. And moving on to your follow-up question, I don't think we can pass an expression inside a parameter value (with regards to above example: dynamic expression inside array) and evaluate that at runtime. But what you could try is create a variable and try to form the Array object similar to that above example using the built in functions available in dynamic expression.
Subashri Vasudevan 11,226 Reputation points

2023-09-13T13:53:47.8333333+00:00
Gieger, Alfred

Please check if the below steps help (based on Kranthi's response).

What I'm trying to do: Decide the additional column name and value dynamically. This is for one column. But same should hold good for array of column names too.

I have a pipeline parameter, table_has_loaded_dt which will say true\false.

If true, that means that, my table \file which is being loaded already has a loaded_dt column.

If false, then the source doesnt have a column by name loaded_dt (in real time, you can use getmetadata activity to get the source file\table structure and decide if it has the loaded_dt column and assign to a variable)

Based on the value of table_has_loaded_dt, I am using an IF condition, to decide about my additional column name as follows.

If condition expression: @pipeline().parameters.table_has_loaded_dt

True & False part will have a set variable activity each assigning value to a variable, say col_name.

In true, i will assign the value "loaded_datetime" to my variable col_name and

in the false part, i will assign the value "loaded_dt" to col_name variable. (note the change of column names)

Upto here, i am just deciding the column name to be given to my additional column in copy source.

Next comes, the value part for my loaded_dt column. I am using another set variable activity called set_utcnow, next to IF condition and use utcNow() to get loaded datetime.

Here is the Pipeline design, to be able to understand better.

We have covered till set_utcnow activity until now.

Moving to third activity, we need to use a template variable called addl_cols of type array (remember kranthis reply poiting to additionalColumns parameter with template or placeholder array).

FYI, Here is the list of variables i need:

addl_cols: Its an array variable with the value [{"name":"<column1>","value":"<value1>"}]: this is the template variable that is going to be holding the additional column name and value for copy activity source. (similar to additionalColumns in Kranthis reply)

final_addl_cols: this is the copy of addl_cols variable. As we cant use self referencin variable in expressions.

loaded_dt: we used to set the current timestamp

col_name: we used it in if condition true & false part

Last but one set activity: replace_placeholder_col_with_actual_colname - I am generating the json for the dynamic column using the below expression to replace the place holders <column1> and <value1> in our template variable and assign to final_addl_cols

@json( replace( replace( string(variables('addl_cols')), '<column1>',variables('col_name') ), '<value1>',variables('loaded_dt')))

Finally, use the variable final_addl_cols in copy source as follows

If i run my pipeline by passing true to table_has_loaded_dt parameter, i will see additonal column name as loaded_datetime. Else, i will see as loaded_dt with utcNow() as value.

Hope this helps for your initial understanding.

Please revert for any question. Sorry for too long reply :)
Gieger, Alfred 141 Reputation points

2023-09-26T12:51:01.4366667+00:00

Thank you Suba, your explanation included the missing link: it was the "json" function, which I did not think of. You have used it to set the variable "final_addl_cols". With this function, it is possible to create any type of object, containing also dynamic elements, and by that also to pass a correct dynamic value to the additional columns field. I have used it now without any additional helper variables (of course, that's not as accurate as your solution, but I simply assume, that the configuration of my pipeline will only pass column_names for additional columns, that do not yet exist in the source data):

@json(concat('[{"name":"',pipeline().parameters.timestamp_column_name,'","value":"',utcNow(),'"}]'))
Subashri Vasudevan 11,226 Reputation points

2023-09-27T13:50:15.6033333+00:00

what do you mean by any type of object? how are we expecting the data to be? could you please tell some example?
Gieger, Alfred 141 Reputation points

2023-09-27T14:52:37.14+00:00
Maybe my comment was confusing. So most important first: my problem is solved completely now.

I'll try to explain my previous comment, but if it is too complicated to understand, simply ignore my explanation:

Of course "Any type of object" is a bit too enthusiastic. I meant, that there is a number of places, where I could use the "json()"-function to provide dynamic expressions of data type "object" or "array", where a complex object is required, not only for the additional columns. For example, when specifying a query condition for copy-activities.

Here is a sample screenshot of the source side of my copy activity:

In this image you can see the dynamic expression used for "additional columns". This has now been done by assigning the value of json expression I posted in my previous comment to a variable (but I could have placed my expression here directly as well).

Beyond that, there are also two selection criteria on column "DOCNUM" in this copy activity. At the moment those are still using the static column name "DOCNUM", In the JSON-source code of my pipeline, this currently looks like "

"selection": [ { "fieldName": "DOCNUM", "sign": "I", "option": "BT", "low": { "value": "@variables('current_low')", "type": "Expression" }, "high": { "value": "@variables('current_high')", "type": "Expression" } }, { "fieldName": "DOCNUM", "sign": "E", "option": "EQ", "low": { "value": "@variables('current_high')", "type": "Expression" } } ]

With my improved knowledge, I can now create an array of the proper structure within my pipeline dynamically. For example:

@json(concat('"selection": [{"fieldName": "',pipeline().parameters.bulk_load_column,'", "sign": "I", "option": "BT", "low": "', variables('current_low'),'", "high": "', variables('current_high'),'"},{"fieldName": "',pipeline().parameters.bulk_load_column,'", "sign": "E", "option": "EQ", "low": "', variables('current_high'), '"} ]'))

In this sample, you see, that I have again replaced the static columnname ("DOCNUM") by a pipeline parameter (pipeline().parameters.bulk_load_column).

Share via

Is it possible to add "Additional Columns" to Azure Data Factory in Copy Activity using "add dynamic content"?

2 additional answers

Your answer