Transferring data to and from Azure

 

Capability	Azure CLI	AzCopy	PowerShell	AdlCopy	PolyBase
Compatible platforms	Linux, OS X, Windows	Linux, Windows	Windows	Linux, OS X, Windows	SQL Server, Azure Synapse
Optimized for big data	No	Yes	No	Yes 1	Yes 2
Copy to relational database	No	No	No	No	Yes
Copy from relational database	No	No	No	No	Yes
Copy to Blob storage	Yes	Yes	Yes	No	Yes
Copy from Blob storage	Yes	Yes	Yes	Yes	Yes
Copy to Data Lake Store	No	Yes	Yes	Yes	Yes
Copy from Data Lake Store	No	No	Yes	Yes	Yes

Capability	Azure Storage Explorer	Azure portal *	Azure Data Factory
Optimized for big data	No	No	Yes
Copy to relational database	No	No	Yes
Copy from relational database	No	No	Yes
Copy to Blob storage	Yes	No	Yes
Copy from Blob storage	Yes	No	Yes
Copy to Data Lake Store	No	No	Yes
Copy from Data Lake Store	No	No	Yes
Upload to Blob storage	Yes	Yes	Yes
Upload to Data Lake Store	Yes	Yes	Yes
Orchestrate data transfers	No	No	Yes
Custom data transformations	No	No	Yes
Pricing model	Free	Free	Pay per usage

Data pipeline orchestration technology in Azure

 

Capability matrix

The following tables summarize the key differences in capabilities.

General capabilities

GENERAL CAPABILITIES

Capability	Azure Data Factory	SQL Server Integration Services (SSIS)	Oozie on HDInsight
Managed	Yes	No	Yes
Cloud-based	Yes	No (local)	Yes
Prerequisite	Azure Subscription	SQL Server	Azure Subscription, HDInsight cluster
Management tools	Azure Portal, PowerShell, CLI, .NET SDK	SSMS, PowerShell	Bash shell, Oozie REST API, Oozie web UI
Pricing	Pay per usage	Licensing / pay for features	No additional charge on top of running the HDInsight cluster

Pipeline capabilities

PIPELINE CAPABILITIES

Capability	Azure Data Factory	SQL Server Integration Services (SSIS)	Oozie on HDInsight
Copy data	Yes	Yes	Yes
Custom transformations	Yes	Yes	Yes (MapReduce, Pig, and Hive jobs)
Azure Machine Learning scoring	Yes	Yes (with scripting)	No
HDInsight On-Demand	Yes	No	No
Azure Batch	Yes	No	No
Pig, Hive, MapReduce	Yes	No	Yes
Spark	Yes	No	No
Execute SSIS Package	Yes	Yes	No
Control flow	Yes	Yes	Yes
Access on-premises data	Yes	Yes	No

Scalability capabilities

SCALABILITY CAPABILITIES

Capability	Azure Data Factory	SQL Server Integration Services (SSIS)	Oozie on HDInsight
Scale up	Yes	No	No
Scale out	Yes	No	Yes (by adding worker nodes to cluster)
Optimized for big data	Yes	No	Yes

Azure Data Factory - If Condition activity

If activity is used to perform different activities based on the input expressions. In the following, we are checking if the file is present or not using "GetMetaData" activity. If file presents perform the activities in if True activities. If file does not present then Edit the False activities.

Passing input values for If condition activity

Set validation expression in settings for If condition activity.

Adding activity for True or False conditions.

For more information, please check https://docs.microsoft.com/en-us/azure/data-factory/control-flow-if-condition-activity

Azure Data Factory - For Each Activity

"ForEach" activity in Azure Data Factory is used to loop thru each row of the input and perform the actions based on the data.

Following screenshot shows two activities one is GetMetaData and second one is ForEach loop. The output of the GetMetaData is passed to the ForEach activity in the Settings. If the checkbox for "Sequential" is selected all the activities are performed in sequence for all the child items. If parallel execution is needed we need to un-check "Sequential".

"ForEach" activity can be used to an output of Lookup activity as well.

You can perform multiple activities for each row of data received for the input of ForEach activity. You can add all the activities by going to Activities and Edit Activities.

Limitations and workarounds

Here are some limitations of the ForEach activity and suggested workarounds.

Limitation	Workaround
You can't nest a ForEach loop inside another ForEach loop (or an Until loop).	Design a two-level pipeline where the outer pipeline with the outer ForEach loop iterates over an inner pipeline with the nested loop.
The ForEach activity has a maximum batchCount of 50 for parallel processing, and a maximum of 100,000 items.	Design a two-level pipeline where the outer pipeline with the ForEach activity iterates over an inner pipeline.

For more information, please check the documentation at  https://docs.microsoft.com/en-us/azure/data-factory/control-flow-for-each-activity

Azure Data Factory Stored Procedure Activity

Stored Procedure activity is used to execute Stored Procedure in Azure Data Factory. You can use this to maintain audit logs, update metadata information, to track files in Azure blobs to  MetaData activity output. To create dynamic external tables in Azure Data Warehouse.

To pass the parameter values to Stored Procedure, you can use pipeline parameters. Use dynamic content to pass dynamic values. You can import all the stored procedure parameters using import parameters

For more information on Stored Procedure activity, Please check the following https://docs.microsoft.com/en-us/azure/data-factory/transform-data-using-stored-procedure

Azure Data Factory Execute Pipeline Activity

Execute Pipeline Activity is used to call a different pipeline in Azure Data Factory. It is similar to calling Execute Package task in SSIS.

The main purpose of Execute Pipeline is to develop common pipelines which can be used in multiple pipelines and call them when needed. This helps to reduce development time and reduce maintenance of code.

If the calling pipeline needs to wait till the called pipeline is completed, set the "waitOnCompletation" to True.

Check the following for more details https://docs.microsoft.com/en-us/azure/data-factory/control-flow-execute-pipeline-activity

Azure Data Factory Lookup activity

Lookup activity in Azure Data Factory is different from the lookup activity present in SSIS. Lookup activity in Azure Data Factory is used to execute Stored Procedure with output.

It can return the first row or multiple rows of data from the stored procedure. The output can be used to input for each loop.

The lookup activity supports a max of 5000 rows as output.

For more details, please check the following https://docs.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity documentation

Azure Data Factory - GetMetaData activity

GetMetaData activity is used to get file information which is present in Azure storage. This will get file size, row count, lastModifiedDate, file exists and other information.

Following screenshot shows how to get all files information present in a particular folder. The folder name is passed as pipeline parameter

The output of this activity can be used as input to Stored Procedure activity. It can be used to store the metadata information in Azure SQL Database

For more information check the following

https://docs.microsoft.com/en-us/azure/data-factory/control-flow-get-metadata-activity