How Can I Automate Version Number Retrieval from SSIS .dtsx Files?

Automating Version Number Retrieval from .Dtsx Files

Retrieving Package Version from SSIS Packages

If you need to read version information within an SSIS package, you can access 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

Finding Package SQL Server Version

To determine the Package SQL Server Version stored in a .dtsx file:

1. Open the .dtsx file as a text (or XML) document.
2. Search for the PackageFormatVersion property.

Extracting Values from .Dtsx Files

Using SQL Server

Refer to the following resources for SQL queries that retrieve information from .dtsx files stored in SQL Server:

• [bill fellows article - SSIS package query](https://billfellows.com/aa/sspackages/default.htm)
• [Microsoft TechNet article - List all SSIS packages stored in msdb database](https://technet.microsoft.com/en-us/library/ms165744.aspx)

Using a Programmatic Approach

Using Regular Expressions

The following code uses Regex to loop over .dtsx files, extracting package properties, including PackageFormatVersion:

Private Sub ReadPackagesInfo(ByVal strDirectory As String)
Dim regexPattern As String = "(?<=""PackageFormatVersion"">)(.*)(?=</DTS:Property>)"
...
Dim strPackageFormatVersion = Regex.Match(strContent, regexPattern, RegexOptions.Singleline).Value
...

Using an XMLParser

Private Sub ReadPackagesInfoUsingXmlParser(ByVal strDirectory As String)
Dim ns As XNamespace = "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
End If
...

Additional Resources

• [SQL Studies - What SQL version is my SSIS package?](https://blog.sqlauthority.com/2010/09/23/sql-server-what-sql-version-is-my-ssis-package/)
• [MSDN - Package Format Changes in SQL Server Denali](https://docs.microsoft.com/en-us/sql/integration-services/developers/package-format-changes-in-sql-server-denali)

The above is the detailed content of How Can I Automate Version Number Retrieval from SSIS .dtsx Files?. For more information, please follow other related articles on the PHP Chinese website!