21. Dynamic Column mapping in Copy Activity in Azure Data Factory 

Thanks

Khuda aap ko lambi umar dei... Sir, Long Live ! Long Live !

Good scenario and superb demonstration. Thanks for your videos

This is a really useful video. Thanks for sharing and explaining in easy-to-understand language.

Great video sir these seem to be real time scenarios which are faced in projects

Hi Maheer, if you revise at the end of the video like what all activities you did in the pipeline then would be very nice. Thanks. Keep it up. Best of luck.

Great tutorial, proved very helpful in my real time implementation

Kudos to this tutorial! Very well explained.

Great explanation and presentation 👍👍👍....it is really helpful.

Wonderful ..You Rocks as always..My Azure Rockstar..

Is there a video for doing the same with JSON sources, dynamically passing the collection reference, the dynamic mapping itself, etc? Wasn't sure what the syntax was for it and if it is able to handle flattening any nested or complex types on the fly.....into a csv. We ignore headers if they are present with csv sources and pass "Prop_N" as the source column headers, along with the number of rows to skip. Wasn't sure what the equivalent was for "Prop_N" when it came to json..... perhaps the .[n] syntax?

Thank you very much sir. very clearly explained❤

Great content, very much helpful in real time implementation

Hi Maheer, Thank You... The solution is very interesting. I have 2 questions -- 1. Is it possible to run the for each parallelly (can we Uncheck "Sequential") -- ? 2. This json is the only possible solution to dynamically mapping the columns -- Do we have any other alternate logic -- ?

Awesome....very useful article.

Master piece Add more videos in this play list

Great Maheer! Would be great if you can make one video on how do you perform unit testing on copy activity to check whether rows and columns copied correctly or not

Nice Video. Please keep on doing this kind of stuff.

Great content Thanks Maheer

Amazing tutorial, very well explained and works a treat for me using CRM as a source. My only issue is I have to match source and sink field name, including case. Very odd as you clearly don't have that issue in your demonstration.

Hello Maheer, All your videos are simply superb and anyone can learn about Azure very easily ...would like to request you to Please create some videos on Azure Architecture....

Hi This tutorial is very good. can you please prepare a video to create dynamic dataset for different environments and please create some videos for COSMOS DB (No SQL) .once again thanks sir.

Outstanding Maheer!!!

I was looking for the same 👍

i have one doubt.......that u have already hardcoded mapping throgh that u got json file so my question is is it dynamic mapping?

Amazing explanation

Hi Maheer .another wonderful video.. I have a scenario.. to read from on prem db and write to azure sql db (upsert operation) .i am able to do this using copy activity. I have 2 new columns only in sink( create date and update date) .. create date shud be set with current date when inserting new record and update date column shud be updated only in case of updating the record(not inserting) .. how do achieve this using upsert copy activity.. i dont find any way to distinguish between new record or existinf record in copy

Excellent video...

Hi Sir, thank you soo much for this Video. I have one doubt, we are already mapping manually, so it will be fixed for multiple runs rt? then what is the use of passing this dynamically? because we have to map and take the json any how for the first time. please help me in this?

hello master!! , thanks for the video. ¿It is possible to perform a dynamic mapping from a web source and destination in to data lake? Thank you !

How to pass these dynamic mapping if my json is array type?

Thanks for the video. Its very informative. I have one doubt. Suppose we execute the pipeline once and for the 2nd execution what if the source file have some additional or missing columns!? Will this give error? Thanks

Hi Maheer I have one use case in which I am getting data from the rest API but the order of the column is not the same every time. And I need to write a response in the CSV file. But the column should be a specific order. Is there any way to handle this in ADF copy activity.

I have scenario of handling empty string in Source need to populate in DateTime column in target using TabularTranslator logic.

It magical. Thank you a lot

Very nice..👍

Hi. How do I change the type/data type in the Sink/destination dataset for a certain column? Say I want to change Emp_Id from int to nvarchar. I can't see any button to change the data type. thanks.

if the excel column is changing (like Emp_name/emp_name/EMP_NAME) then how to handle this scenario

