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