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.
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 -- ?
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?
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.
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.
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
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....
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
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
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?
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.
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. :)
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
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.
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.
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?
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.
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?
@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
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
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
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???
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!
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.
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.
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
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
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
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
Thanks
Thank you 😊
Khuda aap ko lambi umar dei...
Sir, Long Live ! Long Live !
Thank you so much for such a kind words 🙂☺️☺️
Good scenario and superb demonstration. Thanks for your videos
Thank you 🙂
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
Thank you 😊
Wonderful ..You Rocks as always..My Azure Rockstar..
Thank you 😊
Great tutorial, proved very helpful in my real time implementation
Kudos to this tutorial! Very well explained.
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.
Thank you for feedback 😊
Great content, very much helpful in real time implementation
Thank you so much ☺️
Nice Video. Please keep on doing this kind of stuff.
Great explanation and presentation 👍👍👍....it is really helpful.
Thank you 😊
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 -- ?
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?
Master piece
Add more videos in this play list
Thank you 🙂
@@WafaStudies anna, ADB Nerchukovalantey pre Requsities konchem chepparaa?
currently working in deloitte on ADF
@@battulasuresh9306 Hi Bro which project u are working in Deloitte?..
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.
Thank you very much sir , very useful content . 👍
Thank you ☺️
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.
How to pass these dynamic mapping if my json is array type?
Awesome....very useful article.
Thank you 🙂
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
Thank you. Sure i will plan
I was looking for the same 👍
😊👍
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....
Outstanding Maheer!!!
Thank you ☺️
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
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
Did you get any answer ? For this
#Wafastudios please answer this ?
Great content
Thanks Maheer
Welcome 🤗
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?
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.
Very nice..👍
Thank you ☺️
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
!
hi, did you found any solution for dynamic mapping from a web source ?
i have one doubt.......that u have already hardcoded mapping throgh that u got json file so my question is is it dynamic mapping?
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. :)
Thank you 🙂
It magical. Thank you a lot
Thank you 😊
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
Amazing explanation
I have scenario of handling empty string in Source need to populate in DateTime column in target using TabularTranslator logic.
Will it fail if the 2 tables contains similar column name ?
Is it working for delta loading?
Hi , How can we manipulate/transform the source column in Copy data activity - Additional columns mapping ?
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.
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.
Excellent video...
Thank you 😊
Thank You Sir
Welcome 😊
Hi thanks for this video. Can we read Excel sheets dynamically in Azure data factory
You need to maintain file and sheet names some where as configuration and then lookup that data to dynamically point to sheet names
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?
For my requirement, i think the ans will be metadata activity.
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.
Hi ,Can I know if you have found any resolution for this one
@@nagasandeepkumarkapa1561 Microsoft say there is no way we can do this, but we had to make tweaks to the JSON to get it working
Thanks for that , any chance you have done that ? Please let me know just the idea if it is done.
Sir if we have done mapping manually to copy json object then why do we need to do it dynamically? Please reply sir
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?
if the excel column is changing (like Emp_name/emp_name/EMP_NAME) then how to handle this scenario
Like same task dynamically set column names by using maping data flows
how to concatenate two column values from source into one column value into destination in copy activity under mapping
@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
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
Great nice way
@@WafaStudies Great video. Thank you
Thanks Avi, this is really helpful. You might need to add leading { in the query ....tested works for me
Great finding Avi
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
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???
Super brother 👌👌👌
Thank you 😊
Really useful
Thank you ☺️
do u have the json code for the overall pipeline and DB changes? we can download and refer
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!
Yes, it can be in file stored in Blob Storage and use Metadata Activity to retrieve that.
@@vasdecabeza2 But get metadata activity gives us the information about the file right? And not the content of the file?
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.
Hi @vijaysagar5984 any solution of the asked question?
Many thnx!
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.
in data flow there is a option swift drift, inferschema
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
Nice
Thanks 🙏
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
Awesome .
Good work but most of the things you have created already how should we get it yaar
thanks. :)
Welcome 😊
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
th-cam.com/video/qzHrz7Q5474/w-d-xo.html
👍🏻
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 clearly explained❤
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