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

References

• Retrieve files with full path in sql server