Dynamic Input Tool
The Dynamic Input tool reads from an input database at runtime and dynamically chooses what records are read in. Designer filters the database table content and returns only the data that meets the specified criteria. The returned data is joined to the incoming data stream.
File schema errors
To prevent errors, verify the schema of all the files you are reading is the same.
The Dynamic Input tool can improve processing speed when reading a relational database across the network, and can perform spatial queries on relational databases that do not contain spatial objects.
Configure the tool
- Select an Input Data Source Template.
- Click Edit... to select a path to a file or database table. See the Input Data Tool for more information on supported file types and data sources.
- Select how input data sources are updated at runtime.
- Read a List of Data Sources: Uses a selected column to perform a selected action on a list of data sources.
- Append Suffix to File/Table Name: Changes the specified file or database table name by appending the value of the selected Field as a suffix.
- Prepend Prefix to File/Table Name: Changes the specified file or database table name by prepending the value of the selected Field as a prefix.
- Change File/Table Name: Changes the specified file or datable table name based on the value of the selected Field.
- Change Entire Path Name: Changes the specified database input path based in the value of the selected Field.
- Modify SQL Query: Uses a SQL query to set which records and values are returned from the database.
- Pass a Field to the Output: Appends a selected column to the data returned from the database.
- Replace a Specific String: Replaces any instances of a string in a database with the value from a selected column.
- SQL: Update WHERE Clause: Changes the WHERE clause to replace a static value with the value of the selected column. All columns passed to this tool are available.
- SQL: Spatial Filter: Uses a polygon object in the incoming data stream to determine if existing latitude and longitude coordinates in the database are contained within the object's bounding rectangle.
- SQL: Update Stored Procedure: Changes a procedure based on the value of a selected column.
If reading a relational database that contains multiple tables, the Choose Table or Specify Query Window displays.
Select and manage a list of data sources
Field: Select the field, or column, that contains the value to modify the file, database table, or path name. All columns passed to this tool are available.
Action: Select how the file, database table, or path name is modified.
Modify your SQL query
Click Add to include a new clause. Each option opens a new window when selected.
Append a column to output
Output Field: Select the column to be appended. All columns passed to this tool are available.
Replace string with column value
Text to Replace: Enter the string that appears in the database.
Replacement Field: Select the column that contains the value to replace the entered string. All columns passed to this tool are available.
Modify WHERE clause to use column value
SQL Clause to Update: Select a clause to update. All predefined SQL clauses are available.
Value Type: The data type of the value being replaced.
Text to Replace: The text from the existing WHERE clause that is being replaced.
Replacement Field: Select the column that contains the value to replace the entered string. All columns passed to this tool are available.
Group Replacement Value for SQL IN Clause: Determines how the clause is processed. By default, an individual query is run for each clause. When selected, a single query is run unless the character limit is reached, which causes the single query to be divided into smaller queries. The box is automatically checked if the query contains an IN clause.
Character Limit for IN Clause: The size limit is the maximum number of characters that can be used in a query.
Filter coordinates based on polygon
Latitude Field: Select the column from the database that contains latitude coordinates.
Longitude Field: Select the column from the database that contains longitude coordinates.
Fields are Integer (multiplied by 1000000): Reports the data format of the coordinates. By default, the coordinates are assumed to be decimals. Select to indicate that coordinates are formatted as whole numbers.
Spatial Filter Field (Polygons only): Select the column that contains the polygon object the coordinates are evaluated against. All polygon objects passed to this tool are available.
Update stored procedure parameters
Parameter to Update: Select the from the list of parameters within the database.
Parameter Name: Automatically populated by selecting a parameter.
Value Type: Automatically populated by selecting a parameter.
Text to Replace: The text from the existing parameter that is being replaced.
Replacement Field: Select the column that contains the value to replace the entered string. All columns passed to this tool are available.
Click Edit to change the parameters of a clause.
Reorder the clauses by clicking Up and Down.
To delete a clause, select it and click Delete.