Microsoft SQLServer 2012 Native Sequence In The Definition Of ODI Sequence

How To Use a Microsoft SQLServer 2012 Native Sequence

In The Definition Of ODI Sequence

The ODI configuration for the Native Sequence to work needs to be adjusted so that

the drop-down list which displays when you create the ODI Sequence shows the Native Sequences that exists in the used Microsoft SQLServer Logical Schema:
a)Start ODI Studio, and navigate to Topology Manager > Physical Architecture > Technologies.

1.Update the Microsoft SQLServer technology, so that it allows the usage of native Sequences:

Edit the "Microsoft SQL Server" technology.

On the "Definition" tab, check the "Supports native sequences" box,and set:

• Local Sequence Mask:

  

NEXT VALUE FOR %SCHEMA.%OBJECT

• Remote Sequence mask:

NEXT VALUE FOR %DSERVER.%SCHEMA.%OBJECT

b) On the "SQL" tab, in the "Specific Queries" area, go to the "Reverse Engineer Native Sequences" tab, and set the query to:

select SEQUENCE_NAME as SEQUENCE_NAME 
from INFORMATION_SCHEMA.SEQUENCES
where SEQUENCE_CATALOG= :CATALOG 
and SEQUENCE_SCHEMA = :SCHEMA
order by SEQUENCE_NAME

2. Use the Native Sequence in the definition of ODI Sequence:
1. Navigate to Designer. Create your ODI Sequence (either as Project Sequence - from the "Projects" > "Sequences" navigator, or as Global Sequence - from the "Global Objects" > "Global Sequences" navigator). On the "Definition" tab, set the "Native sequence" radio-button, and chose the desired Microsoft SQLServer Logical Schema. Set the "Native Sequence name", or chose the desired one from the loopup window: