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,
Brandon Ledbetter commented
I am currently using the Lookup activity to perform commands like creating a staging table, adding a table partition, switching partitions between the staging table and the target table, etc. I simply end the SQL statement in the Lookup activity with "select 1;" so that it is satisfied that it has a result set (that my pipeline does not use). I opened an Azure support ticket to find out if this was considered a legitimate use of the Lookup activity, and their answer was "no, it hasn't been tested for that". I've also mentioned this to a Technical Account Manager, and his feedback was that he has several customers using the Lookup activity in this way at a large scale. So we will probably proceed with caution, rather than have to maintain stored procs as a separate CI pipeline. But it would be great to have official support with an "Execute command" activity that is officially supported for this use case.