Automate Version number Retrieval from .Dtsx files
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
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
dtsxfile if you open it as text (or xml) And search forPackageFormatVersionproperty, 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:
- bill fellows article - SSIS package query
- Microsoft TechNet article - List all SSIS packages stored in msdb database
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
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
.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 :)