A Sequence Generator transformation in advanced mode and SQL ELT mode follows the same rules as in mappings. However, to use a Sequence Generator transformation in a mapping in advanced mode and SQL ELT mode, you need to additionally set a property in the mapping task properties, with the name of the sequence in Snowflake.

On the

Runtime Options

tab in the task properties, navigate to the

Advanced Session Properties

section.

Add a custom property and enter the following session property value in the following format:

Pushdown.<Sequence transformation name in mapping>.SequenceName=<sequence name to create in Snowflake>

For example,

Pushdown.Seq_SF.SequenceName=snowflake_first_sequence&:Pushdown.sequence_second.SequenceName=SECOND_SEQUENCE&:Pushdown.seq_third.SequenceName=THIRD_SEQ

When you use multiple Sequence Generator transformations, the name of the sequence object to be created in the Snowflake database for each sequence must be unique. If not, the transformation runs with the CREATE SEQUENCE IF NOT EXISTS query twice using the same name. The first sequence runs as expected, but the second sequence which has the same name as the first sequence does not override it. Hence, the nextValue generated for the second sequence is incorrect. However, when the sequence runs with a unique name for the sequence object, that sequence is not updated again, irrespective of the number of times you run the mapping.