SQL Server Integration Services Version

Question

I have a production system which had installed SQL Server 2012, so this system has the Integration Services 11. Then we upgraded the system with SQL Server 2014, so the system had two Integration Services 11 & 12. Then we upgraded to SQL Server 2016, so the system has now three Integration Services 11, 12 & 13.

I done some research on the Internet and i realized that even we upgrade the SQL Server the integration services are installed side-by-side. This is also confirmed from SQL Server Management Console.

When i run DTEXEC on CMD get default version 11, this means that my DTSX packages inside the SSISB Catalog are running using the Integration Services 11 and not the latest one? If this is correct how can i change this to the newest one?

Answer 1

I think the previous answer provides some very useful information. However, I think I understand the misconception here:

First, there is NO benefit or advantage of "changing this to the newest one". Some people think that SSIS 2016 (11) is better than SSIS 2014 (10) or lower - the fact is that , with SSIS2012 nothing changed except addition of several new features and a new way to deploy items. You will not obtain any speed or performance enhancement. It's just that you get access to a few extra components.

Second, this will never fail because of my answer above. Firstly, SSIS is backward compatible (which is what Microsoft brags about - but honestly, why wouldn't it be?? it's the exact same product sold under different titles, why would it NOT be backward compatible). Secondly, your previous editions of SSIS are there for those like a few features (such as ActiveX) to run in compatibility mode. All in all, I think the question is answered by the fact that SSIS is backward compatible and that NOTHING has changed in the past 3 editions of the product (2012, 2014 and 2016 are nearly identical, and no performance or architectural changes exist between 2008/2008R2 and 2012+)

If you'd like more information, have a look at this link, which starts off by stating:

SQL Server 2014 Integration Services (SSIS) can co-exist side-by-side with SQL Server 2008 Integration Services and SQL Server 2012 Integration Services.

Answer 2

I was recently part of such discussion. It is important to understand SSIS Backward compatibility before.

Also, refer SSIS through its SQL Server Data Tools version, as most commonly people understand it in that way. You can link your thoughts with below mentioned details:

SQL      Build#  PackageFormatVersion   Visual Studio Version(SSDT)
2005       9        2                   2005
2008      10        3                   2008
2008 R2   10.5      3                   2008
2012      11        6                   2010 or BI 2012
2014      12        8                   2012 CTP2 or 2013
2016      13        8                   2015

source: sqlstudies

From this microsoft document you'll understand which SSIS version is compatible with which SQL Server.

Briefly:

Target version of SQL Server    Development environment for SSIS packages
2016                            SSDT 2015
2014                            SSDT 2015 or SSDT-BI 2013
2012                            SSDT 2015 or SSDT-BI 2012  

In simple words, SSDT 2015 is capable of deployment on any server from 2012 onwards, whereas 2012, 2013 are restrictive.

Beware that, if you open sql server 2012 dtsx in SSDT 2015, then you cannot go back to open it from SSDT-BI 2012.

If this is correct how can i change this to the newest one?

Hence, if you want to upgrade all your packages, open them through SSDT-2015, select deployment version(right click project -> properties -> Target Server Version).

you can read more about these changes here(MSSQLTips)