patternsqlMinor

Determine the PackageFormatVersion for multiple .DTSX packages files in a folder

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thefilesfolderformultipledeterminepackageformatversionpackagesdtsx

Problem

Kenneth Fisher put a blog post out about how to determine What SQL Version is My SSIS Package? in April, 2015.

It has a table of which SQL versions map to which PackageFormatVersion of the SSIS package found in the XML metadata. This is useful when looking at 1 single individual package.

I have a folder of about 100 SSIS .DTSX packages that I need to know which SQL Version they all are.

How can I determine, in bulk, what the PackageFormatVersion (i.e. the SQL version) for multiple .DTSX packages in a folder (file system)?

The end goal is to determine which is the proper TFS version to get and implement to put these packages into, as no source control system exists currently. The table Kenneth presents will help me answer this question, but first I need to confirm what the package SQL versions are.

Assume that I do NOT have BIDS nor SSDT installed.

Assume a desired output would be something like this, where pipe designates a new column:

PackageFilename | PackageFormatVersion
--------------------------------------
Package1.dtsx   | 3
Package2.dtsx   | 4

PowerShell, TSQL, 3rd party tools that can crawl a directory structure, or other tools are welcome.

Solution

Retrieve dtsx info programmatically

You can read my detailed answer on StackoverFlow:

Automate Version number Retrieval from Dtsx files

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

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  - 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('',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

References

Retrieve files with full path in sql server

Code Snippets

--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

Context

StackExchange Database Administrators Q#162133, answer score: 5

Revisions (0)

No revisions yet.