THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

SSIS Package Stats Collector version 2

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:
    PackageXML  (The XML that forms the .dtsx file)

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: I have also dropped it into SQLMesh.

Looking forward to some feedback (hint hint).


Published Saturday, November 07, 2009 2:58 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Siddharth Mehta said:

Great work Jamie. I have a few suggestions:

1) If I am not wrong, this code reads all packages from a particular directory, it would be good to have this code read from the solution file. Not sure if this can be done from T-SQL

2) A table structure can be created to collect all this data in a version controlled manner, after converting this code into a parameterized SP.

3) It would be great if packages can be read from the table where they are deployed on SQL Server, as this code would work only where deployment is on file system or only from development machine or from a single directory location.

My ideas might sound stupid :) Please feel free to educate me wherever needed.

November 7, 2009 8:41 AM

jamiet said:

Hiya Siddharth,

All good ideas.

1) Reading from the solution file. Well actually it reads from a folder and all subfolders (it is recursive). Yes, reading from a solution file would be good though I'd rather other people took this on themselves and adapted the code accordingly if they need it. What I've provided gives you the basic parsing and then interrogation using XQuery - if people want to populate the list of packages from elsewhere - go ahead!

2) Indeed, putting this stuff into a table and keeping a history over it is a great idea. The script can be easily adapted to do that. It could even be scheduled using SQL Agent.

3) Yes, getting stuff from a table would be nice. Again though, if people need to do this then they're free to go ahead and adapt teh script accordingly!

thanks for the comments.


November 7, 2009 8:54 AM

Sidhartha said:

Dear, Is it possible to get the ParentContainerName rather than getting the ParentContainerGUID directly (though there are way around which doesnt make much sense).

Thanks in advance for the help!

November 19, 2010 2:39 PM

SSIS Junkie : Collecting information about your SSIS packages [SSIS Nugget] said:

October 19, 2011 5:37 AM

Nidhin said:


How would I get Column names of a particular task


February 27, 2012 3:16 PM

VinNep said:

Great post! How would I modify this to get all the database names used (in a connection manager) and all the table names used in the db?


May 18, 2012 1:20 PM

jamiet said:


Not sure. If you find out, let us know :-)

May 18, 2012 1:23 PM

VinNep said:

Ok :-)

I am getting an incorrect syntax error in this version of .sql file...

Msg 102, Level 15, State 1, Line 121

Incorrect syntax near 'MERGE'.

Msg 102, Level 15, State 1, Line 133

Incorrect syntax near 's'.

Msg 102, Level 15, State 1, Line 150

Incorrect syntax near 's'.

Msg 102, Level 15, State 1, Line 168

Incorrect syntax near 's'.

Msg 102, Level 15, State 1, Line 186

Incorrect syntax near 's'.

Msg 102, Level 15, State 1, Line 209

Incorrect syntax near 's'.

May 18, 2012 1:37 PM

VinNep said:

Hi Jamie,

On hindsight, isn't there a tool that compares all the db and tables in our instances and finds out what SSIS packages reference them? Also, on a sidenote, please let me know if there is any tool out there for me to get all the database names used (in a connection manager) and all the table names used in the db in SSIS. All your posts are very helpful. Thanks!


May 18, 2012 4:58 PM

jamiet said:


"I am getting an incorrect syntax error in this version of .sql file...

Msg 102, Level 15, State 1, Line 121

Incorrect syntax near 'MERGE'."

What version of SQL Server are you using? That particular script will only work on SQL Server 2008 and above due to the use of MERGE.

"isn't there a tool that compares all the db and tables in our instances and finds out what SSIS packages reference them?"

I don't know of one but I'm sure there are plenty out there. Try BI Documenter from Pragmatic Works (

Also keep an eye on Project Barcelona



May 18, 2012 5:16 PM

sephuss said:

Nice one!

Do you know if it is possible to change all the data source that have certain name, and so change and save the updated connection String?


September 13, 2012 9:35 AM

Clay said:

Great Post.  I am also interested in getting connection manager related info.  Specifically the connection manager name with the connection string, but don't know enough XQuery.  For example, extract"ACG_EDG_DB" and "Data Source=DTS-EODSDB-S2;Initial Catalog=ACG_EDB_DB_P;Provider=SQLNCLI10.1;Integrated Security=SSPI;" from this fragment:


   <DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

   <DTS:Property DTS:Name="ObjectName">ACG_EDB_DB</DTS:Property>

   <DTS:Property DTS:Name="DTSID">{1DB78004-F094-429F-A776-920835216566}</DTS:Property>

   <DTS:Property DTS:Name="Description" />

   <DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property>



       <DTS:Property DTS:Name="Retain">0</DTS:Property>

       <DTS:Property DTS:Name="ConnectionString">Data Source=DTS-EODSDB-S2;Initial Catalog=ACG_EDB_DB_P;Provider=SQLNCLI10.1;Integrated Security=SSPI;</DTS:Property>




Any help greatly appreciated.

April 8, 2013 3:44 PM

Venkat said:

Thanks for sharing your knowledge with us. It helps lots of peoples like me.

I have question here, I am not sure whether it is possible to implement or not.

Consider this scenario, I have one SSIS package is configured in SQL Agent Job. When job started execute based on the scheduled time, it runs perfectly.

In some cases, job is executing longer than expected. At the time, is it possible to identify which task in SSIS package is delaying the execution.

This is similar to performing the debugging in SSIS. I want to just identify the task name from SSMS using t-sql?

Kindly help me. Thanks Jamie Thomson.

March 17, 2015 10:34 AM

Nick Martel-arquette said:

Is it possible to get the object name include in the task that is being executed?

<DTS:Property DTS:Name="ObjectName">SQL_Set_Restart_Parameters</DTS:Property>

I.e. Here is the list of task, the type of task and what is being exectued.



July 21, 2015 11:19 AM

Tony Cruz said:

Does this code handle password protected dtsx packages?

May 9, 2016 9:26 PM

Vinnie said:

Hi jamie, this is a very helpful article and tool, I wish I would have known about it warlier. Quick question - when I run the select that inserts into @pkgStatsBase I get NULL for all columns expcept for PackagePath, PackageFormantversion, PackageType and PackageXML. This prevents any results to @AllPkgStats which to me is the most value of this tool. Would you have any insight about why the NULL's? Is this a function of SSIS version? I am using SSIS 2012

May 30, 2017 2:19 PM

Leave a Comment


This Blog


Privacy Statement