Sir if we have done mapping manually to copy json object then why do we need to do it dynamically? Please reply sir

Data read and data written are not same I.e my source consists of details of 20 employees but my output when loaded into SQL db ...only first employee details are loaded...could u please help me resolve this issue???

Hi, can yo guide me please? I'm loading csv file, but whe the data is in SQL, these data it is in the differente order, I need the data in the same order that the source. What can I do for this?

Like same task dynamically set column names by using maping data flows

Will it fail if the 2 tables contains similar column name ?

Thank You Sir

Anna i am able to till 19 columns only, if I add one more extra column , i am getting the error like (Failed to run pipeline1 ) something i am getting can u pls Tess is there any limitations for adding the columns pls suggest anna

Its really very useful video for many no doubt, just one more thing, for example if source file has DOB or Birthdate which need to sink to the BirthDate column, in this case if i give multiple source tag, its not working, can any one have an idea to achive this

Hi , How can we manipulate/transform the source column in Copy data activity - Additional columns mapping ?

Super brother 👌👌👌

Thanks for this - was so helpful .... If I may , instead of manually creating the json I used string_agg function to generate the json automatically from a simple mapping table where you map source column to target column for each entity SELECT EntityId, ‘”type”: “TabularTranslator”, “mappings”: [ ‘ + string_agg( ‘{“source”:{ “name”:”‘ + SourceColumn + ‘”},”sink”:{“name”:”‘ + TargetColumn + ‘”}}’, ‘,’ ) + ‘ ] } ‘ AS ColumnMapping FROM dwh_control.entity_column_mapping em GROUP BY em.EntityId

@WafaStudies I am still confused if it is still a dynamic way to accept data types because anyway we are storing the data type in a physical table and we are accepting as a input while load if input data type changes then table_mapping still has old data how to update that table before load satrts

Hi Sir, Is it possible to add an if condition on the source column in json mapping

Really useful

Hi thanks for this video. Can we read Excel sheets dynamically in Azure data factory

Wow..! Very nicely presented. Thankyou so much for your constant effort. May you recieve many more accomplishments and blessings. You deserve it all. Keep going. :)

Instead of SQL table can we store mappings in a file? If yes then how do we query the data from that file. Please any response is appreciated! Btw Thanks for the amazing video series!

Requirement: Create poc for daynamic pipelines with config table and store proc.attached datasets are to load into tables in SQL and create add piplnes to load the data dynamically with config table . please tell me that bettr approach Thanks I am having 8 excel files

This is a great video! Thanks for that :) I have scenario where I have to map one column from the source to multiple sink columns. Please could you let me know if there is a way to do this. I would really appreciate your help on this.

how to concatenate two column values from source into one column value into destination in copy activity under mapping

do u have the json code for the overall pipeline and DB changes? we can download and refer

Its very helpful video for everyone. Thanks for sharing. I have similar kind of requirements but my data is in JSON format. I have data in JSON format in ADLS Gen2 and need to load the data into Azure SQL Table. I have collection reference under that I need take all data point. Challenge: Data point (Number of column) may vary over the time. How can we do it dynamically.? Example: Under the Collection Reference, we have 90 columns. After a year it may be 95 or 100.

Good work but most of the things you have created already how should we get it yaar

Many thnx!

Hi Bro, Any workaround for CSV files which has multiple headers and we can merge them as one Header ? Source is FTP and some files are good and some files has multiple headers.

Is it working for delta loading?

Maheer if you don't mind, please tell me how to create azure free account, I have created account but it has showed to me as you're not eligible for an azure free account

Nice

Awesome .

thanks. :)

Excellent video, you got a like from me, and I would add a subscription if I wasn't subscribed already!

👍🏻

Thank you very much sir , very useful content . 👍

Hi Maheer .another wonderful video.. I have a scenario.. to read from on prem db and write to azure sql db (upsert operation) .i am able to do this using copy activity. I have 2 new columns only in sink( create date and update date) .. create date shud be set with current date when inserting new record and update date column shud be updated only in case of updating the record(not inserting) .. how do achieve this using upsert copy activity.. i dont find any way to distinguish between new record or existinf record in copy

Thank You Sir