Automate Version number Retrieval from .Dtsx files

by

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:

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:

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

App screenshot

enter image description here

Getting values from .dtsx files Using TSQL

You can Read my answer at DBA.StackExchange :

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

More Related Contents:

  1. Query a database based on result of query from another database
  2. SSIS Source Format Implicit Conversion for Datetime
  3. How to Map Input and Output Columns dynamically in SSIS?
  4. convert Excel Date Serial Number to Regular Date
  5. SSIS Package not wanting to fetch metadata of temporary table
  6. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  7. SSIS reading LF as terminator when its set as CRLF
  8. Connecting to Oracle Database using Sql Server Integration Services
  9. I dont know sql syntax for my table [closed]
  10. Making a dynamic pivot table but without using the fiddle
  11. Get top 1 row of each group
  12. How to return only the Date from a SQL Server DateTime datatype
  13. Convert Datetime column from UTC to local time in select statement
  14. Why is SQL Server losing a millisecond?
  15. Find a string by searching all tables in SQL Server
  16. How do I split a delimited string so I can access individual items?
  17. Common Table Expression, why semicolon?
  18. T-SQL split string based on delimiter
  19. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  20. Why use Select Top 100 Percent?
  21. How do I find duplicates across multiple columns?
  22. SQL Server UNION – What is the default ORDER BY Behaviour
  23. How to get last 7 days data from current datetime to last 7 days in sql server
  24. Move SQL data from one table to another
  25. Stored procedure that exports data into csv files only exports to one file
  26. Get Start and End Date from Week Number SQL Server
  27. Nested select statement in SQL Server
  28. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  29. How to get the next number in a sequence
  30. How to Select Every Row Where Column Value is NOT Distinct

Leave a Comment