自动从 .Dtsx 文件中检索版本号

2021-12-30 00:00:00 sql etl sql-server ssis ssis-2012

有什么方法或查询可以让我找到 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
    

相关文章