Before you begin
Set up a connection to Microsoft Azure Synapse Analytics if you don’t have an existing connection that you can use.
Use optimized bulk load (recommended for larger imports)
This import type is ideal for large data volumes. It aggregates data before loading, reducing the number of batch inserts and improving performance. integrator.io splits the data into fixed-size files, stages them in your Azure Data Lake Storage Gen2 (ADLS Gen2) storage account, and loads them into Azure Synapse Analytics with the COPY INTO command. By default, all files load together. To load each file as soon as it’s created, check Load data as available.
On a connection that can use it, new imports start with this query type selected. On any other connection, new imports start with Use batch insert SQL query selected. Existing imports keep their query type.
Additionally, integrator.io automatically generates the insert and update statements based on the destination table and primary keys, so you can update existing data without a separate flow.
-
Destination table: Select the destination table into which the data must be inserted in either of these ways:
- By validated table name: Begin typing the table name to filter tables in the dedicated SQL pool database configured on your connection. Because dedicated SQL pools don’t support cross-database operations, only tables in the connected database appear.
- By referencing a handlebars expression that identifies the destination table: Open the handlebars editor for the Destination table setting to open Build destination table, and enter an expression that returns the table name. The editor lists the resources you can reference.
-
Primary keys: Select the primary keys so that your data can be matched on these fields and then updated or inserted in the destination table. If your table already has primary keys, they appear here automatically. You can select one or more fields. They uniquely identify each record in your table. After you create the import, map the fields you selected. Every primary key must be mapped.
- If you leave Primary keys blank, integrator.io inserts every record. Inserting without primary keys can lead to duplicate records.
- If you select Primary keys, integrator.io updates existing rows and inserts new ones.
- Each primary key value must appear only once in a batch. integrator.io doesn’t remove duplicates. If a batch contains the same key more than once, the import fails with an error that names the key columns and sample values. Remove duplicates before this step or in your Override merge/insert statement.
- Every row in the batch must have a value for every selected primary key. If a row has a null or empty primary key value, the batch fails before it changes the destination table. The check doesn’t run when you check Override merge/insert statement.
-
Override merge/insert statement: Check this box to replace the default statement with your own load logic. Write it with handlebars in the SQL box or the Advanced Field Editor (AFE). The duplicate key check doesn’t run when you check this box. Override statements run exactly as written, so wrap a multi-statement override in
BEGIN TRAN … COMMITto avoid a partial apply. - Load data as available: Check this box to load each file as it’s created. If you leave the box unchecked, integrator.io loads all ready files together. With the box unchecked, if any file or row fails, the whole batch rolls back, no records are written to the destination table, and all records in the batch appear as errors. With the box checked, each file loads as its own transaction. If a file fails, only that file rolls back, and its records appear as errors. Files that already loaded stay in the destination table.
When optimized bulk load is unavailable
Use optimized bulk load (recommended for larger imports) stays visible but disabled when the connection can’t use it, and a message under it names the reason:
- The connection’s Auth type is Database user. Change it to Azure service principal, which also shows the Storage account, Container, and Folder path settings.
- The connection has no staging settings. Add Storage account, Container, and Folder path to the connection.
For the connection settings, see Set up a connection to Microsoft Azure Synapse Analytics.
Data limits
These limits apply to optimized bulk load. Use batch insert SQL query for tables that hit them.
| Limit | Detail |
|---|---|
| Binary columns | Destination columns of type binary or varbinary aren’t supported. Azure Synapse can store binary data, but optimized bulk load can’t load it. If a mapped column is binary, the import fails before any data is staged, with an error that names the column. Use batch insert SQL query for that table, or remove the column from the mapping. |
| Value size | Each varchar(max) or nvarchar(max) value can be at most 1 MB. See COPY INTO in the Microsoft documentation. |
| Temporary space | Records are staged in temporary space on your SQL pool. A very large batch on a small pool can use all of it, and the load fails. For very large loads, check Load data as available so each file loads separately. |
Run-time errors
These errors can appear while an import that uses optimized bulk load runs.
| Error code | Error message | Fix |
|---|---|---|
synapse_null_primary_key |
“<count> row(s) in the batch have no value for primary key column(s) <columns>.” | One or more records have a null or empty value in a primary key column. Populate the primary key in your source or mapping, or change the primary keys on the import. |
synapse_duplicate_keys_in_batch |
“The batch contains at least <count> duplicated primary key value(s) on column(s) <columns>: <sample>.” | Multiple rows in the same batch share a primary key value. Remove the duplicates from your source data, or handle them in your Override merge/insert statement. |
synapse_binary_column_unsupported |
“Column <columnName> is a <sqlType> type; Synapse optimized bulk load does not support binary columns yet.” | A mapped destination column is binary, varbinary, or image. Remove the column from the mapping, or select Use batch insert SQL query. |
synapse_copy_timeout |
“COPY INTO did not finish within <timeoutDuration>.” | The SQL pool took too long to load the staged files. Check Load data as available so files load in smaller pieces, or scale up the dedicated SQL pool. |
synapse_apply_outcome_unknown |
“The outcome of the apply statement on the SQL pool is unknown.” | The connection to the SQL pool dropped or timed out after the insert or update statement was sent. Check the destination table before you run the import again, to avoid duplicate rows in tables without primary keys. |
synapse_staging_not_configured |
“Bulk load on Azure Synapse requires an ADLS Gen2 staging location on the connection. Configure the storage account, container and folder path, then test the connection.” | Add Storage account, Container, and Folder path to the connection, then test it. |
synapse_stage_failed |
“Failed to stage <fileName> to <path> after <attempts> attempt(s): <reason>” | Run the flow again. |
synapse_header_mismatch |
“The header of staged file <fileName> does not match the mapped columns. Re-run the flow; contact support if this persists.” | Run the flow again; contact Celigo support if it repeats. |
synapse_staged_file_check_failed |
“The staged files in <path> could not be verified (<detail>). The run folder is cleaned up; re-run the flow.” | Run the flow again; contact Celigo support if it repeats. |
synapse_copy_rowcount_mismatch |
“COPY INTO loaded <actual> row(s) into the staging table, expected <expected>. The staged files may have been modified during the load.” | Run the flow again; contact Celigo support if it repeats. |
Use batch insert SQL query
Inserts records into the table in batches. Use it for new records when you don’t need custom SQL. integrator.io builds the insert query for you automatically for each batch of records. Each batch’s default number of records bound is 100, but you can use the Batch size setting to tune your imports as needed. There is a query limit of 100KB, so for larger batch sizes, the number of records imported in a single request is adjusted to the size limit.
Note: For batch insert, if you have data types such as money, numeric, decimal, smalldatetime, then you must check if the values to be inserted are within the accepted range of the destination column. If the values are out of range, use another data type that supports the required range. To verify the range based on the server version used, see Data types (Transact-SQL).
Use SQL query once per record
Execute a SQL query once for each record. You can write your SQL command in the SQL query text field. Click Edit to the right of the text field to open the SQL Query Builder AFE.
Tip: If you’re familiar with SQL, enter your query in the box. If you would like to use handlebars, click the handlebars editor icon to open the Handlebars editor and create your SQL query. To build your SQL query easily and quickly, you can ask Celigo AI to generate a query. Enter a description of your query in plain English in the space provided, and Celigo AI will attempt to generate a query based on your prompt.
Use SQL query once per page of records
Execute a SQL query once per page of data. You can write your SQL command in the SQL query text field. Click Edit to the right of the text field to open the SQL Query builder AFE.
Use SQL query on first page only
Use this option if you want to run a SQL command once per flow run. The SQL will execute as soon as the first page of data reaches the flow step. This option should be used carefully to avoid race conditions in your flows.