How to copy multiple tables to Azure blob using Azure data factory

In my previous article, I wrote about introduction on ADF v2. In this post, let us see how to copy multiple tables to Azure blob using ADF v2 UI.

For this example, I have created tables named Test, Test1 within Azure SQL database - Source for the copy operation.

I have created Azure blob with Container called myfolder - Sink for the copy operation.

To copy multiple tables to Azure blob in JSON format, created
Pipeline1 - For Each activity with Copy activity
Pipeline2 - Lookup activity and Execute pipeline activity

Lookup activity provides the list of tables, output of Lookup are mapped to Object type parameter. For Each activity on each iteration (tables list in object parameter) provides the table name to source & sink within copy activity.

Below are the steps:



Create two Linked services 1) Pointing to Azure SQL database 2) Pointing to Azure blob folder




Create three datasets 1) for Lookup activity (Azure sql database) 
                                    2) for source (Azure sql database) 
                                    3) for sink (Azure blob folder)

Lookup dataset - just point to the azure sql database connection:



Source dataset for copy operation - just point to azure sql database connection and edit the table name as dummy:



Sink dataset for copy operation - just point to azure blob connection and edit the file name as Add dynamic content:

Also select the file format, for this example I have Json format.



Click + New to add parameter, I have created parameter with name azuresqltblname.
In the Parameterizable properties -> set the file name as @{dataset().azuresqltblname}



Create a pipeline - pipeline 1 and then click on the empty design window -> Parameters to create object parameter named tableslist



Now drag&drop ForEach activity and Settings -> select sequential and provide the items as
@pipeline().parameters.tableslist



Click on Activities -> Edit Activities -> drag&drop Copy activity 
Select the source dataset and query as SELECT  * FROM @{item().name}

(which means name from lookup query is passed to object parameter and on each iteration table name is passed dynamically)



Select the sink dataset:



In the Parameters tab -> Provide value for the dynamic property (file name) set in the sink dataset as @{item().name}

(which means name from lookup query is passed to object parameter and on each iteration table name is passed dynamically)



Create pipeline - Pipeline2. Drag&drop Lookup and Execute Pipeline activity as shown below:

In the Lookup settings, select the dataset and query to return list of table names

SELECT name FROM sys.tables
WHERE name LIKE '%test%'

Also uncheck the First row only option



In the Execute pipeline settings -> Invoke pipeline 1 and create input parameter for pipeline1 and then map the output of lookup to this parameter as
@activity('LookupTablesList').output.value



Now publish the changes, validate the pipeline and trigger pipeline2 and look into the Monitor window:



All the tables in Azure sql database with name like '%test%' will be copied as JSON file into Azure blob folder.

See Also:


No comments: