THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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:
    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:

image

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:

image

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

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

Comments

 

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.

-Jamie

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:

Hi

How would I get Column names of a particular task

Regards

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?

Thanks.

May 18, 2012 1:20 PM
 

jamiet said:

Vinnep,

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!

Rakshya

May 18, 2012 4:58 PM
 

jamiet said:

Rakshya,

"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 (http://pragmaticworks.com/Products/Business-Intelligence/BIDocumenter/Default.aspx).

Also keep an eye on Project Barcelona http://blogs.msdn.com/b/project_barcelona_team_blog/

Regards

Jamie

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?

Thanks

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:ConnectionManager>

   <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:ObjectData>

     <DTS:ConnectionManager>

       <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>

     </DTS:ConnectionManager>

   </DTS:ObjectData>

 </DTS:ConnectionManager>

Any help greatly appreciated.

April 8, 2013 3:44 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement