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.
If you are referencing the SQL Access object within a Composite Rule, that Composite rule must first be created.
- 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.
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.
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.
- 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)