Write a SQL expression to directly query a database. You can load the result set into variables, and then use those variable at another stage in the flow.
SQL Script Properties
General
Display Name
The default display name of the node is SQL Script 1. Any subsequent nodes are named according to this naming convention, with the appropriate numeric suffix, for example SQL Script 2, SQL Script 3, and so on.
You can change the SQL script node name from directly from this field.
Description
You have the option to add a description to each node; this can be a useful way of documenting the node for yourself and other users.
Validate
Validate the SQL script.
Sql Script
Server
Choose the server where the relevant database is stored.
Database
Choose the database that you want to query.
SQL Script
Write your SQL script in the script window. You can open the SQL Editor which exposes the data source's schemas; you can easily add tables and columns to the SQL statement by double clicking or dragging and dropping. The editor also exposes any variables that have been configured in the Data Flow, enabling you to inject variables into the SQL statement.
Click the SQL icon to open the SQL Editor (red arrow).
|
|
Use the Explain Script function to produce an AI-generated explanation of what the script does. Each time you click the Explain Script button, a new explanation is generated. This explanation can be generated regardless of the method used to generate the script itself. For more information, see Explain Script. |
Commit database changes
In-Memory databases only.
If your SQL Script node manipulates your data structure, for example, by adding or deleting tables, you should select the Commit database changes checkbox to ensure your changes are persisted.
Warning: Depending on the size of your database, this may be a heavy operation. If you have multiple changes of this type (multiple SQL Script nodes), you can minimize the impact of this operation by setting the commit option only on the last node.
Load Result set into Variables
Load the result set (green arrow below) of the SQL script into a variable added using the + icon (yellow arrow below).
You can create a list variable (which are used in For Each Loops) using the union select function to select multiple values.
select 'Sydney' union select 'Brisbane' union select 'Melbourne'
- Click here to learn more about variables.
Example
In the example below, a list variable will be created containing the cities Sydney, Brisbane, and Melbourne:
