Often there come certain situations where you would have more than one output from a Source component. This situation could be because the endpoint has parent and child records, and they return as two or more different outputs. For example, if an endpoint returns Orders as a primary output, then order lines would be a child output. In such cases, you would need to sort both the outputs and merge them using an available key. Our component provides the key for these records, whereas for sorting and merging, you can use the out-of-the-box "Sort” and “Merge Join" components.
We have considered two cases here, that would help you understand how this works.
Case 1: Shopify Rest API
The Shopify Rest API has an Object Orders that returns the orders and the order lines. Once you have chosen it as shown below, you would notice in the columns page that only the order fields are there, and there are no line details present. This is because these could be child array objects, and a secondary output is required for that.
In such cases, you would need to go to the "Columns" page, and then click on "More Fields". This would open a separate window called "Manage Additional Fields", where you would be able to find the additional columns that are returned by the API from related Child endpoints.
Once you have chosen the fields that you require, you can click OK to return to the "Column" page. For this example, we have chosen "Line Items" and its child items. In the Columns page, you will see that there is a new drop-down, as shown below, from which you can select the child fields, in this case, Line Items. You can choose this, and the metadata for the lines will populate in the columns page.
Now, while you enable the child fields, the "LinkID Mode" shows up in the "General" page of the REST Source component. The LinkId Mode option allows you to specify how _LinkIId field should be populated. The _LinkId field is a special column that is used to identify records in the parent output. During the runtime, the value of this field will go into the _{parentObjectName}._LinkId field of all this output's children. This is important for linking outputs with each other. You can choose between two options here.
- Implicit (RowIndex): a unique field that contains the current count of this output node. This is useful as many times, a JSON object will have no key field because relationships are defined hierarchically.
- Explicit (Id): the _LinkId column will be populated with the Id of the parent record.
Getting back to the use case, the Line Items that were enabled will be a different output, and you would need to sort and merge it to get related records as a single output. For that, you can use the out of the box "Sort" component and then a "Merge Join" component to do an inner or left or right or full outer join based on your requirement. For this, you would use the _LinkID that is available in "Orders" and the _Order._LinkID that is present in the "Line Items".
In this way, the output from the Merge Join component would be the sorted and merged output joining the parent and the child records.
Case 2: JSON Source returning parent-child output
In a similar manner as above, our JSON Source component will create a separate output for each JSON array in your JSON document, and these outputs can be linked together based on _RowIndex and _ParentKeyField fields. Therefore, you can use a Merge Join component in your Dataflow to establish a relationship between your records across the different outputs in order to get them together.
Consider an example where we have a JSON document that has Orders and Order details in it. These will be two different outputs, as shown below.
Considering that we use left outer join to get all orders and related order details, use two sort components as shown in the previous case. Then in the Merge Join component, the child "Order Details" can be the Left input and the parent "Orders" the right input.
Then join them as below using the _ParentKeyField of the child to the _RowIndex of the parent. In the output, you will get all the records merged while returning all child records.
But in both of the above cases, if you do not wish to design as shown, then we have a solution that would reduce the number of components used. Our Premium Lookup component that comes along with our SSIS Productivity Pack can directly perform a lookup on one input, based on another. So, by using the Premium Lookup component, you can directly map both the outputs to it and link the _parentKeyField to the _RowIndex field.
Closing
You can efficiently deal with parent and child records regardless of your Source component, thus making data integration that much simplier. All KingswaySoft components mentioned, (REST Source, JSON Source and the Premium Lookup component) can be found in our SSIS Productivity Pack. Feel free to give it a try and see how our solution can make your integration easier and efficient.