How Can I Extract Package Version Information from DTSX Files?

Package Information Extraction from DTsx Files

To determine package versions for multiple.DTsx files stored in a directory, consider the following techniques:

• Using VBA with Regex or XmlParser:
  
  This method involves reading and parsing.DTsx files to extract specific properties, including PackageFormatVersion.


• Using T-SQL Queries:
  
  If the.DTsx files are stored in SQL Server's msdb database, you can retrieve package versions using dedicated queries. However, this method requires manual navigation to the specific database location of the.DTsx files.

However, extracting package version information for files not stored in SQL Server requires programmatic approaches. Here's a detailed perspective on each method:

Accessing Package Version Information Programmatically

To automate the retrieval of package versions, here are two programming approaches using:

1. VBA with Regex:

This method leverages regular expressions to parse.DTsx files and extract the PackageFormatVersion and other package properties. A custom class is defined to represent this information. Here's a code snippet illustrating this approach:

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

2. VBA with XML Parser:

This method utilizes an XML parser to access.DTsx file content and extract the relevant properties. The code parses the XML document and retrieves PackageFormatVersion using namespaces and attributes. Here's a code snippet showcasing this technique:

<br>Dim ns As XNamespace = "www.microsoft.com/SqlServer/Dts"<br>Dim man As XmlNamespaceManager = New XmlNamespaceManager(New NameTable())<br>man.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts")<br>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</p>
<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">strPackageFormatVersion = xml.Root.Descendants(ns + "Property").Attributes(ns + "Name").Where(Function(x) x.Value = "PackageFormatVersion").FirstOrDefault.Parent.Value

Copy after login

End If

Additional Information:

• Demo Application:
  The author has created a demo application to automate the process of extracting package versions. It can be downloaded from the provided Dropbox link.
• TSQL Query:
  The author's answer on DBA.StackExchange provides a TSQL query to determine PackageFormatVersions for.DTsx files in a folder.
• PackageFormatVersion Table:
  The response includes a table mapping SQL Server versions, build numbers, and corresponding PackageFormatVersions.

The above is the detailed content of How Can I Extract Package Version Information from DTSX Files?. For more information, please follow other related articles on the PHP Chinese website!