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

Astera Data Stack can either create a sequence instantly at the dataflow’s run-time (this is called an 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.

{% embed url="<https://youtu.be/4SREigqZIwk?list=PLmyM3TZtVUyJfz1bkpZ-CxJ33fAwHIbug>" %}

### Use Case

Here, we have retrieved data from the *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.

### Using the Sequence Generator

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

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FX9KJoCFWb0yaTaF2219p%2F1.png?alt=media\&token=152dde0b-4b21-4a7a-8ea3-26abfab3eae7)

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/astera-data-stack-v9/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/astera-data-stack-v9/dataflows/destinations/excel-workbook-destination) object.
{% endhint %}

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FXg60JdgeWq8EJkTnfFfm%2F2.png?alt=media\&token=5a0ad2e7-abf0-4fc4-af0d-c460ad2b6835)

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

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FGZycvYk0uKUVLjLvxpnO%2F3.png?alt=media\&token=270e5962-e85a-42d1-aed7-6cd6c2a72039)

This will open the *Context Information Properties* window.

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FrbbdUrXEZCgpY9NYKRpR%2F4.png?alt=media\&token=0422c0f7-534f-45ed-a093-f258d3bc8113)

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

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FYiTCniAoC8JygxDR7Zoj%2F5.png?alt=media\&token=8a88f677-3a17-4f16-9de0-4c0e92d86e18)

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 the database.

{% hint style="info" %}
**Note**: To manage database sequences, go to *Menu > Tools > Sequences*.
{% endhint %}

* *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://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FPNezTNXL3DPohyjQ86PD%2F6.png?alt=media\&token=35207dbb-cce6-4470-9d84-6b9a72a7681a)

* *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://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FYtxMNk1PXf6kAiMBYMyi%2F7.png?alt=media\&token=e01eb591-b9a7-4afc-836a-c694c0ff7638)

{% hint style="info" %}
**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
{% endhint %}

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://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FD5z3gvfsP4vzajSrUxoY%2F8.png?alt=media\&token=ff4d579f-7188-4fe7-838f-1a3829d27940)

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

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FZsXgPOJjY3S1FEDSXXi6%2FSeqGenGif.gif?alt=media\&token=e802a197-c984-498e-9827-0fdb8171a8f6)

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

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FQNwX6jjEbeo8UkeJS1zX%2F10.png?alt=media\&token=6e5c0379-2d58-4d4c-b580-1096340fde52)

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

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