This transform does the multiple processing of a parameter-dependent source with differing variables. Paging is index-based.
The processing of parameter-dependent sources is defined as follows:
| Data Source | The data to be extracted. The data is processed using one or more variables as parameters. The source type can be selected as desired, e.g. a "relational" extract with a parameterized SQL query. The variables must be defined globally. |
| Tree format | See Overview of Tree Formats |
| Loop Source | Contains the values for the variables. The number of columns is the same as the number of variables that are applied to the data source. The number of lines defines the number of iterations for the table loop. The data sources are generated consecutively with the parameter values of the loop source and the individual results are standardized with UNION ALL. |
Notes:
-
The name of the columns in the loop source have to match the name of the variables used in the data source.
-
When the input is coming dynamically from the Source or Transform, but the "Field name" is left empty, the name of the source column/ function is used for the Field name. When the target is manually defined as constant, then the name "constant" is chosen instead of the value from the "Input" field. Adding more than one constant column results in error, because there would be two columns with the name "constant."
This transform can also be used to set variables dynamically for a data source. In this case, the loop source has to contain only one row and a column with the corresponding name for each variable that should be set. A Constant Table extract can be used as loop source to set variables statically.
Minimal page size: if a minimal page size is defined, the table loop might stop looping before reaching the actual end of the loop source. This parameter specifies a minimum number of rows that must be retrieved from a data source so that the iteration continues. If the number of rows is lower than the defined minimum within one specific loop iteration, the table loop automatically stops looping after that iteration. This enables data integrators to utilize index-based paging (e.g., for REST connections) without needing to find the exact number of pages beforehand.
It is useful to know that the page size is typically set to the Step property of a Counter loop source or the variable that defines the number of rows retrieved with a single REST connection call (e.g., $top for OData).
Note that if the number of rows retrieved on the last page (e.g., 50 of a total of 450) from a source connection is the same as the configured page size (e.g., 50), it is not possible to identify whether this is the last page or not. A subsequent call to the source connection is necessary, which will return 0 rows, to detect that the end is reached.
Updated February 7, 2024