Version number Retrieval from .Dtsx files

-

PackageFormatVersion Table


When Opening a DTSX package with a Text Editor it contains a property name PackageFormatVersion 

 <DTS:Property DTS:Name="PackageFormatVersion">3</DTS:Property>

This Property contains a value that indicate the version of SQL Server related to this Package, also it could indicate the Visual Studio Version used to develop this package.

 After Reading this Article From SQLStudies i found this Table

SQL VersionBuild #PackageFormatVersionVisual Studio Version
200592   2005
2008103   2008
2008 R210.53   2008
2012116   2010 or BI 2012
2014128   2012 CTP2 or 2013
2016138   2015
To Retrieve FormatVersion from multiple dtsx file.

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:

Getting values from .dtsx files stored in Sql server

You can follow these links:
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:
Also i created a new Git-repository for this demo app
App screenshot
enter image description here

Getting values from .dtsx files Using TSQL

Retrieve dtsx PackageFormatVersion using TSQL

I wrote an SQL query that get files from a specific directory, filter on *.dtsx file then read PackageFormatVersion property from them.
--Result Table
CREATE TABLE #TblResult (filepath varchar(MAX) , packageformatversion INT)

--Get Files From Directory

Declare @files table (FileName nvarchar(4000))

--Get files fullpath
declare @myPath nvarchar(4000) =  'C:\Users\Admin\Desktop\Stack Overflow';

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
   id int IDENTITY(1,1)
   ,subdirectory nvarchar(512)
   ,depth int
   ,isfile bit
   , ParentDirectory int
   ,flag tinyint default(0));

-- top level directory
INSERT #DirectoryTree (subdirectory,depth,isfile)
   VALUES (@myPath,0,0);
-- all the rest under top level
INSERT #DirectoryTree (subdirectory,depth,isfile)
   EXEC master.sys.xp_dirtree @myPath,0,1;


UPDATE #DirectoryTree
   SET ParentDirectory = (
      SELECT MAX(Id) FROM #DirectoryTree
      WHERE Depth = d.Depth - 1 AND Id < d.Id   )
FROM #DirectoryTree d;

-- SEE all with full paths
WITH dirs AS (
    SELECT
       Id,subdirectory,depth,isfile,ParentDirectory,flag
       , CAST (null AS NVARCHAR(MAX)) AS container
       , CAST([subdirectory] AS NVARCHAR(MAX)) AS dpath
       FROM #DirectoryTree
       WHERE ParentDirectory IS NULL 
    UNION ALL
    SELECT
       d.Id,d.subdirectory,d.depth,d.isfile,d.ParentDirectory,d.flag
       , dpath as container
       , dpath +'\'+d.[subdirectory]  
    FROM #DirectoryTree AS d
    INNER JOIN dirs ON  d.ParentDirectory = dirs.id
)
insert into @files (filename)
SELECT dpath FROM dirs 
WHERE subdirectory like '%.dtsx'


DECLARE @file NVARCHAR(4000)

--Loop over dtsx files

DECLARE csr CURSOR FOR SELECT [FileName] FROM @files

OPEN csr

FETCH NEXT FROM csr INTO @file

WHILE @@fetch_status <> - 1
BEGIN

--Read xml from dtsx file

CREATE TABLE #TblTemp (data varchar(MAX));

DECLARE @strQuery NVARCHAR(4000)

SET @strQuery = 'BULK INSERT #TblTemp
   FROM ''' + @file + '''
   WITH 
      (
         ROWTERMINATOR = ''''
      )'

EXEC(@strQuery)

--Get PackageFormatVersion 

INSERT INTO #TblResult (filepath, packageformatversion)
SELECT @file, SUBSTRING(data  
                 ,CHARINDEX('DTS:Name="PackageFormatVersion">',data,1) + LEN('DTS:Name="PackageFormatVersion">') 
                 ,CHARINDEX('<',SUBSTRING(data,CHARINDEX('DTS:Name="PackageFormatVersion">',data,1) + LEN('DTS:Name="PackageFormatVersion">'), 3) ,1) - 1)

FROM #TblTemp


DROP TABLE #TblTemp

FETCH NEXT FROM csr INTO @file

END

CLOSE csr
DEALLOCATE csr

--Read Result

SELECT DISTINCT * FROM #TblResult

--Drop temp Table

DROP TABLE #TblResult
Result looks like
enter image description here
References

Popular posts from this blog

SSIS - Script Task error: Exception has been thrown by the target of an invocation.

-
Question: When i execute the SSIS package the Script Task throws the following exception: Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() Which is meaningless. How can i read the real error message? Answer: Exception has been thrown by the target of an invocation. Is an general exception that is thrown by Script T
Read more

Don’t install Hadoop on Windows!

-
Image
A few years ago, I was hearing from my colleagues “don’t ever think about installing Hadoop on Windows operating system!” . I was not convinced of this saying because I am a big fan of Microsoft products, especially Windows. In the past three years, I worked on three projects where I was asked to build a Hadoop cluster on Ubuntu. The first time, it was a single-node Hadoop installation with a single Apache Spark worker. The other projects were about building a Big Data ecosystem for radiation data engineering , where the multi-node Hadoop cluster is deployed. Besides Hadoop, we installed and configured Apache Kafka, Spark, Hive, Pig, and Flume installations (I have published some installation guides previously, you can check the links at the end of this article). It was hard to become familiar with those technologies for the first time since they don’t have much documentation online. Each time I had to install Hadoop, I was thinking that why always Hadoop is installed on Linu
Read more

SSIS - script component DateTime Formats Implicit Conversion

-
General Info These are the default formats of datetimes datatypes (when converting from string) DT_DBDATE yyyy-mm-dd DT_FILETIME yyyy-mm-dd hh:mm:ss:fff DT_DBTIME hh:mm:ss DT_DBTIME2 hh:mm:ss[.fffffff] DT_DBTIMESTAMP yyyy-mm-dd hh:mm:ss[.fff] DT_DBTIMESTAMP2 yyyy-mm-dd hh:mm:ss[.fffffff] DT_DBTIMESTAMPOFFSET yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm] Note: DT_DATE and DT_DBTIMESTAMP has the same SET method And i think that converting string to date is depending also on your current culture info More detailed information are found here https://msdn.microsoft.com/en-us/library/ms141036.aspx http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations Experiments : I didn't find any related article on this issue so i made the following experiments: SSIS Implicit datetime Conversion i created a SSIS package with a Dataflowtask. in this dataflowtask i created a Script Component (as a Source) and a Flat File Destin
Read more