自动从 .Dtsx 文件中检索版本号 [英] Automate Version number Retrieval from .Dtsx files
本文介绍了自动从 .Dtsx 文件中检索版本号的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
有什么方法或查询可以让我找到 SSIS 包(*.dtsx 文件)的版本号?
我的 Team Foundation Server 中有我想知道的 *.dtsx 文件.
手动方法是在包上单击鼠标右键，然后单击 Compare 以查看 VersionBuild 但有数千个包，因此手动操作是真的不可能
注意:这个过程应该是自动化的，而不是手动的
解决方案
在 dtsx 包中获取值
如果您尝试读取此包中的包版本，您可以访问其中一个 SSIS
使用 TSQL 从 .dtsx 文件中获取值
你可以在 DBA.StackExchange 阅读我的回答:
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 2015Is there a way or query by which I can find the version number of SSIS packages (*.dtsx files)?
I have the
*.dtsxfiles 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
Compareto see theVersionBuildbut there are like thousands of packages so doing it manually is really not possibleNote: 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
dtsxfile if you open it as text (or xml) And search forPackageFormatVersionproperty, 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
PackageFormatVersionyou can use read it programmatically using anXMLParserorRegex. I wrote a code in Vb.net that useRegexand loop over.dtsxfiles inside a directory and get thePackageFormatVersionproperty 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
PackageInfothat contains properties listed abovePublic 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 ClassUsing 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 SubThe following line of code is the one that read the
PackageFormatVersionproperty from the fileDim strA As String = Regex.Match(strContent, "(?<=""PackageFormatVersion"">)(.*)(?=</DTS:Property>)", RegexOptions.Singleline).ValueUsing 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 SubDemo App
I Created A Demo Application to achieve this procedure you can download it from the following 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 :
PackageFormatVersion Table
And Here is the
PackageFormatVersiontable valuesSQL 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 文件中检索版本号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
