Azure Synapse SQL Account
Overview
You can use the Azure Synapse SQL account type to connect Azure Synapse SQL Snaps with data sources that use Azure Synapse SQL account.
Prerequisites
None.
Limitations
The Azure Synapse Analytics does not support Batch Insert and therefore does not honor Batch executions.
Known issues
None.
Account settings
-
Suggestion icon (
):
Indicates a list that is dynamically populated based on the configuration.
-
Expression icon (
):
Indicates whether the value is an expression (if enabled) or a static value (if disabled).
Learn more about Using Expressions in SnapLogic.
-
Add icon (
):
Indicates that you can add fields in the field set.
-
Remove icon (
):
Indicates that you can remove fields from the field set.
| Field / Field set | Type | Description |
|---|---|---|
| Label | String | Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if there are more than one of the same Snap in the pipeline. |
| Account properties | Use this fieldset to define the account properties. This field set contains the following fields: | |
| Hostname | String/Expression | Required. The server's address to which you must connect.
Default value: N/A Example: sl-synapse.sql.azuresynapse.net |
| Port number | Integer/Expression | Required. The port number of the database server to which you must connect.
Default value: N/A Example: 1433 |
| Database name | String/Expression | Required. The name of the database to which you must connect.
Default value: N/A Example: Synapse SQL |
| Username | String/Expression | The username to connect to the database.
This username is used as the default username when retrieving connections and must be valid to set up the data source.
Default value: N/A Example: snapadminuser |
| Password | String/Expression | The password used to connect to the data source.
This password is used as the default password when retrieving connections and must be valid to set up the data source.
Default value: N/A Example: P#2,nxu0oiX2&? |
| JDBC JARs | Use this field set to specify the JDBC JAR files to be loaded. Add each JAR file in a separate row. This field set contains the following fields: | |
| JDBC Driver | String | The driver to use for your application.
By default, the latest JDBC driver 12.2.0.jre11 is bundled with the Azure Synapse SQL Account.
However, you can manually upload a custom JAR file.
Default value: N/A Example: mssql-jdbc-11.2.0.jre11.jar |
| JDBC driver class | String | Specify the fully qualified name of the driver class to be used for connecting to the server.
Default value: Example: |
| Trust all certificates | Checkbox | Select this checkbox if all SSL certificates are to be accepted for connecting to the database.
Default status: Deselected |
| Enable error file support | Checkbox | Select this checkbox to specify the directory within the COPY statement where the rejected rows and the corresponding error file should be written.
Default status: Deselected Note: The error file only applies to CSV file formats.
|
| Advanced properties | Use this field set to specify the advanced properties. This field set contains the following fields: | |
| Auto commit | Checkbox | Required.
Select this checkbox to commit each batch immediately after it is executed.
If the Snap fails, only the batch being executed at that moment gets rolled back.
Deselect this checkbox to commit the execution output only after the execution of all batches is complete. If the Snap fails, the entire transaction is rolled back, unless the Snap finds invalid input data before it sends the insert request to the server and routes the error documents to the Error view. Default status: Selected |
| Batch size | Integer | Required. Specify the number of statements to execute at a time.
Important:
Default value: 50 Example: 60 |
| Fetch size | Integer | Required. The number of rows to fetch at a time when executing a query.
Important: Large values could cause the server to run out of memory.
Default value: 100 Example: 80 |
| Max pool size | Integer | Required. The maximum number of connections a pool must maintain at a time.
Default value: 50 Example: 60 |
| Max life time | Integer | Required. The maximum lifetime of a connection in the pool.
Note: Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit.
A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value.
An in-use connection is never retired.
Connections are removed only after they are closed.
Default value: 30 Example: 50 |
| Idle Timeout | Integer | Required. The maximum amount of time a connection is allowed to sit idle in the pool.
A value of Default value: 5 Example: 8 |
| Checkout timeout | Integer | Required. The number of milliseconds to wait for a connection to be available when the pool is exhausted.
Note: If you provide
0, the Snap waits infinitely until the connection is available.
Therefore, we recommend you not to specify 0 for Checkout Timeout.
Default value: 10000 Example: 1000 |
| URL properties | Use this field set to define URL properties to use, if any. This field set contains the following fields: | |
| URL Property Name | String | The name for the URL property if any.
Default value: N/A Example: MAX_CONCURRENCY_LEVEL |
| URL Property Value | String | The value of the URL property.
Default value: N/A Example: 3 |