# 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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FeyCqDt9pvH90ADhvXSKK%2F1.png?alt=media\&token=37ffddd7-5f2e-40c1-bc4c-7afec9644e25)

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

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FUQDVU6iowggZa63D7FxT%2F2.png?alt=media\&token=38d543f7-66e2-4dc3-bf87-d3a5e4d494ab)

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

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FjwKlxq5G7RK5oHCuZOX2%2F3.png?alt=media\&token=69f170c8-4eba-4e37-9042-05ee7bf59b5c)

This will open the *Context Information Properties* window.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FcOeaRFJbKRmZIFagBicg%2F4.png?alt=media\&token=704b9b15-4964-4980-ade9-2334aa09f220)

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

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FjS2Cl3jczmdSNNEntb4e%2F5.png?alt=media\&token=7b8e1c5d-c454-413a-ba9f-4a88f7e2e472)

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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FL34a5IKqQhO51QbGztt4%2F6.png?alt=media\&token=92e4fe96-655f-4c91-b5d3-f0e1aa5a9269)

* *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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FrgfWKos0KgLvWY2et3cE%2F7.png?alt=media\&token=5ccb3e94-59e3-49cf-b184-f3340cc49410)

{% 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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FAdePtVZLHgkqfhkQOj7d%2F8.png?alt=media\&token=460978dd-6af4-454a-88ac-2fb9d37f01e3)

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

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FcGWHmIwh0JWmSNKapUsZ%2FSeqGenGif.gif?alt=media\&token=144c2080-1c78-4870-b09d-f47564d99727)

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

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fo9utIa6uP0NCAQFkNgSw%2F10.png?alt=media\&token=6d704646-9313-4d51-b270-89dd3c5017d3)

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

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