One of the primary objectives of any ETL tool or service that is designed to interact with databases, is to enable users to perform transformations quickly and efficiently on the data. Azure Data Factory has various built in transformation activities to perform specific tasks. But sometimes, it is more convenient to write custom SQL code to perform more specific transformations.
When it comes to executing custom SQL code, unlike SSIS, there is no specific Execute SQL activity in Data Factory. Instead, there are a few indirect ways to achieve this. In this post, we will go through some methods that enable users to execute custom SQL code.
Method 1: Stored Procedure Activity
This is probably the most obvious but indirect way of executing custom SQL code. We just create a Stored Procedure in the Database, and then call the stored proc from Data Factory, using the Stored Procedure activity. (By creating a Linked Service to the Database.)
Method 2: Lookup Activity
If creating Stored Procs doesn’t seem feasible or appealing (for a scenario where you just need to execute a simple SQL query on the database), the Lookup Activity can be used to run a simple SQL statement directly on the Database.
The Lookup Activity is designed to perform a lookup on a database table based on an existing column (or variable), but it also provides the option to write a custom SQL statement on the Database (using a Linked Service, of course).
The only catch here is that the Lookup activity expects an output result set from the SQL Query. To ensure that the custom SQL runs properly, we can add the following SQL code at the end of the custom SQL code.
Select 0 id;
This will trick the Lookup Activity to execute the custom SQL code.
Method 3: Pre-Copy Script
As the name suggests, in the Copy Data activity, under Sink settings, there is an option to write custom SQL, called Pre-Copy Script. The custom SQL code written here, gets executed before the data is loaded into the Sink.
As we can see above, all of these methods have their own limitations, but in the absence of a designated Execute SQL activity, these methods do provide a workaround for executing custom SQL in Data Factory.