SSIS Package Stats Collector version 2

jamiet — Sat, 07 Nov 2009 01:58:24 GMT

A few weeks ago I published a blog entitled Collecting information about your SSIS packages which demonstrated a way that you could use T-SQL and XQuery to derive information about your SSIS packages. Since then I’ve made a few amendments to the script that accompanied that blog post as follows:

New columns

The collected data now includes the following information about your packages:
    PackageId
    PackageXML (The XML that forms the .dtsx file)
    NumberOfContainers
    NumberOfDataflows
    NumberOfConnectionManagers
    NumberOfVariablesInEntirePackage

New feature

Returns a list of non-unique GUIDs and the packages that they appear in. If a GUID appears more than once in the same package it tells us how many times

Bug fixes

If there were no tasks in the package no data would be returned. That is now no longer the case.
Previous version didn’t work on case-sensitive collations, should be OK now.

There are also more comments liberally sprinkled around it now as well!

Here’s a screenshot showing some of those new columns:

Returning [PackageId] as a field is an important addition because we can now very easily see if any of our packages share the same Package Id. Package IDs are supposed to be unique but if they are not then they are a problem waiting to happen and unfortunately Business Intelligence Design Studio (BIDS) doesn’t really protect you against it very well, in fact if you use package templates then its very likely that you have non unique GUIDs somewhere. (“Non unique globally unique identifiers” – that should set the alarm bells ringing if nothing else).

As alluded to above the script now returns a brand new dataset showing all the GUIDs that occur more than once across all of your packages. Now, I’m not just talking about PackageId GUIDs here, every single object in a SSIS package be it a task, container, component, variable, connection manager, column, whatever… has a GUID attached to it and given the healthy amount of copy-and-paste that goes on when developing SSIS packages its a sure bet that some of those GUIDs are going to get repeated somewhere. Of course, its up to you whether you want to do anything about it or not – if you’re a purist then the idea of repeated GUIDs might keep you awake at night although I suspect most people simply won’t care. Still, its there if you need it:

You may wonder why I have the [NumberOfTimeshisGUIDOccursInThisPackage] column in there. Well, its perfectly plausible that the a GUID can reoccur in the same package and in fact when I was testing this earlier on today I had a package that had 6 occurrences of a GUID because it contained 6 near-identical connection managers that had obviously been copied-and-pasted.

That’s it for now. Let me know if this script proves useful to you and let me know if there’s anything you’d like to see added to it. Download the latest version from: http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20091105/SSISPackageStatsCollector%5E_v2.sql. I have also dropped it into SQLMesh.

Looking forward to some feedback (hint hint).

@Jamiet

Collecting information about your SSIS packages [SSIS Nugget]

jamiet — Sun, 18 Oct 2009 21:08:00 GMT

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)

I had a lot of help from @rbarryyoung with this one!

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:

http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20091018/SSISPackageStatsCollector.sql

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

SSIS Junkie : XPath, sql server integration services, xquery, nugget