November 20, 2015 at 8:38 am
Hi All,
We can use script to list all the SSIS packages, folders and schedules off of MSDB.
Is there script \way to script out all the connection managers that are used in the package?
Thank you.
November 20, 2015 at 3:56 pm
This should do the trick. I just cooked this up and only had like 10 SSIS pacakages available to test against so there is likely more stuff you can extract.
This will extract the SSIS data as XML so that you can examine it. Note that you can click on the link for the XML and it will open in a new window in SSMS.
SELECT
[name],
SSISXML = CAST(CAST([packagedata] AS varbinary(max)) AS xml)
FROM [msdb].[dbo].[sysssispackages]
You can use that code to get your connection manager information like so:
;WITH XMLNAMESPACES
('www.microsoft.com/SqlServer/Dts' as p1,'www.microsoft.com/SqlServer/Dts' as DTS),
Base AS
(
-- run just this part of the CTE to extract the SSIS XML
SELECT
[name],
SSISXML = CAST(CAST([packagedata] AS varbinary(max)) AS xml)
FROM [msdb].[dbo].[sysssispackages]
)
SELECT
b.name,
DTSID = n.value('(@DTS:DTSID)[1]','varchar(1000)'),
ConnDesc = n.value('(@DTS:Description)[1]','varchar(1000)'), --DTS:refId
refId = n.value('(@DTS:refId)[1]','varchar(1000)'), --DTS:refId
CreationName = n.value('(@DTS:CreationName)[1]','varchar(100)'),
ObjectName = n.value('(@DTS:ObjectName)[1]','varchar(100)'),
PropertyExpression = n.value('(DTS:PropertyExpression/text())[1]','varchar(1000)'),
PropertyExpression =
pe.value('(@DTS:Name)[1]','varchar(1000)')+': ' +
pe.value('(text())[1]','varchar(1000)'),
ConnectionString =
ISNULL('Retain: '+n.value('@DTS:Retain','varchar(20)'),'') +
n.value
('(DTS:ObjectData/DTS:ConnectionManager/@DTS:ConnectionString)[1]','varchar(2000)')
FROM Base b
CROSS APPLY
SSISXML.nodes('DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager') ssis(n)
CROSS APPLY
n.nodes('DTS:PropertyExpression') p(pe);
This is not lightening fast, it takes about 10 seconds to return 70 rows on my mediocre server. That's what happens when you need to convert image format to varbinary(max) then varbinary(max) to XML.
If you plan on accessing/reviewing this data often I would create a SQL table to store this data. Store the XML as XML (not image) and index as needed. I would only include the XML if you think you may want to extract more/different SSIS data down the road. I've done something like this a few times with SSRS data where I have an SSIS job that inserts the RDL into a well indexed table in relational format; this on a nightly basis so that that I can retrieve it quickly when needed.
-- Itzik Ben-Gan 2001
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply