ETL: Execute SQL Script Task

This requires Admin or Data Admin privileges for implementation.

Have you ever found yourself wanting to alter the data in a database table?

For example:

• You might want to change an "Email_Sent" field to "true" once you send out automated emails.  
• You may need to delete older data in order to adhere to data retention policies.

Now you can implement these types of solutions, using the Data Hub task: “Execute SQL Script,” one of the most powerful and flexible tasks in our library of 30+ ETL tasks.

This task requires an input dataset. Keep in mind that the dataset you use for input does not need to be related to the table you plan to modify. The script can execute once or multiple times — once for each row of incoming data. Or, a script can execute without any consideration of the incoming data set if you want to do something like dropping a table systematically.

Let’s walk through a basic example for deleting data from a table. When using the script to drop a table, you can use any (E)xtracted dataset to trigger the task. For this example, I’m going to create a dataset to that looks at the table that I'm planning to manipulate. 

Let's Begin

1. Open a new ETL Job and drag the ( E ) anywhere onto the canvas and then hover over the Extract Data icon and click on the gear. You will then be given the option to either "Create a new data set" or "Use an existing data set."

2. Once you have your dataset configured, drag the ( T ) onto the canvas in order to create a Task. You will immediately be prompted to choose which task you want to use. Navigate to "Miscellaneous" and select "Execute SQL Script."

3. Hover over the extracted data set and grab the arrow above the icon and drag the arrow to the center of the "Execute SQL Script" icon in order to create the link between the data and the task.

4. Once connected, click on the gear above the "Execute SQL Script"

5. You will immediately be prompted with a warning that notifies you that this task can permanently alter and/or delete data and that there is no way to "Undo" the changes made by this script. If you are ready to continue, click Yes. 

Note: It is important that you use this task carefully to ensure that you don't accidentally change or delete data. Make sure you know your data and that you've double-checked your SQL query before executing this task. As mentioned in the "Warning," it is advised you backup your data prior to running the task.

6. Select the target data store for the data that you are going to manipulate:

7. Now you're ready to create your execution task. Select your options, write your SQL Query and then click Save. For this example, we are going to use the following configuration:

• Execution Mode: 1 | Once, independent of the incoming data rows
• SQL Script: delete from guest.sample_data where DateAdd( day, -30, Cast( GetDate() as Date ) ) <= "Date"

Note: For more details on how to use this task, click the blue information button at the bottom of the task window.

8. Now that your task is configured, you can click on the play button to run your script.

9. Once the task runs, you should see binoculars next to all of your icons within the canvas. Clicking on the binoculars will display the results of the respective function. Let's click on the binoculars next to the "Execute SQL Script".

10. You will see the execution results showing you how many rows were affected by your query.  In this case, 211 rows were deleted from the database that were older than 30 days. You can also click on the "View Output Data Rows" if you want to see a list of the rows that were affected by the query.

11.  If this was a one-time query, you're all set. If you need this script for future deletion, simply save the ETL Job by clicking the Save button at the bottom of the window and you'll be able to come back and manually run this job in the future.

Note: You can schedule this job to run as well, however, in order to do so, you'll have to add another task to the end of the job. You could choose to load a table with the result count, or send out an email notifying the result of the job. You can't schedule ETL jobs that end with an SQL Script.