ADF- metadata driven - copy ADLS parquet multiple files to Snowflake multiple tables

Question

Hello, I have 20 parquet files from 20 SQL server tables stored in ADLS blob. Now I m using the metadata driven ADF pipeline to load each file to separate 20 tables in snowflake(to sync the SQL server source to snowflake database staging ). When I configure the metadata-driven ingestion pipeline, there is only a file to one table map is available, whereas I m expecting 20 such mappings to load 20 different files to 20 different tables in snowflake.

Is there any other way to do this one pipeline?

Answer 1

Hi @KarthikShanth ,
Thanks for sharing details and screenshot. While using Metadata-driven copy task, if your source is tabular datastore such as , Azure SQL server or Oracle database, we can select multiple tables and perform full load or incremental load to other datastore such as ADLS . However, if your source is files , there is no option to select multiple files or folders and perform copy activity dynamically.

When source is tabular datastore, we can select multiple tables to leverage parameterized pipeline to perform copy dynamically.

When source is ADLS files, we can't select multiple files.

Get more details on limitations of metadata driven copy task here: https://learn.microsoft.com/en-us/azure/data-factory/copy-data-tool-metadata-driven#known-limitations

To achieve your requirement, you can create your customized pipeline with the steps as described in the above answer as well. Reiterating the same steps here: These are the steps you can follow to achieve the same:

1. Use Get metadata activity and point the dataset to the folder having 20 files and use childItems in the fieldList to fetch all the fileNames .

2. Use ForEach activity to iterate through each of the filenames and process it one by one. Use this expression for the Item in ForEach : @{activity('Get Metadata1').output.childItems}

3. Inside Foreach , use copy activity and in source settings use wildcard path and provide filename as @item().name . In sink dataset, create parameter to make the tablename dynamic and pass the value as @item().name for fileName in sink table.

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

If the above answer helped, Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

Answer 2

Hi @KarthikShanth ,

Welcome to Microsoft Q&A platform and thanks for posting your query.

As I understand your query, you want to load data from 20 different files stored in ADLS to 20 Snowflake tables . Please let me know if my understanding is incorrect.

These are the steps you can follow to achieve the same:

1. Use Get metadata activity and point the dataset to the folder having 20 files and use childItems in the fieldList to fetch all the fileNames .
2. Use ForEach activity to iterate through each of the filenames and process it one by one. Use this expression for the Item in ForEach : @{activity('Get Metadata1').output.childItems}
3. Inside Foreach , use copy activity and in source settings use wildcard path and provide filename as @item().name . In sink dataset, create parameter to make the tablename dynamic and pass the value as @item().name for fileName in sink table.

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