# Sequence Generator

The *Sequence Generator* Transformation object in Astera 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.

The Astera Data Stack can either create a sequence instantly at the 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 *SeqGenerator: Context Information Properties* window. In the case of database sequences, the last value used is recorded in 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.

### Video

{% embed url="<https://www.youtube.com/watch?v=4SREigqZIwk>" %}

### Use Case

Here, we have retrieved data from the an *Orders* table using a *Database Table Source* object. We will use the *Sequence Generator* Transformation object to generate a sequence for the *OrderNo* field in our source data. Let’s see how this works.

### How to Use the Sequence Generator

1. Drag the *Sequence Generator* Transformation object from the Transformations section in the Toolbox and drop it on to the dataflow designer.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/Uwb87cNglUfXRmxeocd2/1.png)

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

{% hint style="info" %}
**Note**: We have the *Orders* table as our source from a [*Database Table Source*](https://documentation.astera.com/dataflows/sources/database-table-source). We will map the fields *OrderDate*, *RequiredDate*, *ShippedDate*, *ShipVia* and *Freight* to an [*Excel Workbook Destination*](https://documentation.astera.com/dataflows/destinations/excel-workbook-destination) object.
{% endhint %}

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/fTtWW31KsxSGwSHTzIeN/2.png)

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

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/eymtCvFyvh8eC6e4S3py/3.png)

This will open the *Context Information Properties* window.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/QXzkoLK9hYRUkkEUdpC3/4.png)

4. In this window, you can choose between three different types of sequence generations and specify the *Sequence Details*.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/gpxtai9wamW4w8Itb4M9/5.png)

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 Astera 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.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/WVtAmHiQGdH66GfkU179/6.png)

* *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.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/UKNMljVJGhtiJsQRd8OF/7.png)

{% hint style="info" %}
**Note**: In this case we will use the In-Memory sequence generator option.&#x20;
{% endhint %}

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.

{% hint style="info" %}
**Note**: In this case, the *OrderNo* field has been created in the *Excel Workbook Destination* object.
{% endhint %}

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/GiJ5qrbqxL0lAwdjrHSa/8.png)

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

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/LqY15OVApsFgbxpdSP88/SeqGenGif.gif)

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

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/HK4bP9GXWpvTpYkTl6wU/10.png)

The sequence has been generated in the new field, *OrderNo*.

This is how the *Sequence Generator* Transformation is used in Astera.