An SQL assignment node runs a SELECT statement against a database and assigns the query results to output variables. Downstream nodes in the task flow can then reference those variables as input. Depending on the node type, output variables can be used in different ways:
On a Single Instance SQL node, variables can be used as table names or in filter conditions.
On a Script node, variables can be used as parameters in a script.
On a Lindorm File Check node, variables can be used as file names.
Without an SQL assignment node, you would need to hard-code values in each downstream node or pass data manually between nodes. With one, a single query result flows automatically to any node that needs it.
Prerequisites
Before you begin, ensure that you have:
Access to the DMS console V5.0
An existing task flow. To create one, see Overview
Read permissions on the database you plan to query. To apply for permissions, see Overview
Limitations
Only SELECT statements are supported in the SQL editor.
Row and Column cannot both be set to All Rows and All Columns at the same time.
Output variable values are always strings.
Add an SQL assignment node to a task flow
Log on to the DMS console V5.0.
In the top navigation bar, choose DTS > Data Development > Task Orchestration.
If you use DMS in simple mode, click the
icon in the upper-left corner and choose All Features > DTS > Data Development > Task Orchestration.Click the name of the task flow you want to edit.
In the Task Type panel on the left, drag the SQL Assignment for Single Instance node onto the canvas.
Double-click the node to open its configuration tab.
In the search box on the configuration tab, enter a keyword to find your database, then select it from the drop-down list.
Click the Metadata tab in the right-side navigation pane to view the schemas of tables in the selected database.
Click the Variable Setting tab in the right-side navigation pane to configure variables and constants.
Tip: Click the
icon in the upper-right corner of the Variable Setting panel to view usage tips.The panel contains five tabs:
Tab Description Node Variable Node-scoped variables, including time variables. See Configure time variables. Task Flow Constants Fixed values shared across all nodes in the task flow. Reference them in the format ${name}.Task Flow Variable Task-flow-scoped variables, including time variables. See Configure time variables. Input Variables Variables passed in from upstream nodes (read-only). Output Variables Variables this node produces for downstream nodes. Click Increase Variable to add one. On the Output Variables tab, click Increase Variable and configure each output variable:
Parameter Description Variable Name and description of the variable. Row Which rows to include: All Rows or Specific Row. Column Which columns to include: All Columns or Specific Column. Link Operator used to concatenate values into a string. Displayed when Row is set to All Rows or Column is set to All Columns. In the SQL editor, write a SELECT statement using the syntax of the selected database. Use the toolbar buttons to work with your statement:
Preview — preview the SQL before running.
Check — validate the SQL syntax.
Save — save the SQL statement.
Click Try Run to test the node.
If the last line of the execution log shows
status SUCCEEDED, the test run passed.If it shows
status FAILED, check the log to identify which node failed and why, fix the configuration, then try again.
What's next
To see a complete example of SQL assignment nodes in a real task flow, see Use the task orchestration feature for e-commerce tagging.
To learn how downstream nodes can use output variables, such as in table names or filter conditions on a Single Instance SQL node, see Variables.