Azure SQL Server VS Azure Data Warehouse

Azure SQL Server

• Database size limit is 4TB
• Optimized for OLTP loads
• Cross-database queries are supported
• Automatic performance tuning of a database is supported

Azure Data Warehouse

• Supports up to 1 Peta Byte (1024 TB) size.
• Optimized for OLAP loads
• MPP (Massively Parallel Processing) system (data is processed on multiple parallel nodes)
• Polybase support to load data from multiple systems.
• Cross-database queries are not supported.
• Manual performance tuning.
• You can pause and resume the database to reduce cost.

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 Analytics: U-SQL Skipping headers and outputting headers

To skip the headers while reading the input files. Use the skipFirstNRows parameter

USING Extractors.Csv(skipFirstNRows:1)

For complete parameter details check the following

https://docs.microsoft.com/en-us/u-sql/functions/operators/extractors/extractor-parameters

Output the Header row while writting the output file

USING Outputters.Csv(outputHeader:true);

For complete parameter details check the following
https://docs.microsoft.com/en-us/u-sql/functions/operators/outputters/outputter-parameters