ODBC

Skyvia allows you to connect to different data sources via ODBC - a standard API to access database management systems. This allows connecting Skyvia to a number of data sources, not supported directly, via the corresponding ODBC driver.

ODBC connections can work only via Agents. ODBC driver must be installed on the same computer, on which the Agent application is installed.

ODBC drivers exist for many major data sources and even for local files, like local Excel workbooks and CSV files.

ODBC Connection Limitations

ODBC connections are not supported in synchronization and replication packages, because the data source you connect may have no fields, storing record creation and modification time.

ODBC connections also do not support Backup and Connect.

In import packages with the ODBC connection as a target Skyvia does not support using Returning feature to return generated values for the result records. The same limitation applies to the Insert action.

How to Create ODBC Connection

Requirements

Skyvia Agent bitness must correspond the bitness of the respective ODBC driver. Note that Skyvia Agent installs both 64 bit and 32 bit versions on 64 bit systems, so you can run the corresponding Agent app version.

Creating Connection

To create an ODBC connection, you need to either specify either a DSN or a Connection String (optionally, with Secret Connection String). If you specify DSN, Connection String and Secret Connection String settings are ignored.

ODBC Connection

DSN (Data Source Name) - the name of an ODBC data source (system or user data source, preconfigured on your computer. An ODBC data source is a combination of the data source itself (a database or a file) and the connection information, required to access it. On Windows you create data sources in ODBC Data Source Manager. You can find more information on Data Source configuration in Microsoft Documentation.

User data sources can be accessed only if the Agent application runs on behalf of the corresponding user. If the Agent app is running as a service, you can use only system DSNs.

Connection String - the ODBC driver connection string with settings including the driver name and connection parameters, required for establishing the connection. It consists of a list of keyword=value pairs, separated by semicolons. Here are several examples of connection strings:

1
2
3
4
5
6
7
8
9
10
11
12
13

Driver={SQL Server};Server=(local);Trusted_Connection=Yes;Database=AdventureWorks;

Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;Persist Security Info=False;Tusted_Connection=Yes

Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\bin\Northwind.mdb

Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\bin\book1.xls

Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\bin

Driver={Devart ODBC Driver for MySQL};User ID=root;Password=test;Data Source=db;Database=demobase;Port=3310

DSN=dsnname

Note that Connection String can be viewed and edited later by users who have access to the workspace, containing the connection. If the connection string contains sensitive parameters, like passwords or tokens, it’s better to move them into Secret Connection String.

Secret Connection String - the concealed part of the connection string for storing parameters like passwords, tokens, etc. Once it is specified, it cannot be obtained or viewed on Skyvia. While you can specify any connection parameters here, it is intended for storing sensitive parameters. For example, if your ODBC connection string includes password, it’s better to move it to the Secret Connection String, so it will be like Password=test;