In your ETL integration development, update actions are common in many use cases. The update action allows you to update any existing records in the Destination Table/Service and can be used in tandem with a key column or a set of columns used for matching and comparing to determine the existence of records. The update action will update any or all the records that have a match found with the provided key. However, there could be cases where you may not want to update every single record with a match. Instead, you may want to update a specific set of records based on certain conditions. For example, consider that your database contains a table for all your contacts. You want to update some of the data that is stored in your contacts table; however, the data that is given to you may be outdated. In this scenario, you may want to compare the modified date of the Source Data against the modified date of the Destination table, and only update the record if the Source Data’s modified date is greater than (or comes after) the Destination table’s modified date. You may end up performing some logic that could potentially increase the complexity of your data flow.
KingswaySoft makes such conditional implementations easier for you. The example above can be easily achieved using our Premium Service Lookup component. With the Premium Service Lookup component, you can add comparisons between the Source and Destination data, and proceed based on that in a single flow. In this blog post, we will go over how to introduce a more complex, yet simplified design, for updating conditions using the Premium Service Lookup component. The below components will be used for our demonstration:
- Premium Excel Source component
- Premium Service Lookup component
- Conditional Split component (Out of the box)
- Premium ADO.NET Destination
Please note that the Source and the Destination could be any service, and the above is just an example. The overall design of the flow would look as shown below.
Setting Up the Source Data
First, we will have our Source component, which in our example, we are using a Premium Excel Source component that contains the updated data from a destination table. When performing an Update action, we will need to know which column (or columns) is the key field. The primary key for our dummy data here will be the Fullname field. This will be referenced at a later stage in the design.
Next, we will use our Premium Service Lookup component which is connected to the same Connection Manager as what we would use in our Destination connection (which you would see in the later section), and target the same Destination table. This enables the comparisons between the same data structures.
Adding the Conditional Logic
Our Premium Service Lookup component is where we will be entering the conditions we wish to set for our Update action. In our example, we only want to update the record if that record was last modified after May 25, 2015. We would check that the Destination table’s modified date is later than May 25, 2015. However, you can have the condition set to any number of possibilities, and mixed and matched with 'AND' and 'OR' logical operators. You can also set these conditions dynamically by using Variables instead of Static Values. In the Lookup Conditions tab, you will be setting the conditions that you want to be performed. The first conditions will be to make sure that the key columns are matching. This way we can make sure that the correct records are being updated.
On the Output Columns page, you need to configure the output column LookupResult_MatchFound. If this output column is returned as TRUE, it will let you know that the record has met your set conditions.
Now, the Premium Service Lookup component will split the records into 2 groups. The records that have satisfied the specified conditions are the ones that can be updated, and records that have not satisfied the conditions should not be updated as per the business requirements. Next, we will use a Conditional Split Component to filter out the values that cannot be updated or do not meet these set conditions.
Now all the records are passed to our Destination component will have passed the given conditional logic.
Writing to the Destination
In our example we are using a Premium ADO NET Destination component to write to an SQL table. However any Destination component can be used as long as it is matching with the Premium Service Lookup. We set our Action to Update and make sure that the key column in the Columns tab is the as our Primary Key. This would, when executed, update based on the conditions that we have designed in the upstream flow.
Conclusion
By adding in the Premium Service Lookup component it, as you can see, it gives us a greater control over the records that are being passed into your Destination component for any actions. This is specifically useful in applying such logic to your flow, trimming down the design aspects, and to make your flow lean and efficient.
We hope this has helped!