DSXchange

I like KeyMgtGetNextValue.
Can you tell me how to reset it?

You can reset it through the command line in the administrator. The command is: UPDATE SDKSequences USING DICT VOC SET F1 = '1' WHERE @ID = 'key_you _want_to_reset';

KeyMgtGetNextValue stores values in a hash file named SDKSequences. You could CAREFULLY update the values in this file.

I suggest you start by trying to view the file first. Create a job as follows:
Define two columns in your input hash file stage: KEY which is your key column (a varchar), and VALUE which is the value associated with the KEY (an integer).

Now just VIEW DATA. Get a feel for the file. By updating this file, you can reset the values. Practice writing to a sequential file, and before you make this a production job, include a sequential output file that reports the old and new values, so you have a recovery option.

We have a job with 2 parameters TableName and ColumnName. The value of the hash file key is always TableName so all it does is route this one value to the hash file.

select max(#ColumnName#) from #TableName#

This is my source query.

I think doing SQL will retard my process. I will try with reset thing, Thanks Keith.

Any more suggestions? PLEASE

A surrogate key is a primary key. So if you select Max() on it then it returns very fast. The job in question is a multiple instance job. It is called by all our jobs. The instance id is set to the TableName. Works great. I think someone at ASCL originally wrote it.