从.Dtsx文件自动检索版本号 [英] Automate Version number Retrieval from .Dtsx files
本文介绍了从.Dtsx文件自动检索版本号的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
是否可以找到SSIS软件包的版本号（* .dtsx文件）？
我有
手动方法是右键单击鼠标在软件包上，然后单击比较以查看 VersionBuild ，但是有成千上万个软件包，因此手动执行实际上不是可能
注意：该过程应该是自动化的，而不是手动的
解决方案
在dtsx软件包中获取值
-
如果您尝试读取此软件包中的软件包版本您可以访问SSIS
使用TSQL从.dtsx文件中获取值
您可以在DBA.StackExchange上阅读我的答案：
PackageFormatVersion表
这是
PackageFormatVersion表值SQL版本Build＃PackageFormatVersion Visual Studio版本
2005 9 2 2005
2008 10 3 2008
2008 R2 10.5 3 2008
2012 11 6 2010或BI 2012
2014 12 8 2012 CTP2或2013
2016 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
*.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屋！
查看全文
