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!
