Data Flow SQL parameters are used to define parameters as result of the SQL query. These parameters values can be used across all objects in the Data Flow to parameterize various properties in them. They can also be used in expressions defined for other parameters in data flow.

It is mandatory that the result of the query should have two columns. The first column represents the key, and the second column represents the value corresponding to the key. The query can return single row or multiple rows and each row in the result should be identifiable by a unique key name.

You can create and use Data Flow SQL parameters in the following way.

Step I: Add or Copy a Data Flow SQL parameter

To add a Data Flow SQL parameter, on the Data Flow Properties menu, click SQL Parameters. Then, to add a new Data Flow SQL parameter, click on Click Here, or click Paste to paste the parameter you have already copied.

Note: To get to the Properties menu, either click on the empty area of the Data Flow canvas, or click on the Data Flow name in the search field drop-down.

Note: Data Flow SQL parameter can be copied across or within same Data Flows. To copy a Data Flow SQL parameter, select the parameter and click the Copy icon.

Step II: Provide the name, datatype and the context corresponding to the database where SQL needs to be executed for the parameter.

The canvas will show the parameter fields.

1. Click on the param name text box, if you want to modify the name. The name for each data flow parameter in the Data flow has to be different.

2. Depending on the return value, specify the data type, precision and scale.

3. From Context drop down list, you can either select Source/ Target context. Context refers to the source database or target database where the defined SQL is executed.Here, select the Source( SRC_product) from the context drop down list.

Note: Following operations are allowed on the Parameter entries: Add, Cut, Copy, Paste, Up, Down, Delete, and Search.

Step III: Provide the SQL query corresponding to the SQL parameter.

1. To open the Editor, under the SQL field, click on the Expression Editor arrow. 

2. This opens the Expression Editor window. You can provide the SQL query in editor which would return the result in two columns - key and value. The query can also include Project Parameter, Data Flow parameters, other Data Flow SQL parameters, and Diyotta System Parameters. 

Once you enter the SQL query, click Validate. If the syntax is correct, a success message is displayed.

Here for example, the SQL query is defined to pick the data in the column Product_code from table control_table by filtering on country_cd depending on the value of data flow parameter - country_cd. The query returns a single row with two columns. The first column represents the key name - product_cd and the second column represents the value for this key.

3. Optionally specify the default value that needs to be considered if the sql query does not return any value. If you want to modify the default value, then click on the default field text box and enter the new value.

The SQL query can include other parameters such as Data Flow Parameters, SQL Parameters, Project Parameters, and Diyotta System Parameters. For more information, refer Working with Data Flow Parameters, Working with SQL Parameters, Working with Project Parameters, and Diyotta System Parameters.

Step IV: Use the Data Flow SQL parameter to parameterize different properties in the Data flow.

  • In an expression editor of transforms and options in the data flow, the data flow SQL parameters can be selected from the Data Flow SQL parameters list in the parameters section.
  • When Data Flow SQL parameter needs to be specified in fields, where expression editor is not available, then type in the Data Flow SQL parameter name with $SP_ prefixed.

Use Case I: Using data flow SQL parameter to define the name of the target file generated as part of execution of associated data flow instance. 

Step I: In data flow edit page, select Target instance and click Load Properties

Step II: Specify the SQL parameter in the "value" field for the property "File Name". Here there is no expression editor so, the data flow SQL parameter must be manually entered and prefixed with $SP_ as $SP_Product_code.Specify the key name along with the Job Flow SQL parameter name to pick the value corresponding to it during execution - $SP_Product_code.Product_cd. During run-time, the value for "File Name" property will be replaced with the value returned by the SQL corresponding to the Data Flow SQL parameter - "Product_code" and the key - "Product_cd".

Use Case II: Using Data Flow SQL parameter in the Where Clause.

Step I: In data flow edit page, select Source instance and click Properties.

To edit the where clause, click the icon beside the Where clause and the Expression Editor wizard appears.

Step II: Add the required expression and for illustration, here we are filtering data based on retrieved value in data flow SQL parameter.

Note: The expression editor contains a section to display all the applicable parameters. To list all the Data flow SQL parameters, from the Parameters drop down list, click on the Data Flow SQL Parameters.

Click on the Data Flow SQL Parameter name to use it in the expression. Specify the key name along with the Data Flow SQL parameter name to pick the value corresponding to it during execution - $SP_Product_code.Product_cd. During run-time, the parameter will be replaced with the value returned by the SQL corresponding to the Data Flow SQL parameter - "Product_code" and the key - "product_cd".

To verify that there are no syntax errors, click Validate and success message appears.

Note: The SQL defined for the SQL parameter can return multiple rows with each row identified by unique key name - first column. To use value corresponding to a key, specify the data flow SQL parameter along with the key as illustrated in the use cases above. The SQL returning multiple rows can be defined in multiple ways.

Example I:

Select 'key1', <column name> from <table name>
union
Select 'key2', <column name> from <table name>
union
Select 'key3', <column name> from <table name>

Example II:

Select <key column name>, <value column name> from <table name>

  • The Data Flow SQL parameter is executed in the order set in the list of Data Flow SQL parameters. If an already defined Data Flow SQL parameter is used in the SQL query of the Data Flow SQL parameter, then it is mandatory then the used Data Flow SQL param be above in the list. For example, if the below are the Data Flow SQL parameters defined and $SP_product_cd is used in $SP_product_location then, it is mandatory that $SP_product_cd be above $SP_product_location in the list.
  • If the SQL parameter name ($SP_****) used anywhere in the data flow or associated data flows does not match any of the data flow parameters defined in the data flow then, during the execution of the data flow these references ($SP_****.<key>) will be replaced by default value of -1.
  • If the SQL parameter name ($SP_****) used in the data flow or associated data flows match any of the data flow parameters defined in the data flow, but the "key" referenced along with it does not exist in the result of the query then, during the execution of the data flow these references ($SP_****.<key>) will be replaced by default value of NULL.