Fri, 08 September 2017
SQL Server 2017: Importing CSV Data from Linux to Salesforce with SSIS
SQL Server 2017 is currently available as a release candidate. Shortly after SQL Server 2017 is officially released, we will release a version of our SQL Server ODBC driver that supports this version of SQL Server. A pre-release of the SQL Server ODBC driver that supports the SQL Server 2017 release candidate is available from the Easysoft Support team ().
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:
- 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.
- 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.com] Description=Easysoft ODBC-SalesForce Driver Driver=Easysoft ODBC-SalesForce uri=https://login.salesforce.com/services/Soap/u/34 email@example.com password=p455w0rd token=ABCDEF123456
- 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.
- 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:
so we created this directory structure on Linux:
- To execute the package:
export PATH=/opt/ssis/bin:$PATH dtexec /F "MyPackage.dtsx"