This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.
First published on MSDN on Jul 13, 2017Before we start, let us consider below questions:
- As SSDT is not supported on Linux, we have to design SSIS packages on Windows. But do we need to make any changes on these packages before we run them on Linux?
- If we would like to use ODBC to connect any databases like Oracle, MySQL on Linux, is the ODBC configuration the same as Windows?
Ok. In order to answer these questions, let's take a very simple example: move data from MySQL to SQL Server. Both of MySQL and SQL Server are running on different Linux machines. How does it happen? We should follow these steps one by one:
Step1: Design your SSIS package on SSDT.
So far, SSDT only supported Windows platform. All package design and validation happen on Windows machine.
Our example is simple, we only need one OLEDB Destination for SQL Server and ODBC Source for MySQL. Of course, you can add any tasks or transformations between them.
Step2: Configure ODBC connection.
In configuration of ODBC connection, we have two options:
- Configure a DSN and use it in the connection string;
- Or we can write a normal connection string;
The prerequisite of either option is that we need to install the same version MySQL driver for Windows platform. The reason why we should do this is that we design package on Windows by SSDT and SSDT only knows database driver on the same machine.
Step3: Create DSN on Linux if you use DSN in ODBC connection string.
Don't forget to install the same version MySQL driver on Linux. Finally we will run SSIS package on Linux.
By the way, we should install 32 bit driver on Windows, but 64 bit on Linux. That's historic problem of SSDT. We won't discuss it here.
If you use DSN in ODBC connection string, please create the same DSN on Linux.
Step4: Copy your SSIS package to Linux and run.
After above configuration, we can copy the SSIS package to Linux machine by some tools like WinSCP.
In the end, we run SSIS package by Dtexec.
Conclusion
Although we only can develop SSIS packages on Windows and run it on Linux, package with ODBC connections can be seamlessly migrated from windows to Linux without any changes. What you should do is to install ODBC driver on both Windows and Linux with the same version.