SQL Server 2017: Copying SQL Server data from Linux to Windows with SSIS

SQL Server 2017 Integration Services can now run on Linux, which means that you can migrate a package developed on Windows and use ODBC as the connector.

To test this out, we created a simple package on Windows that copied data between a local and remote SQL Server instance and then ran the package on a Linux machine.

The process was simple, the only thing to bear in mind is that you need to create ODBC data sources with the same names on the Windows and Linux machines. The steps for creating our simple test package are as follows:

1. In SQL Server Management Studio, connect to the SQL Server instance from which you want to copy data.
2. Create a simple test table:

   CREATE DATABASE ssis
   USE ssis
   CREATE TABLE Persons (
       PersonID int identity,
       LastName varchar(255),
       FirstName varchar(255),
       Address varchar(255),
       City varchar(255)
   );
   INSERT INTO Persons VALUES ('Erichsen', 'Tom', 'Skagen 21', 'Stavanger')
   

3. Connect to the instance to which you want to copy data. Create the test table but do not populate it.
4. On the machine where Visual Studio is installed create two ODBC data sources, one for each SQL Server instance. Use the 32-bit version of ODBC Data Source Administrator to do this.
5. In Visual Studio, create a new Integration Services Project.
6. Drag a Data Flow Task from the Toolbox to the Control Flow tab.
7. Choose the Data Flow tab.
8. Drag an ODBC Source from the Toolbox to the Data Flow tab, and then press Return.

   ODBC Destination is in the Other Sources list.

9. Select the ODBC Source, and then press Return.
10. In the ODBC Source dialog box, choose New.
11. In the Configure ODBC Connection Manager dialog box, choose New.
12. In the Connection Manager dialog box, choose the ODBC data source that connects to the source SQL Server instance, and then use the OK button to return to the ODBC Source dialog box.
13. Choose Columns.
14. Drag an ODBC Destination from the Toolbox to the Data Flow tab, and then press Return.

    ODBC Destination is in the Other Destinations list.

15. Select the ODBC Source. Drag the blue arrow over to the ODBC Destination.
16. Select the ODBC Destination, and then press Return.
17. In the ODBC Destination dialog box, choose New.
18. In the Configure ODBC Connection Manager dialog box, choose New.
19. In the Connection Manager dialog box, choose the ODBC data source that connects to the destination SQL Server instance, and then use the OK button to return to the ODBC Source dialog box.
20. In the Name of the table or the view list, choose Persons.
21. Choose Mappings.
22. Delete the mapping between the PersonID columns. The PersonID is populated automatically.
23. Install, license and test the 64-bit Linux version of the SQL Server ODBC driver on the machine where the Linux port of SQL Server and SSIS is installed.
24. On the Linux machine, create two SQL Server ODBC data source, with the same names as the data sources 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, SQLSERVER_SAMPLE, if you like, but remember to rename them.

25. Copy the SSIS package from the Windows machine to the Linux machine.
26. To execute the package:

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