Need of Execute SQL Task in Azure Data Factory v2
We only have a execute stored procedure in ADFv2. But most of the time we don't want to create stored procedure for all of the primary ETL tasks, such as counting the no. of records from a table, Updating data into tables, creating tables, etc. There are many such activities need T-SQL Execution. It would be great if we have Execution SQL option.
ADFv2 have the option to use variety of RDBMS source and sink systems such as MySQL, Oracle, etc., . ESQL would be the powerful task to have in Azure Data Factory V2 to be used in all of the RDBMS source / sink systems, as SQL is compatible for all of them.
Some of the other points to have ESQL task is highlighted,
1. Stored procedures are not the effective object to understand, debug complex sql code written within. With E-SQL, a large complex SP could be split at many places in the ETL flow and graphically represented to easy understanding. - Graphical representation is the core feature for any ETL tool.
2. Version management - For example. A create table script could be placed on a blob storage. which can be used as a source for E-SQL task. This helps to manage the table object in terms of adding additional columns in future could be done from blob / file storage
3. When SSIS is rebuilt on Azure Data Factory (which is the ultimate goal for Azure Data Factory V2). ESQL is used quite commonly in SSIS. By having this in ADF, it helps for quicker development
Additional feature with E-SQL could be great is to use source and sink systems. For example, getting data from Oracle source system and which need to perform a lookup with SQL Sink, and load them back to SQL Sink. If we have the option on ESQL to use both source and sink. It would be more powerful.
Thanks and Regards,