Configure an SQL assignment node in DMS - Data Management

In Data Management (DMS), an SQL assignment node can assign the data that is read by using the SELECT statement to the output variables of the node. Then, the output variables can be used as the input variables of the downstream node of the SQL assignment node. This topic describes how to configure an SQL assignment node.

Background information

An SQL assignment node allows a task flow to obtain data from a table and assign the data to the input variables for a downstream node. You can use variables for the following purposes on task nodes:

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.

Procedure

Log on to the DMS console V5.0.
In the top navigation bar, choose DTS > Data Development > Task Orchestration.
Note
If you use the DMS console 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 that you want to manage to go to the details page of the task flow.
For more information about how to create a task flow, see Overview.
In the left-side Task Type section next to the canvas, drag the SQL Assignment for Single Instance node to the blank area on the canvas.
Double-click the SQL Assignment for Single Instance node.
On the configuration tab of the SQL Assignment for Single Instance node, enter a keyword to search for a database in the search box and select a database from the drop-down list.
Note
- You must have the read permissions on the database that you select. For more information about how to apply for permissions, see Overview.
- You can click the Metadata tab in the right-side navigation pane to view the schemas of tables in the database.

Click the Variable Setting tab in the right-side navigation pane to configure constants and variables. You can click the icon in the upper-right corner of the Variable Setting panel to view the tips about variable configurations.

Click the Node Variable tab and configure node variables. For more information, see the Configure time variables section of the "Variables" topic.
Click the Task Flow Constants tab and configure task flow constants. Task flow constants are fixed values and can be used on all nodes of the current task flow. Specify a constant in the ${name} format.
Click the Task Flow Variable tab and configure task flow variables. For more information, see the Configure time variables section of the "Variables" topic.
Click the Input Variables tab to view input variables.

Click the Output Variables tab and click Increase Variable to configure the output variables in the following table. Output variables can be referenced by downstream nodes.

Parameter	Description
Variable	The name and description of the variable.
Row	All Rows: All rows of the table are used in the value assigned to the variable. Specific Row: Specific rows of the table are used in the value assigned to the variable.
Column	All Columns: All columns of the table are used in the value assigned to the variable. Specific Column: Specific columns of the table are used in the value assigned to the variable.
Link	The operator used to concatenate values. This parameter is displayed if you set the Row or Column parameter to All Rows or All Columns. The concatenated string is assigned to the output variable.

Note

You cannot set the Row and Column parameters to All Rows and All Columns at the same time.
The value of an output variable is a string.

In the SQL editor, use the syntax of the database that is selected for the current node to write SQL statements.
Note
Only SELECT statements are supported.
- To preview the SQL statements that you are compiling, click Preview.
- To verify the SQL statements that are compiled, click Check.
- To save the SQL statements that are compiled, click Save.
Click Try Run.
- If status SUCCEEDED appears in the last line of the execution log, the test run is successful.
- If status FAILED appears in the last line of the execution log, the test run fails.
  Note
  If the test run fails, you can view the node on which the failure occurs and the reason for the failure in the execution log. Then, you can modify the configuration of the node and try again.