summary
Help Page for ETL Configuration of Data Setting to SQLServer.
constraints
- none in particular
Setting items
STEP1 Basic settings
| item name | indispensable | default value | Contents |
|---|---|---|---|
| Microsoft SQL Server Connection Configuration | Yes | - | Select the previously registered Connection Configuration that has the necessary permissions for this ETL Configuration. |
| Database Name | Yes | - | Specify the Database Name for Data Destination. |
| schema | No | - | Specify the name of the Data Destination schema. |
| table | Yes | - | Specify the name of the Data Destination table Custom Variables can also be used to dynamically determine the value of a setting during ETL Configuration of TROCCO's data. |
| transfer mode | Yes | insert | Select the appropriate transfer mode among insert, insert_direct, truncate_insert, replace, and merge. For details on the mode, see About Transfer Mode below. |
| merge key | No | - | If the transfer mode is merge and the primary key does not exist in the Data Destination table, specify the key column name of the data to be transferred. |
About transfer mode
| mode name | Contents |
|---|---|
| insert | First, intermediate tables are created and data is transferred. After all intermediate tables have been created, insert data into the target tables. If the target table does not exist, it will be created automatically. |
| insert_direct | Inserts rows directly into the target table. If the target table does not exist, it will be created automatically. However, when the transfer fails, data may have been inserted into the target table. |
| truncate_insert | In insert mode, all contents of the target table are deleted before data is inserted into the table. |
| replace | First, intermediate tables are created and data is transferred. Once the intermediate table has been created, delete the target table and rename the intermediate table to the target table name. However, when a transfer fails, the target table may be deleted. |
| merge | First, intermediate tables are created and data is transferred. After all intermediate tables have been created, an update is performed if the merge key for the data in the intermediate tables already exists in the target table, otherwise an insert is performed. If the target table does not exist, it will be created automatically. |
STEP2 Detailed settings
| item name | default value | Contents |
|---|---|---|
| Maximum number of retries | 12 | The maximum number of retries can be specified. |
| Retry wait time (ms) | 1000 | You can specify the waiting time for retries. |
| Maximum retry wait time in milliseconds | 1800000 | You can specify the maximum time to wait for a retry. |
| batch size | 16777216 | Batch size can be specified. |
| Default time zone | UTC | You can specify a default time zone. |
| Connection timeout (seconds) | 300 | You can specify a timeout in seconds before the driver connects. When set to 0, the default for Microsoft SQL Server is 15 seconds. |
| Socket timeout (seconds) | 1800 | You can specify a timeout in seconds before the query is executed. When set to 0, there is no timeout. |