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

Collecting information about your SSIS packages [SSIS Nugget]

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:

dtsx package XML ssis

and clicking on that result opens up the following:

dts package xml screenshot ssis

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)
 
dtsx properties xml ssis
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)

All tasks in a dtsx package file
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:
 
ssis package statistics information

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
Published Sunday, October 18, 2009 11:08 PM 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

 

Vivek said:

Thanks Jamie..This is the exact information I was researching.

October 19, 2009 8:04 AM
 

Jez said:

Shame there isn't an *easy* way to extract from the db.  

October 19, 2009 10:22 AM
 

jamiet said:

Hi Jez,

I'm not sure I understand what you mean. Which DB?

-Jamie

October 19, 2009 10:45 AM
 

John Welch said:

Very nice - I can envision some interesting uses for this.

October 19, 2009 11:13 AM
 

jamiet said:

John,

I'll look forward to seeing your contributions then shall I? :)

-Jamie

October 19, 2009 11:29 AM
 

Creighton said:

You can also extract this information when your packages are deployed to msdb by substituting this query as your derived table.

SELECT  

Name,

CAST(CAST(CAST([packagedata] as varbinary(max)) as varchar(max)) as XML) pkgXML

FROM [msdb].[dbo].[sysssispackages]  

October 19, 2009 1:30 PM
 

Bruce said:

I am getting an error:

Cannot assign a default value to a local variable

I assume you are using SQL 2008 since it is not compatible with t-sql SQL 2005.

I had to explicitly declare and set the variable to get it to work. And it's a great nugget!

October 19, 2009 6:08 PM
 

jamiet said:

Bruce,

Yes, that was an oversight on my part, a stupid one! Really really sorry about that - I'm glad you were easily able to find the problem though!

-Jamie

October 19, 2009 6:50 PM
 

Brian said:

Great code.  This is just 1 field away from being perfect for our shop.  Before I dig in do you have any hints on pulling out a package level variable?

October 28, 2009 2:30 PM
 

Siddharth Mehta said:

It would be good to have this in some SSRS report that consumes this as a stored procedure, and using the Custom Reporting Feature of SSMS, the same can be used to keep a track of the packages in a typical development environment.

October 28, 2009 2:54 PM
 

jamiet said:

Siddarth,

Great idea! Worth blogging about perhaps?

-Jamie

October 28, 2009 3:44 PM
 

jamiet said:

Brian,

That may be a bit more difficult and I don't have the answer off the top of my head. If you find it out let me know!!

-Jamie

October 28, 2009 3:44 PM
 

Siddharth Mehta said:

I am attending today's SQL Server User Group meeting. Let's discuss it today if you get some time, and please let me take this opportunity to post about this article and extending it on my blog http://siddhumehta.blogspot.com :)

October 29, 2009 7:11 AM
 

jamiet said:

Siddharth,

Cool. See you later on today then! Seek me out - I should be easy to find, I'll be the guy stood up at the front presenting :)

-Jamie

October 29, 2009 7:15 AM
 

Brian said:

This is not elegant or probably all that efficient but is a start of how to get package variables.  Next steps probably include creating a table to house the results then pivot and keep only the common variables.

declare @counter int

     , @cmd varchar(max)

     , @url varchar(100)

     , @q char(1)

     , @totvars int

set @url = 'www.microsoft.com/SqlServer/Dts'

set @counter = 0

set @q = char(39)

set @totvars = 25

set @cmd = ''

while @counter < @totvars

begin

 set @counter = @counter + 1

 select @cmd = 'select * from (select SUBSTRING(PackagePath,LEN(PackagePath) - CHARINDEX(' + @q + '\' + @q +',REVERSE(PackagePath),0)+2,LEN(PackagePath)) AS PackageName

             , ' + cast(@counter as varchar(2)) + ' as cnt

             ,   PackageXML.value(' + @q + 'declare namespace DTS="www.microsoft.com/SqlServer/Dts";

                                   /DTS:Executable[1]/DTS:Variable[' + cast(@counter as varchar(2)) + ']/DTS:Property[@DTS:Name="ObjectName"][1]' + @q + ',' + @q + 'nvarchar(500)' + @q + ') AS varName

             ,   PackageXML.value(' + @q + 'declare namespace DTS="www.microsoft.com/SqlServer/Dts";

                                   /DTS:Executable[1]/DTS:Variable[' + cast(@counter as varchar(2)) + ']/DTS:VariableValue[1]' + @q + ',' + @q + 'nvarchar(500)' + @q + ') AS varValue

from  pkgStats ) a '

print (@cmd)

exec (@cmd)

end

October 29, 2009 4:07 PM
 

Jamie Thomson said:

On Thursday (29/10/09) evening I did a presentation at the London SQL Server User Group entitled “Deploying

October 31, 2009 3:12 AM
 

tamzyn said:

Excellent work Jamie. I'm using the table this produces in my Deployment method to take all the SSIS packages from dev to test to production in the SSIS MSDB.

Cheers

Tamzyn

November 4, 2009 8:52 PM
 

Jamie Thomson said:

A few weeks ago I published a blog entitled Collecting information about your SSIS packages which demonstrated

November 6, 2009 8:58 PM
 

Steve Wilson said:

Jamie,  now, can you read the package information out of MSDB and not the file system?  Our team is puting together policies and we would like to check deployed SSIS package connection strings for included passwords.  Our deployed packages reside in the MSDB and not on the file system.  

