问题描述

有什么方法或查询可以让我找到 SSIS 包(*.dtsx 文件)的版本号?

我的 Team Foundation Server 中有我想知道的 *.dtsx 文件.

手动方法是在包上单击鼠标右键，然后单击 Compare 以查看 VersionBuild 但有数千个包，因此手动操作是真的不可能

注意:这个过程应该是自动化的，而不是手动的

在 dtsx 包中获取值

• 如果您尝试读取此包中的包版本，您可以访问其中一个 SSIS

  使用 TSQL 从 .dtsx 文件中获取值

  你可以在 DBA.StackExchange 阅读我的回答:

  • 确定文件夹中多个 .DTSX 包文件的 PackageFormatVersion

  PackageFormatVersion 表

  这里是 PackageFormatVersion 表值

  SQL Version Build # PackageFormatVersion Visual Studio 版本2005 9 2 20052008 10 3 20082008 R2 10.5 3 20082012 11 6 2010 或 BI 20122014 12 8 2012 CTP2 或 20132016 13 8 2015

  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 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:

  • 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
  

  这篇关于自动从 .Dtsx 文件中检索版本号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！

  The End

  ---