Automate Version number Retrieval from .Dtsx files

Question

Is there a way or query by which I can find the version number of SSIS packages (*.dtsx files)?

I have the *.dtsx files in my Team Foundation Server for which I wanted to know.

The manual way is to do a mouse right-click on the package and click Compare to see the VersionBuild but there are like thousands of packages so doing it manually is really not possible

Note: The process should be automated, not manual

Take a look here: http://billfellows.blogspot.ca/2011/08/ssis-package-query.html (from this answer: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/04ca0a47-e514-434f-a92f-35864e16facd/how-to-determine-latest-version-of-ssis-deployed-to-msdb?forum=sqlintegrationservices) — Alex, Apr 24 '17 at 15:16
Thanks for the link @Alex this seems little related but I have the *.dtsx file in my TFS for which I wanted to know. The manual way is to right click on the package and click compare to see the VersionBuild but there are like thousands of packages so doing it manually is really not possible. — saurabh sharma, Apr 24 '17 at 15:44
Your best bet is to write a C# console program that reads all the DTSX files for that property and compiles a list of their versions + names. It's just an XML file, so this will help you: http://www.c-sharpcorner.com/article/reading-and-writing-xml-in-C-Sharp/. You could then write the values to a text file. The property you want to read is this one: http://www.techbrothersit.com/2014/09/ssis-how-to-find-version-of-ssis.html — Alex, Apr 24 '17 at 15:52

Hadi · Accepted Answer · 2017-05-16T15:11:16.783

Getting values within dtsx packages

If you are trying to read a package version within this package you can access to one of the SSIS system variables

Variable        Type    Description
-------------------------------------------
VersionBuild    Int32   The package version.
VersionComment  String  Comments about the package version.
VersionGUID     String  The unique identifier of the version.
VersionMajor    Int32   The major version of the package.
VersionMinor    Int32   The minor version of the package.

If you are looking for Package SQL Server Version, you can find it inside the dtsx file if you open it as text (or xml) And search for PackageFormatVersion property, detailed informations are provided in the following links:
- SQL Studies - What SQL version is my SSIS package? (this link contains the table writen below)
- MSDN - Package Format Changes in SQL Server Denali

Getting values from .dtsx files stored in Sql server

You can follow these links:

it contains queries that achieve this issue

Getting values from .dtsx files not stored in Sql server

To automate reading PackageFormatVersion you can use read it programmatically using an XMLParser or Regex. I wrote a code in Vb.net that use Regex and loop over .dtsx files inside a directory and get the PackageFormatVersion property and other properties found in dtsx file header:

PackageFileName
PackageFormatVersion
CreationDate
CreationName
CreatorComputerName
CreatorName
DTSID
ExecutableType
LastModifiedProductVersion
LocaleID
ObjectName
PackageType
VersionBuild
VersionGUID

First i created a Class named PackageInfo that contains properties listed above

Public Class PackageInfo

    Public Property PackageFileName As String
    Public Property PackageFormatVersion As String
    Public Property CreationDate As String
    Public Property CreationName As String
    Public Property CreatorComputerName As String
    Public Property CreatorName As String
    Public Property DTSID As String
    Public Property ExecutableType As String
    Public Property LastModifiedProductVersion As String
    Public Property LocaleID As String
    Public Property ObjectName As String
    Public Property PackageType As String
    Public Property VersionBuild As String
    Public Property VersionGUID As String


End Class

Using RegEx

