Repeating a Stage using Queued Datasets

Sometimes when building maps, it becomes necessary to call a stage multiple times to process a subset of nested data. Take for example an Origin which may return an Account, but then also a list of Contacts in a structured format (such as JSON).

To process this single row of data, you will want to create 1 Account and 2 Contacts as defined in the "contacts" column (stored as a JSON Array). Starfish ETL has a feature called Queued Datasets which will allow us to define a stage for Contacts, and automatically repeat the stage for each row in the queue dataset.

To set the Queue Dataset, we at the beginning of each row we will set it to contacts column data, by calling SetQueuedJSON() in a VBScriptProcedure - Repeat Each Row.

1. Sub VBScriptProcedure
       SetQueuedJSON Origin("contacts"), "contacts"
   End Sub
   

SetQueuedJSON takes 2 parameters. The first is the JSON array data, and the second is the name of the dataset. Dataset names are optional - they can be left blank, you can have multiples if you need to process other data subsets such as lists of addresses, etc.

In addition to loading Queued Dataset from JSON, you could call SetQueuedDT() from a C# script which will take a .NET DataTable object as the parameter. This would be especially useful if you are retrieving your subset data from the result of a SmartQueryDT() function call.

Next you can add your Account stage as normal:

Then you can add your Contact stage, making sure to set the "Queue Behavior" to Repeat, and entering the name of Queued Dataset to use ("contacts").

Then you may proceed to set the mappings for your Contact stage. You can pull elements from your Queued Dataset by using the Queued() function and passing in the name of the field you want. Here is a simple example based on the data above. Note, you can mix mapping types of Queued() functions and pulling fields from the Origin.

Without any further scripting, Starfish ETL automatically processes the Contact stage twice, based on the data that was loaded into the "contacts" Queued Dataset.

• Related Articles

• Queued Stages

  Queued Stages are a useful feature for aggregating data or supplying "structured" data. Often systems will expect to have data bundled up and submitted all at once. A good example of this is how many ERP systems expect Sales Orders to be submitted. ...

• Using Row Hashing for Incremental Integrations

  Hashing The Whole Record Typically when setting up an ongoing integration, we only want to pull data from your origin which has changed since the last time Starfish ran. This is accomplished using LastRunDate and applying it as a filter against your ...

• Creating a New Stage

  Creating a Stage Under the mappings tab there is a bar titled stages. At the very end of the stages bar (on the right of the screen) there is a button with a + and what looks like a bulleted list, click this button and the New StarfishETL Stage menu ...

• Check for duplicates using xref

  I needed to check for duplicate attachments in a system and remove the duplicates. I created a Job that used the same origin and destination and pulled all records from the Attachments table. I had a before operation that would read and write to the ...

• Examples of using StarfishETL Class Functions In Javascript

  This code was used to lookup a value inside of SugarCRM using the Sugar REST connector. function scriptedField() { var res = ""; res = vals["Branch Name"]; if (res) { Starfish.LogMessage(res.toString()); } res = ...