Azure Synapse Analytics requires a JDBC connection configuration with the driver name and
location for all connections. The parameters outlined in a Connect Azure Synapse Analytics
configuration file include the following:
- DriverName - Required JDBC driver name.
- DriverClassPath - Required JDBC class path.
- MAXPARALLELSTREAMS - Optional maximum number of parallel streams created to load data for performance and according to demand.
- STORAGEACCESSKEY - Required. Azure Blob Storage access key for an active account. If the storage access key is missing or invalid, Connect issues an AZSQDWTERR error message and aborts the job.
- WORKTABLECODEC - Optional compression codec to use to compress files in the staging table. Connect currently supports gzip compression codec only.
- WORKTABLEDIRECTORY - Required. A URL that includes the Blob Storage account name with
the endpoint, including the container name. See https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blobs-introduction#blob-storage-resources.
For example:
WorkTableDirectory=https://<dmxazurestorage>.blob.core.windows.net/dmx-azstorage-containerWhere
<dmxazurestorage>is the blob storage account name and <dmx-azstorage-container> is the container name. If the work table directory is missing or invalid, Connect issues an AZSQDWTERR error message and aborts the job. - WORKTABLESCHEMA - Optional schema name to create the staging data. If this parameter is not set, Connect creates tables in the same schema as the target table.
Defining Azure Synapse Analytics database connections
In the Database Connection dialog, define a connection to an Azure Synapse Analytics
database as follows:
- At DBMS, select Azure Synapse Analytics.
- At Access Method, select JDBC.
- At Database, select a previously defined Azure Synapse Analytics JDBC database connection URL.
- At Authentication, select Auto-detect.
Connect requirements to load data into an Azure Synapse Analytics target
Before using Connect to load data into an Azure Synapse Analytics target, do the following:
- Create or verify that the master database contains a database master key.
- Enable the db_owner privilege for the user connecting to Azure Synapse Analytics.
Alternately, set or verify the following more granular privileges for the connecting
user:
EXEC sp_addrolemember 'db_datawriter', '<user>'; GRANT CONTROL TO <user>;