Search code examples
sql-serverssissql-server-data-tools

How to find version of installed SSIS

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

Solution

  • 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