从自动化文件.dtsx程序版本号检索从自动化文件.dtsx程序版本号检索(Automate Vers

2019-05-12 07:39发布

站内文章 / 后端开发
45 0 0

有没有一种方法或查询通过我可以找到SSIS包（* .dtsx程序文件）的版本号？

我有*.dtsx在我的Team Foundation Server的，我想知道的文件。

手动的方法是做一个鼠标包装上单击鼠标右键，然后单击Compare看VersionBuild但也有像数以千计的包，这样做手工实在是不可能的

注：该过程应该是自动，手动不

Answer 1:

获得DTSX包内的值

  • 如果你尝试读取这个包内的包版本，你可以访问到SSIS的一个系统变量 

     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.

  • 如果您正在寻找包的SQL Server版本，你可以找到它里面dtsx文件，如果你打开它的文本（或XML）和搜索PackageFormatVersion属性，在下面的链接提供了详细信息：

    • SQL研究-什么版本的SQL是我的SSIS包？ （此链接包含以下writen表）
    • MSDN -在SQL Server Denali车型封装格式更改

从.dtsx程序文件获取的值存储在SQL Server

您可以按照以下链接：

  • 比尔·费洛斯文章- SSIS包查询
  • 微软TechNet文章-列出存储在msdb数据库中的所有SSIS包

它包含了实现这一问题的查询

从.dtsx程序文件获取值不存储在SQL Server

要自动读取PackageFormatVersion你可以用它编程方式使用阅读XMLParser或Regex 。 我写在Vb.net代码使用Regex和遍历.dtsx文件的目录中，并得到了PackageFormatVersion财产和DTSX文件头中发现的其他特性：

  • PackageFileName
  • PackageFormatVersion
  • 创立日期
  • CreationName
  • CreatorComputerName
  • CreatorName
  • DTSID
  • ExecutableType
  • LastModifiedProductVersion
  • 的LocaleID
  • 对象名
  • 包装类型
  • VersionBuild
  • VersionGUID

第一i-创建名为类PackageInfo包含上面列出的属性

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

使用正则表达式 

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

下面的代码行是阅读的一个PackageFormatVersion从文件属性

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

使用XML解析器 

    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

演示应用

我创建了一个演示应用程序来实现这一过程中，您可以从以下链接下载：

  • Dropbox的下载链接

此外，我创建了一个新的Git存储库的这个演示程序

应用截图

从.dtsx程序文件使用TSQL获取值

您可以在DBA.StackExchange阅读我的答案：

  • 确定多个.dtsx程序的PackageFormatVersion文件夹中的文件打包

PackageFormatVersion表

这里是PackageFormatVersion表值

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


Answer 2:

如果你使用的项目部署解决方案（如果您有SSISDB在您的服务器），您可以使用此查询： 

SELECT 
   [name],
   [package_format_version]
FROM [SSISDB].[catalog].[packages];

注：集成服务必须安装



Answer 3:

.ispac文件的zip文件实际上，每个.dtsx是它里面的所有必要信息的XML文件。 另外要回答这个朋友发送XML本身容纳所有的信息你想要的。

我希望这可以帮助你 ：）



文章来源: Automate Version number Retrieval from .Dtsx files
标签： sql sql-server ssis etl ssis-2012
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~