First published on MSDN on Jun 16, 2017
Dear all,
We just announced https://blogs.msdn.microsoft.com/ssis/2017/05/17/ssis-helsinki-is-available-in-sql-server-vnext-ctp2-1/ , 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.
For Linux ODBC driver manager installation, you can refer to http://www.unixodbc.org/ or https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-driver-manager . unixODBC version 2.3.4 or newer is supported
For SQL Server Linux ODBC driver installation, refer to this https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server .
For MySQL Linux ODBC driver installation, refer to this https://dev.mysql.com/downloads/connector/odbc/ .
You can use command isql shipped with unixODBC driver manager to validate the driver manager and driver installed, refer to http://www.unixodbc.org/doc/UserManual/ 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.
Dear all,
We just announced https://blogs.msdn.microsoft.com/ssis/2017/05/17/ssis-helsinki-is-available-in-sql-server-vnext-ctp2-1/ , 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 https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication for more detail on how to configure integration authentication for Linux ODBC.
For Linux ODBC driver manager installation, you can refer to http://www.unixodbc.org/ or https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-driver-manager . unixODBC version 2.3.4 or newer is supported
For SQL Server Linux ODBC driver installation, refer to this https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server .
For MySQL Linux ODBC driver installation, refer to this https://dev.mysql.com/downloads/connector/odbc/ .
You can use command isql shipped with unixODBC driver manager to validate the driver manager and driver installed, refer to http://www.unixodbc.org/doc/UserManual/ 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.
Published Mar 25, 2019
Version 1.0
