How to find packages which include a specific database as a datasource

• MadAdmin

  SSChampion

  Points: 11261

  More actions

  September 28, 2012 at 3:27 am

  #282317

  Problem : 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?

  Catch-all queries done right [/url]
  Gail Shaw's Performance Blog[/url]

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  October 1, 2012 at 4:35 pm

  #1544186

  I 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:

  SELECT name

  FROM msdb.dbo.sysdtspackages90

  WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))

  LIKE 'servername\instancename';

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• MadAdmin

  SSChampion

  Points: 11261

  More actions

  October 4, 2012 at 4:00 am

  #1545432

  This seems to work well.

  ;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS pNS1,

  'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespaces

  SELECT 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)') ConnectionString

  FROM

  --

  ( 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%'

  Catch-all queries done right [/url]
  Gail Shaw's Performance Blog[/url]