How to find version of installed SSIS

1
votes

It is local machine without an actual SQL server (just used for development)

By running DTEXEC it retreived Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.

SQL server 2008R2 is installed with SDK, SSIS, and management studio

Visual studio 2013 is installed but due to some unknown reason SSDT did not provide full features to SSIS (included .dtsx cannot open and .sqlproj cannot open)

No choice but installed a package called SSDTBI for visual studio 2013. Ridiculously only 32 bit application can be installed(my os is 64bit) and it forces SQL server 2014 installed.

However, visual studio 2013 can open .dtsx & .sqlproj

Everything looks fine (ignoring now this local instance contains SQL server 2008R2, 2012 and 2014 from 2008R2 discs, visual studio 2013 & SSDTBI https://www.microsoft.com/en-us/download/details.aspx?id=42313)

Lastly, when I open the .dtsx file (which should be 2008R2 version)

The program will modify the format the XML to other format from

<DTS:Property DTS:Name="PackageFormatVersion">3</DTS:Property>

to

<DTS:Property DTS:Name="PackageFormatVersion">8</DTS:Property>

It is checked means the SSIS package is in SQL server 2014 now.

Thus the questions are How exactly to check the current SSIS version (not SSIS package or SQL server or SSDT)

(Skipped) Is it possible to open SSIS items without the encountered issues? - it is checked that visual studio 2013 is proposed to manage SSIS package with version 8. Thus it is normal behaviors instead of issue (editing SSIS package version 3 should use visual studio 2008 with BI instead)

enter image description here

1
sql-serverssissql-server-data-tools
The SSIS version you have directly relates to the data engine version. SQL Server 2008R2 has SSIS 2008R2. SQL Server 2014 has SSIS 2014. If, however, you have a SQL Server 2008(r2) instance you have to use BIDS in VS 2008. SSDT supports SSIS 2012+, it does not support 2008. – Larnu
Possible duplicate of SQL Server Integration Services Version – Yahfoufi

1 Answers

2
votes

Thus the questions are How exactly to check the current SSIS version (not SSIS package or SQL server or SSDT)

One server can have multiple versions of SSIS installed. This can be caused by in-place upgrades.

Installed Major versions can be discovered by using SQL Server Configuration Manager:

enter image description here

Builds, versions and tools requirements:

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
2017      14        8                   2015/2017

Is it possible to open SSIS items without the encountered issues?

By default, BIDS creates a backup of package during upgrade