Make campaign ID (primary key) start at least from 10000

Hi Salvatore,

Please may you explain a bit more why you must force the ID values in your import job, instead of letting Adobe Campaign import system increment automatically the primary key value of the targeted standard objects?

And will you import the data with Adobe Campaign tools (workflow activity or manually with Import model treatment)? Or with the help of an external ETL (Extract / Transform / Load) tool ?

Some standard Adobe Campaign tables (schemas) use autopk=true so it increments automatically the primary key value (most of times internal=true ID generated but can be other fields as well).

The incremental counter is managed through sequences schemas, see xtkNewId in documentation, and these sequences values are shared for several tables/objects.

For instance, the folders (nodes) of the Adobe Campaign tree-list use such xtkNewId.
And even with an empty new instance, the values must start over 1001.

So you can import the historical campaigns (operations/deliveries) with standard AC tools, and let the system set the ID values accordingly.
If you want to keep the external ID you can store it in an additional field in same schemas (see schemas extension), but not force the default primary key values.

In case of you really need to force standard/default primary key values using sequences (autopk=true), then you must claim for range with GetNewIds procedure, but you won't obtain a range in small values, but after the current max value.

Hope this helps.

Regards
J-Serge

Hi Salvatore,

I have a working theory on how to achieve this.

In a very simple way , without extending your schema and chaning anything else, you can simply run an ALTER Table command to have your sequence which generates IDs to restart with 10000.

The command would have to run on the DB and the syntax would be something like :

ALTER sequence <Sequence name> MINVALUE 0 MAXVALUE 2147000000 RESTART WITH 10000 CACHE 1000 CYCLE;

This will reset the Sequence to restart with 10,000 and will start assigning IDs incrementally after 10,000.

Please bear in mind though , that this change will have an effect across all schemas which uses this sequence and the change will not only be limited to the operation schema.

If you want to make the change effective only in this schema you will have to implement a couple of more complex changes which include

1>. Create a new sequence on DB and also create a custom function for generating IDs for this sequence. Make sure to have the "restart with" parameter set at 10000 or after creating the Sequence , use the ALTER command to reset the sequence to start with 10,000.

2>. Go to your operation schema extend it and make sure that autoPk="true" and pkSequence="<new_sequence>" is set.

Your operation schema would then start using this new sequence with IDs starting from 10,000.

Regards,

Adhiyan