Running an SSIS package on Linux

SQL Server 2017 is the first version of the database to include a Linux port, which the SQL Server ODBC driver also supports.

SQL Server 2017 Integration Services (SSIS) has recently been ported to Linux, and the rest of this blog will show you how to migrate an SSIS package from Windows and run it on Linux.

The SSIS package in question imports product records from a CSV file into the Salesforce Products2 table. The Windows package creation process is described in this article.

To migrate the package from Windows to Linux:

1. Install, license, and test the 64-bit Linux version of the Salesforce ODBC driver on the machine where you have installed the Linux port of SQL Server and SSIS.
2. On the Linux machine, create a Salesforce ODBC data source, with the same name as the data source you created on Windows.

   On Linux, you create ODBC data sources by editing a text file, normally stored in /etc/odbc.ini. You can adapt the sample data source, SF_SAMPLE, if you like, but remember to rename it. To migrate the data source described in the Windows article, you would need an entry in odbc.ini like:

   [Salesforce]
   Description=Easysoft ODBC-SalesForce Driver
   Driver=Easysoft ODBC-SalesForce
   uri=https://login.salesforce.com/services/Soap/u/34
   user=myuser@mydomain.com
   password=p455w0rd
   token=ABCDEF123456

3. FTP the SSIS package and CSV file from the Windows machine to the Linux machine.

   Note We chose binary transfer mode for both files. Normally you'd choose ascii transfer mode for the CSV file, but when we did this the package execution failed with "The end of the data file was reached while reading header rows." Perhaps this is something Microsoft will address in the future or there is an alternative approach, but either way this is how we worked around the problem.

4. You need to mirror the directory structure where the CSV file is stored on Windows on the Linux machine, omitting the drive letter and substituting \ for /. In our case, the CSV file was stored in:

   C:\Users\Administrator\Documents

   so we created this directory structure on Linux:

   /Users/Administrator/Documents

5. To execute the package:

   export PATH=/opt/ssis/bin:$PATH
   dtexec /F "MyPackage.dtsx"