Expand my Community achievements bar.

SOLVED

Is it possible to load data on custom and AC (classic) schemas with an external ETL tool?

Avatar

Level 2

Hello

on Adobe Campaign classic (v7) onpremise, is it possible to load data on custom schemas with an external ETL? And own AC schemas?

Regards.

1 Accepted Solution

Avatar

Correct answer by
Level 10

Hi Joana,

You can either use the Adobe Campaign (classic v6/v7) capabilities (workflows with Collector file activities, etc), or your own ETL to import data in the dedicated schemas.


But please take care to all AC schemas having an auto-incremental key, so you can find autopk="true" in the schema, such as in NmsRecipient schema.
That means that the auto-increment primary key is managed by AC directly, not by the underlying RDBMS system (Database systems manage differently this kind of key, please refer to your RDBMS Oracle/SQL Server).

For all the autopk="true" schemas, there are sequences objects managed by AC, and this is stored in the xtkNewId table.
Please read the technical documentation about xtkNewId before trying to import data with your ETL tool, especially this one:

Database mapping


In addition to supported  web method from JSAPI to get an ID or range or ID, there are specific stored procedure for each sequence type, for instance with the default xtkNewId value of xtkNewId table (shared by more than 30 tables). Of course, the stored procedure code is specific to your RDBMS, you should read it...

Hence, with your ETL tool, it is mandatory to call this stored procedure to get a range of ID, then you can leverage them in your own ETL code.

Please check carefully the ETL code, and be sure to use this  stored procedure in order to not overwrite the sequence values and create a major issue.

Regards
J-Serge

View solution in original post

4 Replies

Avatar

Level 10

Hi,

Can you share more details about your need? There are some connectors available with AC Classic (like FDA to connect to external database, or CRM connectors, etc.), as well as capabilities to perform file imports or data import through APIs.

Florent

Avatar

Level 2

Hi

I want to know if, in an On Premise installation, I could load data with my company's ETL tool (example, IBM DataStage) on:

  • customized schemas

  • Adobe Campaign schemas (for example, Recipients schema)

or if it's necessary  use Adobe Campaign's capabilities to load data with perform file imports or data import through APIs.

Regards.

Avatar

Correct answer by
Level 10

Hi Joana,

You can either use the Adobe Campaign (classic v6/v7) capabilities (workflows with Collector file activities, etc), or your own ETL to import data in the dedicated schemas.


But please take care to all AC schemas having an auto-incremental key, so you can find autopk="true" in the schema, such as in NmsRecipient schema.
That means that the auto-increment primary key is managed by AC directly, not by the underlying RDBMS system (Database systems manage differently this kind of key, please refer to your RDBMS Oracle/SQL Server).

For all the autopk="true" schemas, there are sequences objects managed by AC, and this is stored in the xtkNewId table.
Please read the technical documentation about xtkNewId before trying to import data with your ETL tool, especially this one:

Database mapping


In addition to supported  web method from JSAPI to get an ID or range or ID, there are specific stored procedure for each sequence type, for instance with the default xtkNewId value of xtkNewId table (shared by more than 30 tables). Of course, the stored procedure code is specific to your RDBMS, you should read it...

Hence, with your ETL tool, it is mandatory to call this stored procedure to get a range of ID, then you can leverage them in your own ETL code.

Please check carefully the ETL code, and be sure to use this  stored procedure in order to not overwrite the sequence values and create a major issue.

Regards
J-Serge