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

In this video, I discussed about how to perform column mapping dynamically in copy activity in Azure data factory
Link for Azure Synapse Analytics Playlist:
• 1. Introduction to Azu...
Link for Azure Databricks Play list:
• 1. Introduction to Az...
Link for Azure Functions Play list:
• 1. Introduction to Azu...
Link for Azure Basics Play list:
• 1. What is Azure and C...
Link for Azure Data factory Play list:
• 1. Introduction to Azu...
Link for Azure Data Factory Real time Scenarios
• 1. Handle Error Rows i...
Link for Azure LogicApps playlist
• 1. Introduction to Azu...
#Azure #AzureDatafactory #DataFactory

[@vijaybodkhe8379]

Thanks

[@WafaStudies]

Thank you 😊

[@papachoudhary5482]

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

[@WafaStudies]

Thank you so much for such a kind words 🙂☺️☺️

[@Funwithkiddoss]

Good scenario and superb demonstration. Thanks for your videos

[@WafaStudies]

Thank you 🙂

[@generaltalksoflife]

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.

[@WafaStudies]

Thank you for feedback 😊

[@deepureddy6567]

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

[@WafaStudies]

Thank you 😊

[@vijaybodkhe8379]

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

[@roshankumargupta46]

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

[@WafaStudies]

Thank you. Sure i will plan

[@sureshthippani5163]

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.

[@himanshutrivedi4956]

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

[@WafaStudies]

Thank you 😊

[@RahulKumar-jg5ly]

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

[@WafaStudies]

Thank you 😊

[@vijaymulimath6519]

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

[@priyankapatimidi2392]

Great content, very much helpful in real time implementation

[@WafaStudies]

Thank you so much ☺️

[@battulasuresh9306]

Master piece Add more videos in this play list

[@WafaStudies]

Thank you 🙂

[@battulasuresh9306]

@@WafaStudies anna, ADB Nerchukovalantey pre Requsities konchem chepparaa? currently working in deloitte on ADF

[@HanumanSagar]

@@battulasuresh9306 Hi Bro which project u are working in Deloitte?..

[@avirozenboim6446]

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]

Great nice way

[@SQL4ALL]

@@WafaStudies Great video. Thank you

[@SQL4ALL]

Thanks Avi, this is really helpful. You might need to add leading { in the query ....tested works for me

[@anshuldubey9395]

Great finding Avi

[@Benwooduk85]

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.

[@0shaan0]

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....

[@gurumoorthy5321]

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 -- ?

[@geoffchaddock]

Great tutorial, proved very helpful in my real time implementation

[@mayurkrish75]

Kudos to this tutorial! Very well explained.

[@nivedabaskar316]

Thank you very much sir , very useful content . 👍

[@WafaStudies]

Thank you ☺️

[@udaychodagiri252]

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

[@neha1075]

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

[@sudheermattapally2791]

Awesome....very useful article.

[@WafaStudies]

Thank you 🙂

[@Sandeep.Gupta27]

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

[@ajaykhedkar5940]

Yes , I have same doubt. If my files are coming daily, and one of file have some additional columns then how can we cope up with that? 2nd thing if I wanted to vomit those extra columns then can I do that? If yes, then how? @maheer

[@SairamV17]

Did you get any answer ? For this

[@SairamV17]

#Wafastudios please answer this ?

[@satyaraj21]

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

[@venkatrajak6277]

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?

[@vickym3193]

One reason I can think of is - if in future, you get more columns, you can directly add more items to the JSON, instead of modifying your ADF pipeline.

[@MaheshReddyPeddaggari]

Great content Thanks Maheer

[@WafaStudies]

Welcome 🤗

[@rodrigoalejandronunezcabre3150]

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 !

[@parthgovekar8693]

hi, did you found any solution for dynamic mapping from a web source ?

[@shekareddy5146]

I was looking for the same 👍

[@WafaStudies]

😊👍

[@nitagawade3330]

Outstanding Maheer!!!

[@WafaStudies]

Thank you ☺️

[@AnandKumar-dc2bf]

Excellent video...

[@WafaStudies]

Thank you 😊

[@rk-ej9ep]

Very nice..👍

[@WafaStudies]

Thank you ☺️

[@bagamanocnon]

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.

[@acharjyadebasis]

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

[@annukumari9629]

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. :)

[@WafaStudies]

Thank you 🙂

[@sonamkori8169]

Thank You Sir

[@WafaStudies]

Welcome 😊

[@nuwanmenuka]

Amazing explanation

[@yashaswinividwanmani2143]

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???

[@prasangisreenwas9088]

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

[@ragharaj3367]

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.

[@nagasandeepkumarkapa1561]

Hi ,Can I know if you have found any resolution for this one

[@ragharaj3367]

@@nagasandeepkumarkapa1561 Microsoft say there is no way we can do this, but we had to make tweaks to the JSON to get it working

[@nagasandeepkumarkapa1561]

Thanks for that , any chance you have done that ? Please let me know just the idea if it is done.

[@skselva403]

Super brother 👌👌👌

[@WafaStudies]

Thank you 😊

[@user-wr3mt5yv5o]

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?

[@dibassimohamed5514]

It magical. Thank you a lot

[@WafaStudies]

Thank you 😊

[@priyankadp5777]

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

[@jebakanifamilyjyojay33]

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

[@sujitunim]

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.

[@GrowthMindsetGlobal]

Really useful

[@WafaStudies]

Thank you ☺️

[@ReelsVibe1]

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

[@gpriyanka811]

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

[@sanketchakane7745]

@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

[@shubhampawade2933]

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!

[@vasdecabeza2]

Yes, it can be in file stored in Blob Storage and use Metadata Activity to retrieve that.

[@shubhampawade2933]

@@vasdecabeza2 But get metadata activity gives us the information about the file right? And not the content of the file?

[@ayushibansal7947]

Awesome .

[@anuragkhare3821]

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.

[@sanketchakane7745]

in data flow there is a option swift drift, inferschema

[@shanavajshanu7196]

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

[@vasavig4612]

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

[@WafaStudies]

You need to maintain file and sheet names some where as configuration and then lookup that data to dynamically point to sheet names

[@deepjyotimitra1340]

Hi, I have a requirement of loading all excel files, it may be csv or xls into sql db. The file names are not known because it will be dynamic everytime only file extension is fixed. Can we read file from blob using *.xls or *.csv?

[@deepjyotimitra1340]

For my requirement, i think the ans will be metadata activity.

[@KostikV]

Many thnx!

[@vijaysagar5984]

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.

[@manasam7777]

Hi @vijaysagar5984 any solution of the asked question?

[@karnatimanikantareddy9969]

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

[@suktvm]

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

[@snvp786]

Nice

[@WafaStudies]

Thanks 🙏

[@santhidhanuskodi8668]

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

[@nightfury5967]

thanks. :)

[@WafaStudies]

Welcome 😊

[@jolyjuju]

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

[@potrunaresh2241]

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

[@dbasalo]

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

[@hindi-english1664]

Is it working for delta loading?

[@anjireddy5931]

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

[@WafaStudies]

kzbin.info/www/bejne/p6uro61thppnbZY

[@parthpatwardhan3450]

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

[@cloudfitness]

👍🏻

[@priyankadp5777]

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

[@srinubathina7191]

Thank You Sir