Here is what I've used to search for fields within SSIS packages(dtsx files) stored in the MSDB database.


--Connect to database:  msdb
SELECT [name] AS SSISPackageName,
    CONVERT(XML,CONVERT(VARBINARY(MAX), packagedata)) AS SSISPackageXML,
    CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX), packagedata)) AS SSISPackageVarchar
INTO #SSISObjectSearch
FROM msdb.dbo.sysssispackages
;

SELECT *
FROM #SSISObjectSearch
WHERE SSISPackageVarchar LIKE '%ColumnNameHere%'
ORDER BY SSISPackageName
;