SELECT 'SSISPackages' AS [Source], [name] AS [SourceId], [name] as [PackageName], [description] as [PackageDescription], @@SERVERNAME as [ServerName], case [packagetype] WHEN 0 then 'Undefined' WHEN 1 then 'SQL Server Import and Export Wizard' WHEN 2 then 'DTS Designer in SQL Server 2000' WHEN 3 then 'SQL Server Replication' WHEN 5 then 'SSIS Designer' WHEN 6 then 'Maintenance Plan Designer or Wizard' end as [PackageType],case [packageformat] WHEN 0 then 'SSIS 2005 version' WHEN 1 then 'SSIS 2008 version' end as [PackageFormat], [createdate], CAST(CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) AS NVARCHAR(MAX)) PackageXML
FROM [msdb].[dbo].[sysdtspackages90]
WHERE CAST(CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) AS NVARCHAR(MAX)) like '%<text here>%'
SELECT 'SSISPackages' AS [Source], [name] AS [SourceId], [name] as [PackageName], [description] as [PackageDescription], @@SERVERNAME as [ServerName], case [packagetype] WHEN 0 then 'Undefined' WHEN 1 then 'SQL Server Import and Export Wizard' WHEN 2 then 'DTS Designer in SQL Server 2000' WHEN 3 then 'SQL Server Replication' WHEN 5 then 'SSIS Designer' WHEN 6 then 'Maintenance Plan Designer or Wizard' end as [PackageType], [createdate], CAST(CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) AS NVARCHAR(MAX)) PackageXML
FROM [msdb].[dbo].sysssispackages
WHERE CAST(CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) AS NVARCHAR(MAX)) like '%<text here>%'