Private Sub ReadPackagesInfo(ByVal strDirectory As String)


        m_lst.Clear()

        For Each strFile As String In IO.Directory.GetFiles(strDirectory, "*.dtsx", IO.SearchOption.AllDirectories)


            Dim strContent As String = ""

            Using sr As New IO.StreamReader(strFile)

                strContent = sr.ReadToEnd
                sr.Close()

            End Using


            Dim strPackageFormatVersion As String = Regex.Match(strContent, "(?<=""PackageFormatVersion"">)(.*)(?=</DTS:Property>)", RegexOptions.Singleline).Value
            Dim strCreationDate As String = Regex.Match(strContent, "(?<=DTS:CreationDate="")(.*?)(?="")", RegexOptions.Singleline).Value
            Dim strCreationName As String = Regex.Match(strContent, "(?<=DTS:CreationName="")(.*?)(?="")", RegexOptions.Singleline).Value
            Dim strCreatorComputerName As String = Regex.Match(strContent, "(?<=DTS:CreatorComputerName="")(.*?)(?="")", RegexOptions.Singleline).Value
            Dim strCreatorName As String = Regex.Match(strContent, "(?<=DTS:CreatorName="")(.*?)(?="")", RegexOptions.Singleline).Value
            Dim strDTSID As String = Regex.Match(strContent, "(?<=DTS:DTSID="")(.*?)(?="")", RegexOptions.Singleline).Value
            Dim strExecutableType As String = Regex.Match(strContent, "(?<=DTS:ExecutableType="")(.*?)(?="")", RegexOptions.Singleline).Value
            Dim strLastModifiedProductVersion As String = Regex.Match(strContent, "(?<=DTS:LastModifiedProductVersion="")(.*?)(?="")", RegexOptions.Singleline).Value
            Dim strLocaleID As String = Regex.Match(strContent, "(?<=DTS:LocaleID="")(.*?)(?="")", RegexOptions.Singleline).Value
            Dim strObjectName As String = Regex.Match(strContent, "(?<=DTS:ObjectName="")(.*?)(?="")", RegexOptions.Singleline).Value
            Dim strPackageType As String = Regex.Match(strContent, "(?<=DTS:PackageType="")(.*?)(?="")", RegexOptions.Singleline).Value
            Dim strVersionBuild As String = Regex.Match(strContent, "(?<=DTS:VersionBuild="")(.*?)(?="")", RegexOptions.Singleline).Value
            Dim strVersionGUID As String = Regex.Match(strContent, "(?<=DTS:VersionGUID="")(.*?)(?="")", RegexOptions.Singleline).Value



            m_lst.Add(New PackageInfo With {.PackageFileName = strFile,
                      .PackageFormatVersion = strPackageFormatVersion,
                      .CreationDate = strCreationDate,
                      .CreationName = strCreationName,
                      .CreatorComputerName = strCreatorComputerName,
                      .CreatorName = strCreatorName,
                      .DTSID = strDTSID,
                      .ExecutableType = strExecutableType,
                      .LastModifiedProductVersion = strLastModifiedProductVersion,
                      .LocaleID = strLocaleID,
                      .ObjectName = strObjectName,
                      .PackageType = strPackageType,
                      .VersionBuild = strVersionBuild,
                     .VersionGUID = strVersionGUID})


        Next



End Sub

The following line of code is the one that read the PackageFormatVersion property from the file

Dim strA As String = Regex.Match(strContent, "(?<=""PackageFormatVersion"">)(.*)(?=</DTS:Property>)", RegexOptions.Singleline).Value

