Retrieving the definitions of the SSIS Packages
SELECT p.[name] as [PackageName] ,[description] as [PackageDescription] ,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] ,l.[name] as [Creator] ,p.[createdate] ,CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML FROM [msdb].[dbo].[sysssispackages] p JOIN sys.syslogins l ON p.[ownersid] = l.[sid]
Extracting connection strings from an SSIS Package
;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="DelayValidation"][1]', 'varchar(100)') AS DelayValidation, 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="Retain"][1]', 'varchar(MAX)') Retain, 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].[sysssispackages] ) PackageXML CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager') SSIS_XML (SSIS_XML ) INNER JOIN [msdb].[dbo].[sysssispackages] c ON PackageXML.id = c.id
0 comments: