Sequence Generator

The Sequence Generator in Astera Centerprise is used to add sequences of integer values to a dataflow. The sequences can start with any number and have any step, for example, 50, 55, 60, 65 etc.

Centerprise can either create a sequence at dataflow’s run-time (this is called in-memory sequence), or it can read sequence control data from a database table as your dataflow is executed.

In the case of in-memory sequences, a sequence always starts at the Start Value provided in the sequence properties. In the case of database sequences, the last value used is recorded into the control database, and a new start value is used every time the sequence is invoked. This makes it possible to generate ever increasing values for the sequence each time the dataflow runs. In effect, such a sequence is a chain of sequences with non-overlapping values.

Use Case

Here, we have retrieved data from the Orders table using a Database Table Source object. We will use the Sequence Generator transformation to generate a sequence for OrderNo for the source data. Let’s see how it works.

How to Use the Sequence Generator

1. Drag the Sequence Generator transformation object from the Transformations drop-down in the Toolbox and drop it on to the dataflow designer.

2. Map the required fields from the source object to a destination object.

Note: We have the Orders table as our source from a ***Database Table Source***. We will map the fields OrderDate, RequiredDate, ShippedDate, ShipVia and Freight to an Excel Workbook Destination object.

3. To configure the properties of the Sequence Generator Transformation object, right-click on its header and select Properties from the context menu.

This will open the Context Information Properties screen.

4. On this screen, you can choose between three different types of sequence generation and specify the Sequence Details.

A description of these three methods is given below:

• In Memory: The sequence will be created in memory at the dataflow run-time. The sequence always starts at the specified start value in the sequence properties.

  Sequence Details:

  • Start Value – the initial value for the sequence
  • Step – the increment value

• Database Table: The sequence control information for the database table can be managed within Centerprise through the Manage Sequences option.

  • Connection: Specify the connection to the database where the sequences will be stored

  • Sequence: Select the sequence from the list of available sequences in database.

    Note: To manage database sequences, go to Menu > Tools > Sequences.

  • Batch Size: Specifies the minimum number of values to be allocated to the sequence.

  • Use Memory Sequence during preview: Prevents the user from breaking the sequence cycle during a data preview by substituting with an in-memory sequence, which does not affect (i.e. increase) the database sequence’s current value.

• Sequence Object - The sequence control information is read from a special database table coming from SQL Server or Oracle database.
  • ​ Connection: Specify the connection to the database that stores your sequences.
  • Sequence: Select the sequence from the list of available sequences.
  • Use Memory Sequence during previews: Prevents the user from breaking the sequence cycle during a data preview by substituting with an in-memory sequence.

Note: In this case we will use the In-Memory sequence generator option. Let’s specify the Sequence Details as follows:

Start Value: 0

Step: 1

5. In the destination object, a new field will be created where the sequence generator value will be mapped.

Note: In this case, the OrderNo field has been created in the ExcelDest object.

6. The NextVal field will be mapped to the OrderNo field in the ExcelDest object.

7. You can see the output of the excel destination object in the Data Preview window.

The sequence has been generated in the new field OrderNo.

This is how the Sequence Generator transformation is used in Astera Centerprise.