Database sources include relational sources such as Oracle, MySQL, and Microsoft SQL Server. When you configure a Source transformation for a database source, you can use a single source table or multiple source tables. If you use multiple tables as a source, you can select related tables or create a relationship between tables.
To configure a Source transformation for a database source, perform the following tasks:
•Configure the source properties.
•If the source includes multiple tables, configure the Source transformation to join the tables. You can join related tables or specify a custom relationship.
•Optionally, configure a custom SQL query to extract source data.
•Optionally, configure the Source transformation to filter or sort source data.
•Ensure that the table and column names do not exceed 74 characters.
Database source properties
Configure properties for database sources such as the database connection, source type, and source objects. You can also specify filter and sort conditions, pre- and post-SQL commands, and whether the output is deterministic or repeatable.
The following table describes the database source properties:
Property
Description
Connection
Name of the source connection.
Source Type
Source type.
Object
Source object for a single source.
Add Source Object
Primary source object for multiple sources.
Add Related Objects
For multiple sources. Displays objects related to the selected source object.
Select an object with an existing relationship or click Custom Relationship to create a custom relationship with another object.
Filter
Adds conditions to filter records. Configure a simple or an advanced filter.
Sort
Adds conditions to sort records.
Select Distinct Rows Only
Reads unique rows from the source. Adds SELECT DISTINCT to the SQL query.
Define Query
For a custom query. Displays the Edit Custom Query dialog box. Enter a valid custom query and click OK.
Tracing Level
Detail level of error and status messages that Data Integration writes in the session log. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
Pre SQL
SQL command to run against the source before reading data from the source.
You can enter a command of up to 5000 characters.
Post SQL
SQL command to run against the source after writing data to the target.
You can enter a command of up to 5000 characters.
SQL Query
SQL query to override the default query that Data Integration uses to read data from the source. You can enter an SQL statement supported by the source database.
Output is deterministic
Relational source or transformation output that does not change between mapping runs when the input data is consistent between runs.
When you configure this property, the Secure Agent does not stage source data for recovery if transformations in the pipeline always produce repeatable data.
Output is repeatable
Relational source or transformation output that is in the same order between session runs when the order of the input data is consistent.
When output is deterministic and output is repeatable, the Secure Agent does not stage source data for recovery.
Related objects
You can configure a Source transformation to join related objects. You can join related objects based on existing relationships or custom relationships. The types of relationships that you can create are based on the connection type.
Use the following relationships to join related objects:
Existing relationships
You can use relationships defined in the source system to join related objects. You can join objects with existing relationships for the following connection types:
•Database
•Salesforce
•Some Data Integration connectors
To join related objects, you select a primary object. Then you select a related object from a list of related objects.
For example, after you add Opportunity as a primary Salesforce source object, you can add any related objects, such as Account.
The following image shows a list of Salesforce objects with existing relationships with the Opportunity object:
Custom relationships
You can create custom relationships to join objects in the same source system. To create a custom relationship, select a primary object, select another object from the source system, and then select a field from each source to use in the join condition. You must also specify the join type and join operator.
You can select one of the following join types:
Inner
Performs a normal join. Includes rows with matching join conditions. Discards all rows that do not match, based on the condition.
Left
Performs a left outer join. Includes all rows for the source to the left of the join syntax and the rows from both tables that meet the join condition. Discards the unmatched rows from the right source.
Right
Performs a right outer join. Includes all rows for the source to the right of the join syntax and the rows from both tables that meet the join condition. Discards the unmatched rows from the left source.
For example, the following image shows a custom relationship that uses an inner join to join the EMPLOYEE and MANAGER database tables when the EMPLOYEE.E_MANAGERID and MANAGER.M_ID fields match:
Joining related objects
You can join related objects in the Source transformation. You can join related objects based on a relationship defined in the source system or a custom relationship defined in the Source transformation.
1On the Source tab, select Multiple Objects as the source type.
2In the Objects and Relationships table, select Add Source Object from the Actions menu.
3In the Select Source Object dialog box, select a source object.
The source object appears in the Objects and Relationships table.
4From the Actions menu, select Add Related Objects.
5To join an object based on relationships in the source system, select Existing Relationships, select the related object, and then click OK.
6To join an object based on a custom relationship, select Custom Relationships and perform the following steps:
aSelect the Related Object to use in the join.
bConfigure the Primary Object Key by selecting the primary object field to use in the join.
cConfigure the join type.
You can configure an inner, left outer, or right outer join.
dConfigure the join operator.
eConfigure the Related Object Keyby selecting the related object field to use in the join.
fClick Add.
The relationship appears in the Configured Relationships table.
gClick OK.
7To join additional sources, select the source to act as the primary source and then repeat steps 4 through 6.
Advanced relationships
You can create an advanced relationship for database sources when the source object in the mapping is configured for multiple sources.
To create an advanced relationship, you add the primary source object in the Objects and Relationships table. Then you select fields and write the SQL statement that you want to use. Use an SQL statement that is valid for the source database. You can also add additional objects from the source.
You can also convert a custom relationship to an advanced relationship. To do this, create a custom relationship, and then select Advanced Relationship from the menu above the Objects and Relationships table. You can edit the relationship that Data Integration creates.
When you create an advanced relationship, the wizard converts any relationships that you defined to an SQL statement that you can edit.
Custom queries
Create a custom query when you want to use a database source that you cannot configure using the single- or multiple-object source options. You might create a custom query to perform a complicated join of multiple tables or to reduce the number of fields that enter the data flow in a very large source.
To use a custom query as a source, select Query as the source type, and then click Define Query. When you define the query, use SQL that is valid for the source database. You can use database-specific functions in the query.
You can also use a custom query as a lookup source. For information about using a custom query in a Lookup transformation, see Custom queries.
When you create a custom query, enter an SQL SELECT statement to select the source columns you want to use. Data Integration uses the SQL statement to retrieve source column information. You can edit the datatype, precision, or scale of each column before you save the custom query.
For example, you might create a custom query based on a TRANSACTIONS table that includes transactions from 2016 with the following SQL statement:
SELECT TRANSACTION_ID, TRANSACTION_TOTAL, TRANSACTION_TIMESTAMP from dbo.TRANSACTIONS WHERE TRANSACTION_TIMESTAMP>’0:0:0:0 01/01/2016’
Data Integration ensures that custom query column names are unique. If an SQL statement returns a duplicate column name, Data Integration adds a number to the duplicate column name as follows:
<column_name><number>
When you change a custom query in a saved mapping, at design time Data Integration replaces the field metadata with metadata using the revised query. Typically, this is the desired behavior. However, if the mapping uses a relational source and you want to retain the original metadata, use the Retain existing field metadata option. When you use this option, Data Integration doesn't refresh the field metadata during design time. Data Integration maps the existing fields with the fields from the revised query at run time. Fields that can't be mapped will cause run time failure.
Tip: Test the SQL statement you want to use on the source database before you create a custom query. Data Integration does not display specific error messages for invalid SQL statements.
Source filtering and sorting
You can configure the Source transformation to filter or sort data before the data enters the data flow. Use the source query options to filter or sort source data.
Configure the query options on the Source tab of the Source transformation. Expand the Query Options section, and configure the filter and sort conditions.
You can use the following source query options:
Filter
Filter source data to limit the amount of source data that enters the data flow. You can create the following types of filters:
- Non-parameterized. Select the source field and configure the operator and value to use in the filter. When you configure more than one filter, the task applies the filter expressions in the listed order with an AND operator between the filters.
- Completely parameterized. Use a parameter for a filter expression and define the filter expression in the task.
- Advanced. Create complex expressions that use AND, OR, or nested conditions. The expression that you enter becomes the WHERE clause in the query used to retrieve records from the source. You can use source fields, input and in-out parameters, or system variables in the expression.
For example, you can use an input parameter for one of the fields, and select it when the task runs. You can reuse the same parameter in an Expression transformation to create a field expression and also in the Target transformation. Or, you can you an in-out parameter in the expression to retrieve rows that have been updated since the last run.
For more information about system variables, see Function Reference. For more information about parameters, see Mappings.
You can convert simple non-parameterized data filters to an advanced data filter, but you cannot convert an advanced data filter to simple data filters.
Sort
You can sort source data to provide sorted data to the mapping. For example, you can improve task performance when you provide sorted data to an Aggregator transformation that uses sorted data.
When you sort data, you select one or more source fields. When you select more than one source field, the task sorts the fields in the listed order.
Data in each field is sorted in ascending order. If you want to sort in descending order, you can use the Sorter transformation.
You can use parameters for the sort fields and define the sort fields in the task.
