- We use the synchronization task to synchronize data between a source and a target.
- The synchronization task allows us to synchronize the data between the source and the target. We can build a synchronization task from the IICS UI by choosing the original and the target without using any transformations like in mappings.
- Anyone without PowerCenteror IICS mapping and transformation knowledge can easily build synchronization tasks as UI guides you step by step.
- We can also use Expressions and lookups to transform the data according to your business logic.
- The synchronization task will allow Relational Databases (Oracle, SQL Server, MYSQL), Flat files, and Salesforce as Source and Target types.
- When you configure a synchronization task, you specify the task operation and the type of target. The available target types depend on the task operation that you select.
- In Realtime, we will this task to load the data source to stage tables.
Task Operations in synchronization Task: To use Update, Upsert or Delete operations, Target table must have a primary key.
▶︎ Insert: When you run a task with the Insert task operation, Data Integration inserts all source rows into the target. If Data Integration finds a source row that exists in the target, the row fails.
If you write data to a flat file target, Data Integration truncates the flat file before it inserts the source rows into the file.
▶︎ Update: When you run a task with the Update task operation, Data Integration updates rows in the target that exist in the source. If Data Integration finds a row in the source that does not exist in the target, the row fails.
▶︎ Upsert: When you run a task with the Upsert task operation, Data Integration updates all rows in the target that also exist in the source and inserts all new source rows in to the target.
If a source field contains a NULL value and the corresponding target field contains a value, Data Integration retains the existing value in the target field.
▶︎ Delete: When you run a task with the Delete task operation, Data Integration deletes all rows from the target that exist in the source.
Below is the Example of synchronization Task filed Mapping which contains Expressions and Lookups.
Using Saved Query in synchronization Task:
When you Create a synchronization Task, you can select source type as Saved query which I have shown in the next page.
- A saved query is a component that you create to run SQL statements against a database. You can use a saved query as the source object in a synchronization task or as the query in an SQL transformation.
- When you create a saved query, enter an SQL statement that is valid for the database that you want to run the query against.
- Create an SQL query by clicking on New-> Components ->Saved Query And enter any valid database Query.
- Enter a name for the saved query and the location where you want to save it.
- Select the database type. You can choose one among the below options,
iii) SQL server
To use the query in an SQL transformation, the database type must be Oracle or SQL Server.
- In SQL query section provide the SQL statement that make up the SQL query.
To use the query as the source in a synchronization task, enter a SELECT statement. Data Integration uses the SELECT statement to retrieve the source column information. To use the query in an SQL transformation, enter one or more valid SQL statements.
- If you enter a SELECT statement, click Get Columns and select a connection. The Saved Query Column Details table displays the columns selected in the SQL statement.