How to use Script activity
In this article, you learn how to add a new SQL Script activity, add a new connection, and configure script content. The Script activity can be used to execute both query and non-query SQL scripts against a backend SQL Azure or on-premises SQL instance.
Prerequisites
To get started, you must complete the following prerequisites:
- A tenant account with an active subscription. Create an account for free.
- A workspace is created.
Add a Script activity to a Pipeline with UI
Open an existing data pipeline or create a new data pipeline.
Click on add a pipeline activity and search for Script.
Alternately, you can search for Script Activity in the pipeline Activities ribbon at the top, and select it to add it to the pipeline canvas.
Select the new Script activity on the canvas if it isn’t already selected.
Refer to the General settings guidance to configure the General settings tab.
Configure the Script Activity
Select the Settings tab, and choose an existing connection from the dropdown, or create a new one.
After selecting your connection, you can choose either Query to get a data result or NonQuery for any catalog operations.
Then you can input content into the script expression box. We have multiple ways in which you can input script content into the expression box:
You can add dynamic content by either clicking in the box or clicking on the "dynamic content" icon on the right-hand side. A flyout will appear, as seen in the screenshot below, that showcases dynamic content and functions that you can seamlessly use to build your expressions. If you are familiar with Power Automate, the experience is very similar.
You can also directly edit your script code in the code editor by clicking on the pencil icon on the right-hand side, as seen in the screenshot below. After clicking on it, a new dialog box will pop up so that you can seamlessly write and edit your code.
You can also use the expression builder that utilizes IntelliSense code completion for highlighting, syntax checking, and autocompleting to create expressions. Refer to the Expression Language doc guidance to use the expression builder.
Save and run or schedule the pipeline
After you configure any other activities required for your pipeline, switch to the Home tab at the top of the pipeline editor, and select the save button to save your pipeline. Select Run to run it directly, or Schedule to schedule it. You can also view the run history here or configure other settings.
