Composite Records

What are composite records?

Many real-world applications need to handle data structures which can't be represented with a simple flat table. A good example is an invoice record where each record contains a sub-list of line item records. For each main object, which identifies the customer invoice, there can be any number of child records, which identify each of the items being billed. These child records are also known as composite records.

Modelling composite records in COZYROC SSIS+

SSIS doesn't provide a specialized infrastructure or guidance for processing that kind of hierarchical data, but we have come up with an elegant way of handling it. Our unique approach allows processing of records in an efficient "streaming" mode, enabling the handling of big volumes of data without concern that your server will run out of RAM.

Many of COZYROC's adapters (e.g. NetSuite, QuickBooks, Sage, REST, MongoDB, EDI etc.) have built-in handling for composite records to support parent-child relationships between the processed entities. Most hierarchical structures have only two levels. However, some services contain hierarchical structures with many levels, so COZYROC's adapters for those services are capable of handling any number of levels.

For source components, this means that the external data gets represented with multiple outputs: the first one is for the main records, while the subsequent output(s) are for their composite records. The outputs for composite records automatically include all the fields of the parent output(s), so if you need the source data flattened, you won't need to set up any additional joins.

NOTE : The records in child outputs use a blank row delimiter (i.e. a row for which all fields have NULL values) to separate child records for different parent records. These delimiters allow COZYROC destination components to properly restore the encoded hierarchical structure.

COZYROC destination components that support composite records have multiple inputs that enable assembling complex hierarchical objects before sending them to the external system. The matching of the parent-child rows is done using the already mentioned blank row delimiters. Such formatted hierarchical input data can either come from a COZYROC source adapter or can be easily prepared from any OLEDB, ADO or ADO.NET source by using the Query transform.

An example with NetSuite invoices

To demonstrate how COZYROC components work with composite records, we will be creating invoices in NetSuite from two MS Excel worksheets. NOTE : The same concepts would apply for various other composite records scenarios where COZYROC adapters need to handle parent-child relationships.

The challenge of sending invoice data to NetSuite in SSIS is that, along with the main invoice record, we need to send the line items (i.e. the purchased goods/services), too. Usually there would be two separate tables/files - one for invoice records and another one for line item records. The data from both sources needs to be correctly assembled, before forwarding it to the destination. In our example we will be using file sources for simplicity.

Figure 1: Relationship of main records to composite records

Figure 1 shows how the data should be structured so that a COZYROC NetSuite destination component can assemble the complex invoice object.

The Invoice main objects on the left are associated with the groups of line items on the right, as shown by the blue arrows. COZYROC composite data handling relies solely on blank row delimiters; there is no explicit association in any of the columns stored with the data in the files. In the file on the right, the invoice items associated with a single main invoice object are grouped together and a null record separates each group. The association is inferred from the order of the main objects and the order of the groups of line items in their respective files.

Figure 2: Data Flow Configuration with composite
records for NetSuite destination

In the example package shown in Figure 2, each source Excel file is read in by a separate Excel Source Plus component. This figure depicts the complete data flow configuration required to create the invoices in NetSuite using the COZYROC NetSuite destination component.

The Excel Destination Plus component labeled "Receive Results" shown at the end of the data flow is there to identify records with errors. Side note : since the NetSuite Destination supports an extended error output, which also collects the IDs of the newly created items, it can also be used to get the internal IDs of the newly created NetSuite invoice records.

Figure 3: Main object data configuration

During configuration of the package, the arrows from the two Excel Source Plus components are connected to the NetSuite destination component. Multiple inputs are offered when connecting each arrow and the correct input must be selected. In Figure 3, we will be selecting the main invoice object, called "Invoice", from the drop-down list, when we're connecting the Excel component on the left to the NetSuite destination component.

Figure 4: Composite records configuration

In Figure 4, we are selecting the invoice line items, called "Invoice_itemList_item", from the drop-down list, when we're connecting the Excel component on the right to the NetSuite destination component.

Figure 5: Resulting Invoice in NetSuite

In Figure 5, you can see one of the resulting invoices that we set up in NetSuite with the package shown in Figure 2. It contains the proper line items.

Our sample SSIS package successfully loaded the invoice data from MS Excel to NetSuite. Mission accomplished!

Using SSIS+ source components to retrieve composite records

If you wanted to create a package to do the reverse--for example, to back up the invoice information from NetSuite into your corporate MS SQL Server database, you would use COZYROC's NetSuite Source component to extract the invoices from NetSuite. You can configure e.g. two OLE DB destination components for receiving the data. One OLE DB destination would be used to store the main objects in a table and the other destination would be used to store the line items (i.e. the composite records) in another table. For the line items output you would just need to use a Conditional Split to filter out the blank delimiter rows (i.e. the rows with blank "InvoiceID").