Reference an SQL Access object from a Ruleset to interact with a database during
transformation. Create a rule from the SQL Access. This allows you to pass an element,
field, or cell value into a database, or populate an element/field/cell with data from the
database.
-
In a Ruleset editor, select the Actions panel and locate the SQL
Access object to be referenced.
- To locate your own SQL Access objects, expand the folder named after the
package that contains them.
Note:
The filter box searches using the first characters of a name. Therefore,
typing sql into the filter box will not show your SQL
Access objects.
Since file extensions do not display in the Actions panel, it is
recommended to include the file extension in the object's file name
itself.
-
Select and drag the SQL Access object to the Rules section. An SQL
Access object used as a Rule.
- If it is to stand by itself, drag it to a blank part of
the Rules section.
- If it is to be used within a Composite Rule, drag it onto
the Composite Rule.
-
Under the Properties view, select the SQL Access rule and specify the
values for each Property. Do this by dragging either a Schema node
(element, field, or cell) or a variable node from the Variables view to
the desired parameters.
- If passing data into the input parameter, drag the desired
element or variable to the property's Value column.
- If retrieving data from the output parameter, drag the
desired element or variable to the Outputs area.
- If hard-coding a constant into the property, type the
constant value in the property's Value column. (Input parameters
only)
Each
Property corresponds with the parameters created in the SQL Access
object. While the
In parameters names are listed,
Out parameters
are listed at
Output0,
Output1, etc. These correspond with the
order you specified in the SQL Access object's Parameters area.
Note: A single
SQL Access rule can output its value(s) to multiple elements or variables at
one time. For example, if you are pulling a UPC code from your database to
populate two fields in your schema, you can place both fields in the
Outputs' Return Assignments area.