This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.
First published on MSDN on Jun 16, 2017Dear all,
We just announced SSIS on Linux CTP2.1 refresh , in this refresh, besides many other improvements, we add a new feature which are queried by many customers during CTP2.1 - ODBC support.
When the package is designed to use the ODBC connection, it will communicate with the ODBC driver manager and drivers installed on Linux system, the driver manager we have tested is unixODBC, and the drivers we have tested are MySQL and SQL Server ODBC driver, in theory, we support any unicode ODBC driver as long as the driver follows the ODBC specification, ANSI ODBC driver is not supported.
With this feature, it extends the data source support SSIS on Linux, allow user to have similar experience on ODBC connections as windows, user can do the similar ODBC configurations as windows like ODBC logs, DSN and so on. Package with ODBC connections can be seamlessly migrated from windows to Linux without any changes.
To use the Linux driver, during package design, the connection string can either contains DSN or a normal connection string.
- To use DSN, the DSN need to be configured on Linux Driver manager, refer to unixODBC document on how to configure DSN on Linux system.
- To use a normal connection string, it needs contain Linux driver name, for example, this is a sample SQL Server connection string “server=server name or IP;uid=SQL Server Login name;pwd= SQL Server Login password;Driver=linux driver name like {ODBC Driver 13 for SQL Server};database=database name;”, and note the windows authentication is not supported for SSIS on Linux.
- Windows authentication can be used too as long as the system running SQL Server and the system running SSIS are in the same domain. Refer to Using Integrated Authentication on Linux for more detail on how to configure integration authentication for Linux ODBC.
For Linux ODBC driver manager installation, you can refer to unixODBC site or SQL Server site . unixODBC version 2.3.4 or newer is supported
For SQL Server Linux ODBC driver installation, refer to this link .
For MySQL Linux ODBC driver installation, refer to this link .
You can use command isql shipped with unixODBC driver manager to validate the driver manager and driver installed, refer to this link for more detail on how to use isql command.
One note: when using ODBC destination component, we found several drivers do not support array parameters binding ODBC method which means batch size does not take effective for these drivers. In this case, setting TransactionSize to a proper value will help to improve the performance greatly.