# Sequence Generator Transformation

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>" %}

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

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2F9DtgaPVBheGOpCJvY5LZ%2Fimage.png?alt=media&#x26;token=5bdd0865-ae6d-498d-93fe-60b2ff1a184c" alt=""><figcaption></figcaption></figure>

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

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FCwU9IlvYAtVI6fok0VLT%2Fimage.png?alt=media&#x26;token=336ecbd6-e9b7-4c1a-8a7a-509e2a90fef4" alt=""><figcaption></figcaption></figure>

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

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FY2cJdGmje02LuYZJjbxC%2Fimage.png?alt=media&#x26;token=6241162a-56f1-4bf5-a71e-46e4749269d2" alt=""><figcaption></figcaption></figure>

This will open the *Context Information Properties* window.

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FZu8xjnxuRfoTMZcndB55%2Fimage.png?alt=media&#x26;token=e49e4dd4-9a02-4176-9827-70b57e921b41" alt=""><figcaption></figcaption></figure>

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

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FqbDQXYZ4iP0pPdlCcWHt%2Fimage.png?alt=media&#x26;token=967879e6-1cde-49ba-89c3-acb0399625bb" alt=""><figcaption></figcaption></figure>

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. The *Sequence Details are:*
  * *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.

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2Fe4hmECdrVKHVN9H0sOOV%2Fimage.png?alt=media&#x26;token=10794f3c-9d31-4b85-b344-af52f3e8b952" alt=""><figcaption></figcaption></figure>

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

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2Fjz5Q4Ia6C5KjuynOLlJA%2Fimage.png?alt=media&#x26;token=9c88777f-92aa-4953-9d4e-abdf1c3d826e" alt=""><figcaption></figcaption></figure>

{% 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 %}

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FKaATxPxx95D8XMwWPrdE%2Fimage.png?alt=media&#x26;token=93f9ce24-d788-40a3-bfe1-39f644b8eb0d" alt=""><figcaption></figcaption></figure>

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

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FhLgOCdz5bpUcx8MwgIOV%2Fimage.png?alt=media&#x26;token=8a48d5e7-26b1-49b0-973c-2406fa2d59fe" alt=""><figcaption></figcaption></figure>

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

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FWtc3UP5ZGnexXsBUrZHp%2Fimage.png?alt=media&#x26;token=1e116427-9577-48f7-8968-1e49ee432b93" alt=""><figcaption></figcaption></figure>

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

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