Using Xml Parser

    Private Sub ReadPackagesInfoUsingXmlParser(ByVal strDirectory As String)

        m_lst.Clear()

        For Each strFile As String In IO.Directory.GetFiles(strDirectory, "*.dtsx", IO.SearchOption.AllDirectories)

            Dim strPackageFormatVersion As String = ""
            Dim strCreationDate As String = ""
            Dim strCreationName As String = ""
            Dim strCreatorComputerName As String = ""
            Dim strCreatorName As String = ""
            Dim strDTSID As String = ""
            Dim strExecutableType As String = ""
            Dim strLastModifiedProductVersion As String = ""
            Dim strLocaleID As String = ""
            Dim strObjectName As String = ""
            Dim strPackageType As String = ""
            Dim strVersionBuild As String = ""
            Dim strVersionGUID As String = ""


            Dim xml = XDocument.Load(strFile)

            Dim ns As XNamespace = "www.microsoft.com/SqlServer/Dts"
            Dim man As XmlNamespaceManager = New XmlNamespaceManager(New NameTable())
            man.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts")

            If Not xml.Root Is Nothing AndAlso
                Not xml.Root.Descendants(ns + "Property").Attributes(ns + "Name") Is Nothing AndAlso
                     xml.Root.Descendants(ns + "Property").Attributes(ns + "Name").Where(Function(x) x.Value = "PackageFormatVersion").Count > 0 Then

                strPackageFormatVersion = xml.Root.Descendants(ns + "Property").Attributes(ns + "Name").Where(Function(x) x.Value = "PackageFormatVersion").FirstOrDefault.Parent.Value

                strCreationDate = If(xml.Root.Attributes(ns + "CreationDate").FirstOrDefault Is Nothing, "", xml.Root.Attributes(ns + "CreationDate").FirstOrDefault.Value)
                strCreationName = If(xml.Root.Attributes(ns + "CreationName").FirstOrDefault Is Nothing, "", xml.Root.Attributes(ns + "CreationName").FirstOrDefault.Value)
                strCreatorComputerName = If(xml.Root.Attributes(ns + "CreatorComputerName").FirstOrDefault Is Nothing, "", xml.Root.Attributes(ns + "CreatorComputerName").FirstOrDefault.Value)
                strCreatorName = If(xml.Root.Attributes(ns + "CreatorName").FirstOrDefault Is Nothing, "", xml.Root.Attributes(ns + "CreatorName").FirstOrDefault.Value)
                strDTSID = If(xml.Root.Attributes(ns + "DTSID").FirstOrDefault Is Nothing, "", xml.Root.Attributes(ns + "DTSID").FirstOrDefault.Value)
                strExecutableType = If(xml.Root.Attributes(ns + "ExecutableType").FirstOrDefault Is Nothing, "", xml.Root.Attributes(ns + "ExecutableType").FirstOrDefault.Value)
                strLastModifiedProductVersion = If(xml.Root.Attributes(ns + "LastModifiedProductVersion").FirstOrDefault Is Nothing, "", xml.Root.Attributes(ns + "LastModifiedProductVersion").FirstOrDefault.Value)
                strLocaleID = If(xml.Root.Attributes(ns + "LocaleID").FirstOrDefault Is Nothing, "", xml.Root.Attributes(ns + "LocaleID").FirstOrDefault.Value)
                strObjectName = If(xml.Root.Attributes(ns + "ObjectName").FirstOrDefault Is Nothing, "", xml.Root.Attributes(ns + "ObjectName").FirstOrDefault.Value)
                strPackageType = If(xml.Root.Attributes(ns + "PackageType").FirstOrDefault Is Nothing, "", xml.Root.Attributes(ns + "PackageType").FirstOrDefault.Value)
                strVersionBuild = If(xml.Root.Attributes(ns + "VersionBuild").FirstOrDefault Is Nothing, "", xml.Root.Attributes(ns + "VersionBuild").FirstOrDefault.Value)
                strVersionGUID = If(xml.Root.Attributes(ns + "VersionGUID").FirstOrDefault Is Nothing, "", xml.Root.Attributes(ns + "VersionGUID").FirstOrDefault.Value)
            End If



            m_lst.Add(New PackageInfo With {.PackageFileName = strFile,
                      .PackageFormatVersion = strPackageFormatVersion,
                      .CreationDate = strCreationDate,
                      .CreationName = strCreationName,
                      .CreatorComputerName = strCreatorComputerName,
                      .CreatorName = strCreatorName,
                      .DTSID = strDTSID,
                      .ExecutableType = strExecutableType,
                      .LastModifiedProductVersion = strLastModifiedProductVersion,
                      .LocaleID = strLocaleID,
                      .ObjectName = strObjectName,
                      .PackageType = strPackageType,
                      .VersionBuild = strVersionBuild,
                     .VersionGUID = strVersionGUID})

        Next

    End Sub

Demo App

I Created A Demo Application to achieve this procedure you can download it from the following link:

Dropbox download link

Also i created a new Git-repository for this demo app

