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.
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.
, CONVERT( xml, CONVERT(varbinary(max),PackageData) ) AS XMLSource
--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!