How to use SSIS API to read a package and determin

Question

I am writing aprogram which uses the Microsoft.SqlServer.Dts.Runtime library to load a package from XML. Currently, I am extracting all of the connections and tasks but I am not currently capturing the precedence sequence.

I used the following short debug snippet to dance around the objects and properties that looked like they contained this information

public void Scan(string tempPath)
    {    
        XmlDocument packageXml = new XmlDocument();
        packageXml.Load(tempPath);
        IDTSEvents events = new SsisEventSupport();
        package = new Microsoft.SqlServer.Dts.Runtime.Package { PackagePassword = this.packagePassword };
        package.LoadFromXML(packageXml, events);

        foreach (var precedenceConstraint in package.PrecedenceConstraints)
        {
            //precedenceConstraint.;
            Microsoft.SqlServer.Dts.Runtime.TaskHost constrainedExecutable = (Microsoft.SqlServer.Dts.Runtime.TaskHost)precedenceConstraint.ConstrainedExecutable;
            string taskName = constrainedExecutable.Name;
            string taskId = constrainedExecutable.ID;
            string precedenceConstraintId = precedenceConstraint.ID;

            Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask executeSqlTask = (Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask)constrainedExecutable.InnerObject;

        }

constrainedExecutable.ID is giving me the ID of the object that is being constrained but I can't figure out the ID of the object that is constraining it! precedenceConstraint.ID is the ID of the constraint itself, not the ID of what is constraining the object identified by constrainedExecutable.ID.

I've search and searched but can't seem to find it. If you need an example, I created a simple package with 3 Execute SQL Tasks with no Connections. The execution flow is as follows:

Perform  1 => Do Step 2 => Another Step

I've opened up the SQL 2008 dtsx file and cleaned up the file leaving mostly the relevant xml. In the first example, the first SSIS constraint IDE arrow between "Perform Step 1" and "Do Step 2" has an ID of 9726771C-E514-4D2D-B586-BC49D82A944E (precedenceConstraint.ID), the ID of the arrow/precident itself. The value of constrainedExecutable.ID is the ID of the constrained task, "Do Step 2", 06B8BB21-5604-4A62-BD2F-9BBA7D1CA1ED. Looking at the XML, I know that the ID of the of the preceeding task is AEFFD93C-4C6D-4388-9FD5-603F6608A76C}, the ID of the first task, "Perform 1", but how do I get the value of this ID from code?

  <DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0">
  <DTS:Property DTS:Name="TaskContact">Execute SQL Task; Microsoft Corporation; Microsoft SQL Server 2008 R2; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property> 

  <DTS:Property DTS:Name="ObjectName">Perform 1</DTS:Property> 
  <DTS:Property DTS:Name="DTSID">{AEFFD93C-4C6D-4388-9FD5-603F6608A76C}</DTS:Property> 
  <DTS:Property DTS:Name="Description">Execute SQL Task</DTS:Property> 

    <DTS:ObjectData>
        <SQLTask:SqlTaskData SQLTask:Connection="" SQLTask:TimeOut="0" SQLTask:IsStoredProc="False" SQLTask:BypassPrepare="True" SQLTask:SqlStmtSourceType="DirectInput" SQLTask:SqlStatementSource="" SQLTask:CodePage="1252" SQLTask:ResultType="ResultSetType_None" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" /> 
    </DTS:ObjectData>  
  </DTS:Executable>


 <DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0">
  <DTS:Property DTS:Name="LoggingMode">0</DTS:Property> 
  <DTS:Property DTS:Name="FilterKind">1</DTS:Property> 
  <DTS:Property DTS:Name="EventFilter" DTS:DataType="8" /> 
  </DTS:LoggingOptions>
      <DTS:Property DTS:Name="ObjectName">Do Step 2</DTS:Property> 
      <DTS:Property DTS:Name="DTSID">{06B8BB21-5604-4A62-BD2F-9BBA7D1CA1ED}</DTS:Property> 
      <DTS:Property DTS:Name="Description">Execute SQL Task</DTS:Property> 

     <DTS:ObjectData>
      <SQLTask:SqlTaskData SQLTask:Connection="" SQLTask:TimeOut="0" SQLTask:IsStoredProc="False" SQLTask:BypassPrepare="True" SQLTask:SqlStmtSourceType="DirectInput" SQLTask:SqlStatementSource="" SQLTask:CodePage="1252" SQLTask:ResultType="ResultSetType_None" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" /> 
     </DTS:ObjectData>

</DTS:Executable>

- <DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0">
  <DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property> 
  <DTS:Property DTS:Name="ExecutionAddress" /> 
  <DTS:Property DTS:Name="TaskContact">Execute SQL Task; Microsoft Corporation; Microsoft SQL Server 2008 R2; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property> 

  <DTS:Property DTS:Name="ObjectName">Another Step</DTS:Property> 
  <DTS:Property DTS:Name="DTSID">{2B89143B-4828-4AFA-9B5A-FAE0DB17880B}</DTS:Property> 
  <DTS:Property DTS:Name="Description">Execute SQL Task</DTS:Property>   
 <DTS:ObjectData>
  <SQLTask:SqlTaskData SQLTask:Connection="" SQLTask:TimeOut="0" SQLTask:IsStoredProc="False" SQLTask:BypassPrepare="True" SQLTask:SqlStmtSourceType="DirectInput" SQLTask:SqlStatementSource="" SQLTask:CodePage="1252" SQLTask:ResultType="ResultSetType_None" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" /> 
  </DTS:ObjectData>
  </DTS:Executable>

- <DTS:PrecedenceConstraint>
  <DTS:Property DTS:Name="Value">0</DTS:Property> 
  <DTS:Property DTS:Name="EvalOp">2</DTS:Property> 
  <DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property> 
  <DTS:Property DTS:Name="Expression" /> 
  <DTS:Executable IDREF="{AEFFD93C-4C6D-4388-9FD5-603F6608A76C}" DTS:IsFrom="-1" /> 
  <DTS:Executable IDREF="{06B8BB21-5604-4A62-BD2F-9BBA7D1CA1ED}" DTS:IsFrom="0" /> 
  <DTS:Property DTS:Name="ObjectName">Constraint</DTS:Property> 
  <DTS:Property DTS:Name="DTSID">{9726771C-E514-4D2D-B586-BC49D82A944E}</DTS:Property> 
  <DTS:Property DTS:Name="Description" /> 
  <DTS:Property DTS:Name="CreationName" /> 
  </DTS:PrecedenceConstraint>
- <DTS:PrecedenceConstraint>
  <DTS:Property DTS:Name="Value">0</DTS:Property> 
  <DTS:Property DTS:Name="EvalOp">2</DTS:Property> 
  <DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property> 
  <DTS:Property DTS:Name="Expression" /> 
  <DTS:Executable IDREF="{06B8BB21-5604-4A62-BD2F-9BBA7D1CA1ED}" DTS:IsFrom="-1" /> 
  <DTS:Executable IDREF="{2B89143B-4828-4AFA-9B5A-FAE0DB17880B}" DTS:IsFrom="0" /> 
  <DTS:Property DTS:Name="ObjectName">Constraint 1</DTS:Property> 
  <DTS:Property DTS:Name="DTSID">{42D509E8-4856-491B-A1D7-A30E4AA82B77}</DTS:Property> 
  <DTS:Property DTS:Name="Description" /> 
  <DTS:Property DTS:Name="CreationName" /> 
  </DTS:PrecedenceConstraint>
  <DTS:Property DTS:Name="ObjectName">Package1</DTS:Property> 
  <DTS:Property DTS:Name="DTSID">{D0AC46B8-4D38-41AB-830F-93E195B5300D}</DTS:Property> 
  <DTS:Property DTS:Name="Description" /> 
  <DTS:Property DTS:Name="CreationName">SSIS.Package.2</DTS:Property> 
  <DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property> 

  <DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:BoundingTop>1000</dwd:BoundingTop><dwd:Layout><dds> <diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout100" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout100" version="7" nextobject="14" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="4263" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="21616" y="8281" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"> <font> <ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /> </font> <mouseicon> <ddsxmlobjectstreamwrapper binary="6c74000000000000" /> </mouseicon> </diagram> <layoutmanager> <ddsxmlobj /> </layoutmanager> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="No connection manager is specified. " left="0" top="7654" logicalid="6" controlid="1" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{06B8BB21-5604-4A62-BD2F-9BBA7D1CA1ED}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="No connection manager is specified. " left="978" top="5300" logicalid="7" controlid="2" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{AEFFD93C-4C6D-4388-9FD5-603F6608A76C}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="1400" top="6065" logicalid="8" controlid="3" masterid="0" hint1="0" hint2="0" width="1777" height="2089" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{9726771C-E514-4D2D-B586-BC49D82A944E}" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="2" destid="1" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="2777" y="6464" /> <point x="2777" y="7059" /> <point x="1799" y="7059" /> <point x="1799" y="7654" /> </connector> </ddscontrol> <ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.2" tooltip="No connection manager is specified. " left="740" top="9930" logicalid="9" controlid="4" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{2B89143B-4828-4AFA-9B5A-FAE0DB17880B}" vartype="8" /> <property name="ShowConnectorSource" value="0" vartype="2" /> </ddsxmlobj> </layoutobject> <shape groupshapeid="0" groupnode="0" /> </ddscontrol> <ddscontrol controlprogid="MSDDS.Polyline" left="1400" top="8419" logicalid="10" controlid="5" masterid="0" hint1="0" hint2="0" width="1539" height="2011" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"> <control> <ddsxmlobj> <polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /> </ddsxmlobj> </control> <layoutobject> <ddsxmlobj> <property name="LogicalObject" value="{42D509E8-4856-491B-A1D7-A30E4AA82B77}" vartype="8" /> <property name="Virtual" value="0" vartype="11" /> <property name="VisibleAP" value="0" vartype="3" /> </ddsxmlobj> </layoutobject> <connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout100" sourceid="1" destid="4" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"> <point x="1799" y="8818" /> <point x="1799" y="9374" /> <point x="2539" y="9374" /> <point x="2539" y="9930" /> </connector> </ddscontrol> </dds></dwd:Layout><dwd:PersistedViewPortTop>4263</dwd:PersistedViewPortTop></dwd:DtsControlFlowDiagram></Package></DTS:Property> 
  <DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property> 
  <DTS:Property DTS:Name="ObjectName">package-diagram</DTS:Property> 
  <DTS:Property DTS:Name="DTSID">{9517A3EC-AB7A-4B57-A78B-33F2E4568DCF}</DTS:Property> 
  <DTS:Property DTS:Name="Description" /> 
  <DTS:Property DTS:Name="CreationName" /> 
  </DTS:PackageVariable>
  </DTS:Executable>

I wish I could go straight to offering a big bounty for this one, as I suspect that that's where it will end.

Answer 1

There was another object under the constraint, PrecedenceExecutable, that represents the "preceeding" object, and it also has an ID property. I'm not sure how I missed it. I just needed to look at it fresh, it seems.

    foreach (var precedenceConstraint in package.PrecedenceConstraints)
    {

        Microsoft.SqlServer.Dts.Runtime.TaskHost constrainedExecutable = (Microsoft.SqlServer.Dts.Runtime.TaskHost)precedenceConstraint.ConstrainedExecutable;
        Microsoft.SqlServer.Dts.Runtime.TaskHost precedenceExecutable = (Microsoft.SqlServer.Dts.Runtime.TaskHost)precedenceConstraint.PrecedenceExecutable;

        string taskName = constrainedExecutable.Name;

        string constrainedExecutableId = constrainedExecutable.ID;
        string precedenceConstraintId = precedenceConstraint.ID;

        Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask executeSqlTask = (Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask)constrainedExecutable.InnerObject;

    }