How to copy only selected tables from on premise MySql server to azure blob using adf

Question

Hi ,
I have an on premise My SQL database which is having almost 500 tables. Now i have created an ADF pipeline which is copying tables from MySql to blob using self hosted IR. Now in source dataset it ask to select table which i can select only 1 table at a time. I want to copy approx 40 tables from My SQL to blob and want to save it in blob using dynamic names. Can anyone suggest the workflow and the roadmap of achieving it

Answer 1

Hi @Amar Agnihotri ,

Thankyou for using Microsoft Q&A platform and posting your query.

As I understand your query here, you want to copy certain tables from your MySQL database and load it into Azure blob storage using Azure data factory pipelines. You want to know the best possible way to accomplish the same. Please let me know if my understanding about your query is incorrect.

Step1:
For this purpose, you can create a control table / lookup table within your MySQL database or create a look up file in .csv format and store it in Azure blob storage. Basically, In this lookup table/ file , you can create the metadata for the required tables to be loaded. Example: You can store SchemaName,TableName, IncrementalOrFullLoadType, TableIsActive , etc. This approach is also called metadata driven workflow.

Step2:
In ADF , use Look Up activity to fetch the data from the newly created lookup table / file. It will give us the metadata of the table you want to copy as the output JSON of Lookup activity.

Step3:
Use Foreach block to iterate through each of the tablename

Step4:
Use copy activity and parameterize the source and sink dataset to process the tables one by one .

For more details, please check the following video where we are loading data from onPremise SQL server to ADLS using this approach :

https://www.youtube.com/watch?v=FXw1gPaa2-M

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators