Move data from multiple Azure tables to cosmosdb using Azure data factory

Question

I am using Azure data factory V2 to migrate data from Azure table storage to Azure cosmosDb sql Api. Under my subscription, I have multiple tables from which I want to move the contents to cosmosDb. All the tables have same format. I am able to copy data by creating a copy activity for single table. Names of the table are not static (cannot be guessed) as they are per user and depends on userID e.g Table_UserID.

I looked into "Lookup" and "Foreach" activity of ADF but seems like lookup activity works in specific source table to get the contents.

Looking for help on how can I loop over all the tables and copy over the data.

Answer 1

You can use parameterized query to perform this activity.

In the ADF Copy activity you can add a script to copy tables from Azure Table Storage to your Cosmos Sink

You can use below two links to formulate your pipeline:

1. Copy Bulk Data in ADF
2. Using Cosmos DB as Sink in ADF

Answer 2

Well i was in same boat and looking for solutions. Found that lookup doesn't help in case of Table Storage, and we have to replace lookup activity with Web Activity and query table storage rest api https://docs.microsoft.com/en-us/rest/api/storageservices/query-tables?WT.mc_id=AZ-MVP-5003556 which returns list of table names and then loop over the response. Hope that helps.