InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 05:53:47 GMT20http://www.sqlservercentral.com/Forums/Topic1365710-148-1.aspxThis seems to work well.[code="sql"];WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS pNS1, 'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespacesSELECT c.name, SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]', 'varchar(100)') AS ObjectName, SSIS_XML.value('./pNS1:Property[@pNS1:Name="Description"][1]', 'varchar(100)') AS Description, SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1:Property[@pNS1:Name="ConnectionString"][1]', 'varchar(MAX)') ConnectionStringFROM --( SELECT id , CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML FROM [msdb].[dbo].[sysdtspackages90] ) PackageXML CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager') SSIS_XML ( SSIS_XML ) INNER JOIN [msdb].[dbo].[sysdtspackages90] c ON PackageXML.id = c.id WHERE SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1:Property[@pNS1:Name="ConnectionString"][1]', 'varchar(MAX)') LIKE '%data source%'[/code]Thu, 04 Oct 2012 04:00:12 GMTMadAdminhttp://www.sqlservercentral.com/Forums/Topic1365710-148-1.aspxI guess you are not using SSIS Package Configurations to store Connection Manager info. If you are you can simply change the config files and the code will not need to change.If not, then you can try selecting this information from the package XML:[code="sql"]SELECT nameFROM msdb.dbo.sysdtspackages90WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX)) LIKE 'servername\instancename';[/code]Mon, 01 Oct 2012 16:35:27 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1365710-148-1.aspxProblem : Database needs to move from one server to another.Before moving the database, I would need to get a list of all the ssis packages which reference this DB so that they can be updated after the move.Is there a table in MSDB which has this information?Fri, 28 Sep 2012 03:27:46 GMTMadAdmin