Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find packages which include a specific database as a datasource Expand / Collapse
Author
Message
Posted Friday, September 28, 2012 3:27 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 14, 2014 11:05 AM
Points: 56, Visits: 351
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?
Post #1365710
Posted Monday, October 1, 2012 4:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1366817
Posted Thursday, October 4, 2012 4:00 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 14, 2014 11:05 AM
Points: 56, Visits: 351
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%'

Post #1368285
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse