Did you know that is is possible to read the contents of a SSIS package (i.e. a .dtsx file) from within SQL Server Management Studio (SSMS) using T-SQL? For example, take the following T-SQL snippet:
select cast(BulkColumn as XML)
from openrowset(bulk 'C:\tmp\MyPkg.dtsx',
single_blob) as pkgColumn;
It uses OPENROWSET to return the contents of a specified package (C:\tmp\MyPkg.dtsx) as an XML document. Here is a screenshot showing what this returns:
and clicking on that result opens up the following:
That’s what the inards of a .dtsx file look like. In other words we now have a queryable XML document representing a package, thereafter you’re limited only to what you can do with XQuery which is quite a lot. Now, I’m no XQuery expert by any means but I did manage to find a few uses for this. Here are some sample queries:
All properties of a package
SELECT Props.Prop.query('.') as PropXml
, Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
string(./@p1:Name)','nvarchar(max)') as PropName
, Props.Prop.value('.', 'nvarchar(max)') as PropValue
FROM (
SELECT CAST(pkgblob.BulkColumn AS XML) pkgXML
FROM OPENROWSET(bulk 'C:\tmp\MyPkg.dtsx',single_blob) AS pkgblob
) t
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:Property') Props(Prop)
Name and type of every task in a package
SELECT Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
./p1:Property[@p1:Name=''ObjectName''][1]','nvarchar(max)') as TaskName
, Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
./@p1:ExecutableType','nvarchar(max)') as TaskType
FROM (
select cast(pkgblob.BulkColumn as XML) pkgXML
from openrowset(bulk 'C:\tmp\Package.dtsx',single_blob) as pkgblob
) t
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
//DTS:Executable[@DTS:ExecutableType!=''STOCK:SEQUENCE''
and @DTS:ExecutableType!=''STOCK:FORLOOP''
and @DTS:ExecutableType!=''STOCK:FOREACHLOOP''
and not(contains(@DTS:ExecutableType,''.Package.''))]') Pkg(props)
Note that this one will also return all tasks that exist in eventhandlers and it ignores how “deep” a task is in the container hierarchy.
Putting it all together
Ok, that’s all pretty cool but it would be nice to combine it all together and get a summary of many packages, perhaps all of the packages on your machine. Hence I’ve put together a T-SQL script that will display summary information about all of the packages in a folder and its subfolders. Here’s the results of running that script on my dev machine:
217 packages – I’ve collected lot of them over the years! This shows a sampling of some of the information that it is possible to collect:
- Package name
- Original creator of the package
- Package Type (signifies which version of BIDS was used to originally build it)
- Version numbers
- Number of tasks in the package
Want to know what your most complex package might be? Simply order this dataset in descending order of [NumberOfTasks]. Or maybe you want to know which of your developers has built the most packages – the answers are right here!
Of course, this could be extended to capture much much more information than what I have captured here. You may want to know how many eventhandlers each of your packages has, when the packages were created, or perhaps how many components are in your dataflows. The (SSIS) world is your oyster!!!
One thing that I thought would be very useful would be to stick this script into a SQL Agent job, run it on a daily basis, and insert the results into a history table thereby giving you a running history of all the packages in your system and when they changed. If anyone does do that let me know how it goes!
To execute the script simply open it and change the following line as appropriate:
DECLARE @Path VARCHAR(2000) = 'C:\*.dtsx';
Note that you will need to enable [xp_cmdshell] in order to run the script which is available on my SkyDrive at:
If you experience any problems with it let me know. If you adapt it any way let me know that too because writing these XQuery statements is no easy task (believe me!!!) and it would be great to share that stuff with other people!
@JamieT
Update: The original version of this script only worked on SQL Server 2008. I have now updated it so that it works on SQL2005 also! Thanks to Bruce in the comments for alerting me to this fact/
Update 2: Found a few more issues and hence have uploaded another new version (same link still works though). Changes:
- Script will now work on a server with a case-sensitive collation
- xp_cmdshell is turned on at the top of the script
- An error message that could get returned by the command-line call under certain circumstances wasn't getting handled. It is now!
Update 3: An updated version with bug fixes and new features is now available at
SSIS Package Stats Collector version 2