I have a dev environment with SQL Server 2008 R2 and SSIS (10.0) installed.
I have a number of SSIS packages built with SQL Server 2008 R2 BIDS and these have been deployed to a Prod server running SQL Server 2017 and SSIS (14.0). The legacy SSIS packages run fine on Prod's SQL Server 2017.
I recently installed SQL Server 2017 & SSIS onto my Dev environment. The plan being to upgrade the SSIS packages in the future. Therefore in my dev environment, I have SQL Server 2008 R2 with SSIS 10.0 and also SQL Server 2017 with SSIS 14.0. Everything seems to co-exist fine.
The reason for my question is that in my dev environment, when I run the 2008 R2 SSIS packages from anywhere (BIDS / SQL Server 2008 R2 / SQL Server 2017) it requires the 'SQL Server Integration Services 10.0' Windows service to be running. If I run the same package on the Prod server which only has SQL Server 2017 and SSIS 14.0 installed it requires the 'SQL Server Integration Services 14.0' Windows service to be running.
Why in Dev is it falling back to use the older SSIS 10.0 Windows service, even when launched from SQL Server 2017? The package is deployed in an identical way onto the Dev SQL Server 2017 and prod SQL Server 2017, yet the Dev install uses SSIS 10.0 and Prod uses 14.0. Is this to be expected? I don't have a huge issue with this, but with Dev using a different SSIS runtime to Prod, I am not guaranteeing a similar SSIS environment for testing.
Is this behaviour to be expected?
Many thanks in advance.
Best Answer
The solution ended up being nothing to do with running different versions of the SSIS Windows service, but how SSIS 14.0 in SQL Server 2017 handles legacy SSIS packages which use configuration files. I use configuration files to store database credentials.
In SQL Server 2008 R2 when you deploy a SSIS package, it also asks you where you want to store the configuration file and the deployed package references that configuration file in the file location you specify.
In SQL Server 2017 when you deploy a legacy SSIS package, it grabs the contents of the configuration file and stores it within the deployed project. The configuration parameters are then configurable via the SSMS SSIS GUI.
Here's the gotcha, in SQL Server 2017, if the configuration file exists in the original file location when the SSIS deployment package was built, it uses the contents of that configuration file in preference over any configuration defined in the SSIS package node of SQL Server 2017. If the configuration file is removed/renamed from the original file location (when the SSIS deployment package was built), it then falls back to using the configuration defined via the GUI in the SSIS package node of SQL Server 2017.
This doesn't make a whole heap of sense, but this is definitely what is happening.
Hopefully this solution might help someone somewhere!