Database Ingestion and Replication

Back Next

Microsoft Azure Synapse Analytics targets

The following list identifies considerations for preparing and using Microsoft Azure Synapse Analytics targets:

To deploy and run a

database ingestion and replication

task with a Microsoft Azure Synapse Analytics target, the target connection must specify a database user who has the CONTROL permission on the target database. To grant the CONTROL permission to a user, use the following SQL statements:

USE database_name; 
GRANT CONTROL TO user_name;

This permission is required for initial load, incremental load, and combined initial and incremental load jobs. This permission allows

Database Ingestion and Replication

to create target tables and database objects, such as external data source, external file format, and database scoped credential objects, if they do not exist in the database. This permission is specifically required for creating external data source and database scoped credential objects.

You must manually create the master key. To create the master key, you must have the CONTROL permission on the database.

Database ingestion and replication

jobs first send data to a Microsoft Azure Data Lake Storage Gen2 staging file before writing the data to Microsoft Azure Synapse Analytics target tables. The staging file uses the hexadecimal x1d separator as the field delimiter. After the data is written to the target, the entire contents of the table-specific directory that includes the staging files are deleted.

If you use Microsoft Azure Data Lake Storage Gen2 with a Synapse Analytics connection, you must enable the

Hierarchical namespace

option in Microsoft Azure Data Lake Storage. With this setting, use of blob storage is not recommended.

The number of columns in a source table that a

database ingestion and replication

job can propagate to a Microsoft Azure Synapse Analytics target must not exceed 508 columns.

Database ingestion and replication

incremental load jobs and combined initial and incremental load jobs generate a recovery table named INFORMATICA_CDC_RECOVERY on the target to store internal service information that prevents jobs restarted after a failure from propagating previously processed data again. This recovery table is generated in the same schema as the target tables.

After a

database ingestion and replication

job loads data to a Microsoft Azure Synapse Analytics target by using external tables, the job does not drop the log tables and external tables created on the target, even though these tables might be re-created when the job starts again.

Database Ingestion and Replication targets - preparation and usage

Download Guide

Watch

Comments

Cloud Mass Ingestion Homepage