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. 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.
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?
@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, 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?
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
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 .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.
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
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.
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 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 -- ?
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, 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?
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.
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 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.
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.
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
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!
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
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 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.
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 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.
How to pass these dynamic mapping if my json is array type?
I could have saved 3 weeks if I had seen this video before. Thanks bro
Sir if we have done mapping manually to copy json object then why do we need to do it dynamically? Please reply sir
I have scenario of handling empty string in Source need to populate in DateTime column in target using TabularTranslator logic.
if the excel column is changing (like Emp_name/emp_name/EMP_NAME) then how to handle this scenario
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. 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.
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?
Wonderful ..You Rocks as always..My Azure Rockstar..
Thank you 😊
@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, 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?
Great video sir these seem to be real time scenarios which are faced in projects
Thank you 😊
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
how to concatenate two column values from source into one column value into destination in copy activity under mapping
Hi , How can we manipulate/transform the source column in Copy data activity - Additional columns mapping ?
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 😊
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
Will it fail if the 2 tables contains similar column name ?
Great explanation and presentation 👍👍👍....it is really helpful.
Thank you 😊
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.
Kudos to this tutorial! Very well explained.
i have one doubt.......that u have already hardcoded mapping throgh that u got json file so my question is is it dynamic mapping?
Hi Sir, Is it possible to add an if condition on the source column in json mapping
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
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.
Great tutorial, proved very helpful in my real time implementation
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 ?
Is it working for delta loading?
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 -- ?
do u have the json code for the overall pipeline and DB changes? we can download and refer
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 ?
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 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.
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.
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 🙂
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?
Great content, very much helpful in real time implementation
Thank you so much ☺️
Thank you very much sir , very useful content . 👍
Thank you ☺️
Outstanding Maheer!!!
Thank you ☺️
Nice Video. Please keep on doing this kind of stuff.
Like same task dynamically set column names by using maping data flows
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?..
Awesome....very useful article.
Thank you 🙂
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.
Great content
Thanks Maheer
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
ua-cam.com/video/qzHrz7Q5474/v-deo.html
Amazing explanation
I was looking for the same 👍
😊👍
Excellent video...
Thank you 😊
Very nice..👍
Thank you ☺️
It magical. Thank you a lot
Thank you 😊
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?
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
Super brother 👌👌👌
Thank you 😊
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 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
Really useful
Thank you ☺️
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
Good work but most of the things you have created already how should we get it yaar
Thank You Sir
Welcome 😊
Many thnx!
Awesome .
Nice
Thanks 🙏
thanks. :)
Welcome 😊
Excellent video, you got a like from me, and I would add a subscription if I wasn't subscribed already!
👍🏻
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 very much sir. very clearly explained❤
Thank You Sir