DSXchange

How do I generate surrogate keys using a SQL server sequence ; and use it with the slow changing dimension stage ?

Set up your Surrogate Key Generator stage to get the next value from the SQL Server sequence.

Hi Ray ...thank you , but I don't have this option.
I have only DB2 and Oracle in the list. I use DS 11.5

Unfortunately the answer to your question isn't a simple straightforward one. How you go about solving it depends greatly on the number of records you need to process or, in other terms, what your performance expectations are. Overall I would prefer to use the DataStage sequences unless the surrogate keys are also being generated by some other application and you need to synchronize those values with the ones from DataStage.

I'm sorry I wasn't clear about the issue I have ....
I'm looking here to have the confirmation if our installation has an issue or is simply like that, because in the <Database Type> drop down list, I have options only for DB2 and Oracle ?

I build the classic , standard job to create a sequence (the sequence is a db object that the stage should connect too)

ODBC ----- > Surrogate Key Generator -----> stat file

In the drop down list of Surrogate Key Generator Stage
|---- > Key Source
| ------> Database Type =( here I have only 2 options : DB2 and Oracle )
| ----- > Source Type = Database Sequence

How can we get the MS SQL Server option to be displayed in the Surrogate Key Stage??

There was no reason to ask this twice so I removed the stand-alone post on the subject.

All the documentation I see only lists two possibilities for the source as you note: DB2 and Oracle. AFAIK, you'll have to take another tack for SQL Server, either just let DataStage generate them (if that is a viable option for you) or use another mechanism to access the surrogate. From what I recall, a sparse lookup could be done to 'get' the next value... but I'm sure people will chime in with other thoughts.

The right answer is that with SQL Server we don't really need a Sequence Generator or State File or a database sequence , we just need to add to the table the IDENTITY column which will auto increment for every record inserted.
And to use that with the SCD stage just let the Surrogate Key option blank .
Of course in the case you don't want to load the Fact table at the same time.

The same is used with TERADATA.

We can use a table with IDENTITY column only if the Surrogate keys are unique across a table. What if the requirement is to have a unique value across the schema? I am using version 2008 and my dilemma is exactly this. I cannot use a state file and want the best possible alternative to use a squence generated in the database