want to shred ssis conn managers i am extracting from msdb

Question

question

db042190 asked Dec 16, '20

want to shred ssis conn managers i am extracting from msdb

Hi we run 2014 std and are researching a potential issue with an alias in one of our ssis environments.

one of my peers believes we have two types of packages on msdb in that environment. Some that currently run with two specific conn mgrs (one is sql the other ado) referencing alias "abc". And some that wont work with that alias unless those 2 specific conn mgrs are hard coded to the server on which that ssis environment runs.

there are often 5 conn managers in our pkgs. because "abc" is referenced in our pkgs for another reason , i think i need to learn how to shred two specific conn mgrs to find if either one refers to "abc". i tried using LIKE in my query to match on what shows after their guids but couldn't get that to work.

so in the example shown below , i show my base query followed by the typical conn mgrs anonymzed. The goal is to find examples where the 2nd and 3rd conn mgrs have the string "abc" between their beginning and ending tags. i will cast the varchar(max) to xml if necessary.

select * from 
(
select * , cast(cast(packagedata as varbinary(max)) as varchar(max))pd2
from sysssispackages 
) x
where...


<DTS:ConnectionManagers>      
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[xxx.app1]" DTS:CreationName="OLEDB" DTS:DTSID="{a guid}" DTS:ObjectName="xxx.app1">        <DTS:ObjectData>          <DTS:ConnectionManager DTS:ConnectionString="Data Source=aliasthatsok;Initial Catalog=app11;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;"/>        </DTS:ObjectData>      </DTS:ConnectionManager>      
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[xxx.app2]" DTS:CreationName="OLEDB" DTS:DTSID="{a guid}" DTS:ObjectName="xxx.app2">        <DTS:ObjectData>          <DTS:ConnectionManager DTS:ConnectionString="Data Source=hardcodedserver;Initial Catalog=app2;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;"/>        </DTS:ObjectData>      </DTS:ConnectionManager>      
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[xxx.app2 ADO.NET]" DTS:CreationName="ADO.NET:System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=somekey" DTS:DTSID="{a guid}" DTS:ObjectName="xxx.app2 ADO.NET">        <DTS:ObjectData>          <DTS:ConnectionManager DTS:ConnectionString="Data Source=hardcodedserver;Initial Catalog=app2;Integrated Security=True;"/>        </DTS:ObjectData>      </DTS:ConnectionManager>      
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[xxx.SSISConfig]" DTS:CreationName="OLEDB" DTS:DTSID="{a guid}" DTS:ObjectName="xxx.SSISConfig">        <DTS:ObjectData>          <DTS:ConnectionManager DTS:ConnectionString="Data Source=abc;Initial Catalog=master;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"/>        </DTS:ObjectData>      </DTS:ConnectionManager> 
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[PackageLogFile]" DTS:CreationName="FILE" DTS:Description="Package Log File. Location is set dynamically using an expression." DTS:DTSID="{a guid}" DTS:ObjectName="PackageLogFile">        <DTS:PropertyExpression DTS:Name="ConnectionString">@[Template::LogFileName]</DTS:PropertyExpression>        <DTS:ObjectData>          <DTS:ConnectionManager DTS:FileUsageType="1" DTS:ConnectionString="some letter:\temp\LogFiles\pkgname_1.0.206_20201117131352.log"/>        </DTS:ObjectData>      </DTS:ConnectionManager>    
</DTS:ConnectionManagers>

sql-server xml

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Dec 16, 2020 at 08:39 AM

And for that specific example given, what do you want as output?

0 ·

db042190 Kev Riley ♦♦ commented · Dec 17, 2020 at 06:59 PM

good question. minimally connector object name (eg xxx.app2) and package name. i can make that a cte and join on variations of the base query.

0 ·

Answer 1 · 2020-12-18T10:28:57Z

Kev Riley answered Dec 18, '20

Try this:

with xmlnamespaces('www.microsoft.com/SqlServer/Dts' as DTS)
select 
    pd_XML.value('(/DTS:Executable/@DTS:ObjectName)[1]','varchar(max)') as PackageName,
    managers1.value('(@DTS:ObjectName)[1]','varchar(max)') as ConnectionManagerName,
    managers2.value('(@DTS:ConnectionString)[1]','varchar(max)') ConnectionString
from 
    (select cast(cast(packagedata as varbinary(max)) as xml) from sysssispackages ) packagedata(pd_XML)
cross apply pd_XML.nodes('/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager') AS connection1(managers1)
cross apply managers1.nodes('DTS:ObjectData/DTS:ConnectionManager') AS connection2(managers2)
where 
    managers2.value('(@DTS:ConnectionString)[1]','varchar(max)') like '%abc%'

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2020-12-18T13:35:47Z

db042190 answered Dec 18, '20

thank you. if it isn't against this forums rules, can you tell me (or point me at an artcle) what sql feature (pd_XML) represents in the following statement? I'm going to try to understand this, not just use it. I assume packagedata just before it is just a table alias or name like we always use for sub selects.

(select cast(cast(packagedata as varbinary(max)) as xml) from sysssispackages ) packagedata(pd_XML)

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Dec 18, 2020 at 01:38 PM

It's simply another way of aliasing the columns, it could have been written like:
(select cast(cast(packagedata as varbinary(max)) as xml) as pd_XML from sysssispackages ) packagedata

0 ·

db042190 Kev Riley ♦♦ commented · Dec 21, 2020 at 02:55 PM

appreciated Kev. makes sense.

0 ·

question