8

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.

  • i provided an answer with a Demo app. check it out – Hadi Apr 27 '17 at 20:43
  • Would command line/ PowerShell solution do? – Stoleg May 15 '17 at 13:02
  • Yes, if you have a PowerShell solution, please post an answer with that too, for completeness and posterity. – John G Hohengarten May 15 '17 at 13:50
  • 1
    Your TSQL almost worked, I only got 19 results returned. There were a lot of "Cannot bulk load. The file "<filename>.dtsx" does not exist." errors. – John G Hohengarten May 15 '17 at 16:09
  • 1
    Sorry for late reply, yes, you were correct, they were in sub-folders. Your updated code works! – John G Hohengarten May 15 '17 at 21:42
5
+50

Retrieve dtsx info programmatically

You can read my detailed answer on StackoverFlow:

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

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

  • @JohnGHohengarten check my detailed answer at stackover flow , i provided a link in my answer check it out – Hadi May 12 '17 at 23:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.