Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
This article outlines how to use the copy activity in a data pipeline to copy data from an ODBC data source.
Prerequisites
To use this connector, set up an on-premises data gateway. For more information, see How to access on-premises data sources in Data Factory.
Supported configuration
For the configuration of each tab under copy activity, go to the following sections respectively.
General
Refer to the General settings guidance to configure the General settings tab.
Source
The following properties are supported for ODBC under the Source tab of a copy activity.
The following properties are required:
- Connection: Select an ODBC connection from the connection list. If the connection doesn't exist, then create a new ODBC connection.
- Use query: You can choose either Table or Query as your use query. The following list describes the configuration of each setting.
- Table: Select the table in your database from the drop-down list. Or check Edit to enter your table name manually.
- Query: Specify the custom SQL query to read data. For example:
SELECT * FROM MyTable.
Under Advanced, you can specify the following fields:
Query timeout (minutes): Specify the timeout for query execution, default is 120 minutes. If a parameter is set for this property, allowed values are logic, for example
02:00:00(120 minutes).Additional columns: Add additional data columns to store source file's relative path or static value. Expression is supported for the latter.
Destination
The following properties are supported for ODBC under the Destination tab of a copy activity.
The following properties are required:
Connection: Select an ODBC connection from the connection list. If no connection exists, then create a new ODBC connection.
Table: Select the name of the table in the ODBC data source.
Under Advanced, you can specify the following fields:
Pre-copy script: Specify a SQL query for the copy activity to execute before writing data into destination table in each run. You can use this property to clean up the preloaded data.
Write batch timeout: Specify the wait time for the batch insert operation to complete before it times out. The allowed value is timespan. For example, "00:30:00" (30 minutes).
Write batch size: Specify the number of rows to insert into the SQL table per batch. The allowed value is integer (number of rows). A new batch will be created when the current batch reaches the write batch size. The default value is
0(auto-detected).Max concurrent connections: Specify the upper limit of concurrent connections established to the data store during the activity run. Specify a value only when you want to limit concurrent connections. The allowed value is integer.
Mapping
For Mapping tab configuration, see Configure your mappings under mapping tab.
Settings
For Settings tab configuration, see Configure your other settings under settings tab.
Table summary
The following tables contain more information about the copy activity in ODBC.
Source information
| Name | Description | Value | Required | JSON script property |
|---|---|---|---|---|
| Connection | Your connection to the source data store. | < your ODBC connection > | Yes | connection |
| Use query | The way to read data from ODBC. Apply Table to read data from the specified table or apply Query to read data using SQL queries. | / | Yes | / |
| Table | Name of the table in the ODBC data source. | < table name > | No | tableName |
| Query | Use the custom SQL query to read data. For example: SELECT * FROM MyTable. |
< SQL queries > | No | query |
| Query timeout (minutes) | The wait time before the query attempt times out. | < timespan > | No | queryTimeout |
| Additional columns | Add additional data columns to store source file's relative path or static value. Expression is supported for the latter. | • Name • Value |
No | additionalColumns: • name • value |
Destination information
| Name | Description | Value | Required | JSON script property |
|---|---|---|---|---|
| Connection | Your connection to the destination data store. | < your ODBC connection > | Yes | connection |
| Table | Name of the table in the ODBC data source. | < table name > | Yes | tableName |
| Pre-copy script | A SQL query for the copy activity to execute before writing data into destination table in each run. You can use this property to clean up the pre-loaded data. | < your pre-copy script > | No | preCopyScript |
| Write batch timeout | The wait time for the batch insert operation to finish before it times out. The allowed value is timespan. | < timespan > | No | writeBatchTimeout |
| Write batch size | The number of rows to insert into the SQL table per batch. The allowed value is integer (number of rows). | < integer > 0 (default) |
No | writeBatchSize |
| Max concurrent connections | The upper limit of concurrent connections established to the data store during the activity run. The allowed value is integer. | < integer > | No | maxConcurrentConnections |