January 20, 2010 4:47 PM
 

jamiet said:

Steve,

I expect so, yeah. I can never remmeber where they're stored in msdb but if they're stored in a column of type XML then it shouldn't be too much of a jump to adapt the code above to read out of that column instead.

-Jamie

January 20, 2010 5:06 PM
 

Pulkit said:

Hello Jamie,

Can you please provide me with information\query on how to pull the information from already MSDB deployed SSIS packages (like package name, package folder, package tasks, package variables, connection managers etc)?

Thanks

Pulkit

August 20, 2010 11:45 AM
 

zaini said:

Hello Jamie,

Can you inform to me, how to know tables that use a store procedure from ssis packages or another tool.

thanks

Zaini

zainidwtr@yahoo.com

March 28, 2011 4:13 AM
 

jamiet said:

zaini,

I'm sorry, I don't understand the question. Could you rephrase?

JT

March 28, 2011 4:20 AM
 

jamiet said:

June 6, 2012 12:09 PM
 

Tiago Sumita said:

Great article, Thanks!

July 2, 2012 2:31 PM
 

tables used by SSIS packages said:

how do you get the object data...i am looking to capture the tables used by SSIS packages

September 10, 2012 12:03 PM
 

Bala said:

Great Article..When i ran the powershell script, it didnt include the files(.dtsx) directly under MSDB.

September 12, 2012 2:45 AM
 

Evila said:

Jamie thanks for the wonderful article. I am using this to extract all the SQL Tasks in a given SSIS package. I have how ever run into a situation where I cannot make this happen with SQL Server 2000 since it uses DTS and not SSIS.

Do you know a simpler way to get the dts package in dtsx format without having to export it to an sql server 2005/2008 ?

Would be worth getting this done as a batch job since I've got about 200 dts packages.

December 5, 2012 12:33 AM
 

jamiet said:

Hi Evila,

I'm afraid I have no idea about dts files. From memory (I'm going back about 8 years) they are compiled binary files hence extracting information from them is bound to be difficult.

regards

Jamie

December 5, 2012 7:00 AM
 

Gautam said:

Thanks Jamie for such a wonderful article, this helped me a lot.

Can you please share the query to get the information about Data Flow components also.

Thanks

Gautam

April 3, 2013 2:41 AM
 

Megha said:

I don't know why I'd use this...however it's good to know that something like this exists...

Your blog has wealth of knowledge..

Thanks for this and all other articles.

May 6, 2013 1:04 AM
 

Anshuman saini said:

HI,

Can i read all the column name from dtsx file from source to destionation, if yes please tell me how  ?

Regards,

Anshuman Saini

May 15, 2013 6:53 AM
 

Brian Ambrose said:

Any way to use the same method to extract compent level information?  I'm trying to query all connection managers so I can retrieve the source code query.

August 26, 2013 2:12 PM
 

Shair said:

Brain - you're reading my mind! We're changing servers and I have to find all SSIS packages that are linked to a specific server.  I found this article and hoped there was a way to gather the connection info without going package-by-package.

August 29, 2013 2:07 PM
 

Dinesh said:

I can't find connectionstring properties and some other properties missing. Could you please help me.

October 8, 2013 8:14 AM
 

Meraj said:

Thanks for posting this.  I cant access your sky drive to download the script could you plerase help.

October 18, 2013 4:32 PM
 

jamiet said:

Hi,

Hmm...that's kind of annoying. Try this link instead https://skydrive.live.com/#cid=550F681DAD532637&id=550F681DAD532637%2111737

Regards

JT

October 18, 2013 5:31 PM
 

Meraj said:

Thank you sir that link worked.

October 21, 2013 10:12 AM
 

Meraj said:

Hi agian and thanks again for your help.  I had one other question for you.  Your "Name and type of every task in a package" script is realy what I'm after becuase what I need is to find all dtsx packages that have an Send mail task.  However I have many packages that I need to cursor though and when I try to use a wild card on the:

rom    openrowset(bulk 'C:\Test\*.dtsx',single_blob) as pkgblob

I get:

Cannot bulk load because the file "C:\Test\*.dtsx" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).

Also is there a way to extract the content from the "To", "from", "body" , Subject"... of the send email task?

October 21, 2013 10:35 AM
 

jamiet said:

Hi meraj,

I'm afraid I don't know why you're getting that error.

"Also is there a way to extract the content from the "To", "from", "body" , Subject"... of the send email task?"

I'm sure there is a way, yes. You're going to have to discover what XPath expression to use in order to extract them. I find a tool called XMLPad to be invaluable in building XPath expressions so i recommend you read this post I wrote on XMLPad http://sqlblog.com/blogs/jamie_thomson/archive/2011/02/11/xmlpad-a-new-tool-in-my-developer-utility-belt.aspx

Regards

Jamie

October 22, 2013 6:03 AM
 

Sharma said:

Thanks Jamie,

I have facing an issue where one query is consuming high TempDB. The query is being run from around 300 ssis packages. I am not able to find from which package

Can you help me in customizing the above code to find the keywords of that query from all the packages

December 18, 2013 5:54 AM
 

Balaji said:

Hi, Its was a great code.. Thanks for sharing.. Similarly is there any way we can get the package name and corresponding tables used in it??

Thanks in advance!!

February 26, 2014 11:24 AM
 

Vaibhav GUPTA said:

Is there any way to get information about all connections\database used in all DTS packages using T SQL

March 5, 2014 3:21 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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