Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DBA On The Go

I've been working in Enterprise IT environments for roughly 10 years. My move to become a DBA was new, but expected. I've worked in secure DoD databases and secure Pharmaceutical databases. One thing to note, the security aspect isn't a lot different. My goal is to marry Databases and security in a functional manner and see how we can speed these things up with minimal work.

Searching SSIS Packages or searching XML In General

Today I'm posting a small script with a simple purpose. I wanted to search all of my SSIS packages in MSDB for a specific word. This does not work on encrypted packages. That being said, here's the script in question.

SELECT 
  Name
, CONVERT( xml, CONVERT(varbinary(max),PackageData) )   AS XMLSource
FROM msdb.dbo.sysssispackages
--where convert ( nvarchar(max), CONVERT( xml, CONVERT(varbinary(max),PackageData) ) ) = ''

I know it has a lot of converts, but it works. It takes about 19 seconds to run on my server if that helps. 

If there are better ways to do this, please add it in the comments. I'd love to get a better version for my archives. 

I know this is a short post. Prepping for my first SQL Saturday has taken a bit more time than I expected. Thanks for reading everyone!

Comments

Leave a comment on the original post [www.dbaonthego.com, opens in a new window]

Loading comments...