App screenshot

Getting values from .dtsx files Using TSQL

You can Read my answer at DBA.StackExchange :

Determine the PackageFormatVersion for multiple .DTSX packages files in a folder

PackageFormatVersion Table

And Here is the PackageFormatVersion table values

SQL Version Build # PackageFormatVersion    Visual Studio Version
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

Thanks for the solution Hadi, I have never worked in Vb but I can try your solution. Also, Just to add that my all packages are in server not on local, I access them via Visual Studio. I was just wondering how can I use vb code to get the required info from server. — saurabh sharma, Apr 27 '17 at 13:46
The code i provided is a function that get a directory as arguments. Loop over dtsx files and get their versions into a list. — Hadi, Apr 27 '17 at 13:56
@saurabhsharma files in tfs are mapped to a local path so changes are made local until they are checked in. So in the team explorer in visual studio right click on a package and click on `Open containing folder` — Hadi, May 14 '17 at 08:53

score 5 · Answer 2 · edited May 29 '17 at 01:58

5

You can use this query if you use project deployment solution (If you have SSISDB in your server) :

SELECT 
   [name],
   [package_format_version]
FROM [SSISDB].[catalog].[packages];

Note: Integration Services must be installed

edited May 29 '17 at 01:58

Yahfoufi

2,220
1
22
41

answered Apr 24 '17 at 17:30

Ardalan Shahgholi

11,967
21
108
144

I don't know why I have gotten the negative point?!? But still I think that you should deploy all your packages to SSIS and then run the above query. – Ardalan Shahgholi Apr 24 '17 at 20:30
1

I gave you a vote too. Pretty simple if you have saved it in the SSISDB. The more I use SSISDB the more I like it. – Jacob H May 12 '17 at 20:15
1

@JacobH SSISDB is pretty good, but the question is about automating this process? i don't think this query is what the OP is asking for – May 12 '17 at 22:12
@lahmbajin the question is a way to query for the version number of the package. When the package is stored in the SSISDB, the version is stored in the catalog.packages table. Just a select statement. Put it in whatever automation you need (log, sproc, etc.) just as you would any select statement. – Jacob H May 13 '17 at 01:40
1

@JacobH if you read the OP comments ` I have the *.dtsx file in my TFS for which I wanted to know.` , he is not talking about dtsx stored in sql server – May 13 '17 at 05:56
2

@lahmbajin Deploy temporary your project to SQL Server and run this query to find version of all package that you have. – Ardalan Shahgholi May 14 '17 at 01:35
@ArdalanShahgholi it is simpler to use a third-party application like the one provided in Hadi answer – May 14 '17 at 07:11
@ArdalanShahgoli Even if the OP accepted my answer. i upvoted your answer because it is also a good way to solve the issue. – Hadi May 16 '17 at 16:04
@ArdalanShahgholi i provided an answer to a similar question at DBA site check it out https://dba.stackexchange.com/questions/162133/determine-the-packageformatversion-for-multiple-dtsx-packages-files-in-a-folder – Hadi May 19 '17 at 15:15

score 2 · Answer 3 · answered May 09 '17 at 11:20

2

.ispac file is zip file actually and every .dtsx is XML file with all required information inside it. additionally to answer that friends sent,XML itself hold all of information that you want.

I hope this helps you :)

answered May 09 '17 at 11:20

Yashar Aliabbasi

2,663
1
23
35

2

the OP said `The process should be automated, not manual.`, so we are searching for a way to automate reading this values from the xml. Take a look at Hadi answer, he is in the right way, but i am searching for the optimal method if it exists – Yahfoufi May 09 '17 at 11:25
What is `.ispac` ? Does it have a relation with this question? – May 14 '17 at 17:33
2

`.ispac` is deployed file of SSIS package @lahmbajin – Yashar Aliabbasi May 14 '17 at 18:51
@YasharAliabasi upvoted ur answer. Because it is where we have to start. Take a look at my answer you can consider it as the next step to your answer. Good luck – Hadi May 22 '17 at 15:50