Very Computer

1.  To find the first step look for the step with no precendence constraints
2.  Each Step has a PrecedenceConstraints collection which contains 0+
PrecedenceConstraint objects.  A PrecedenceConstraint object has a Property
of StepName which is the Step previous that is before it.

HTH

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Quote:> Hello, I am building a program that will run a series of packages for
> our Data Warehouse project.  The reason I am doing this, is we wanted
> to add some additional functionality that we just couldn't get out of
> DTS, such as sending a 'net send' if the package fails (we're not
> using jobs), and being able to run multiple packages in sequence and
> still being able to see the "datagrid" interface of each step as in EM
> (something ExecutePackage task does not do).  At any rate, I have the
> program going and it works fine, however, in order to fill the
> datagrid, I'm just using the step descriptions as returned by the
> package.steps collection, which unfortunately lists them in order of
> creation, and not of workflow.  These packages are rather large and
> have some complex workflow in them, and I haven't been able to figure
> out a way of listing them according to there workflow.  If anyone has
> done this or has some insight into it that could get me on the right
> track, I would surely appreciate it.  I'm developing the program in
> VB.NET.

An alternative is to use events, and just add rows to the grid for an
OnStart and update based on the other events as required.

--
Darren Green
http://www.sqldts.com

Quote:> Hello, I am building a program that will run a series of packages for
> our Data Warehouse project.  The reason I am doing this, is we wanted
> to add some additional functionality that we just couldn't get out of
> DTS, such as sending a 'net send' if the package fails (we're not
> using jobs), and being able to run multiple packages in sequence and
> still being able to see the "datagrid" interface of each step as in EM
> (something ExecutePackage task does not do).  At any rate, I have the
> program going and it works fine, however, in order to fill the
> datagrid, I'm just using the step descriptions as returned by the
> package.steps collection, which unfortunately lists them in order of
> creation, and not of workflow.  These packages are rather large and
> have some complex workflow in them, and I haven't been able to figure
> out a way of listing them according to there workflow.  If anyone has
> done this or has some insight into it that could get me on the right
> track, I would surely appreciate it.  I'm developing the program in
> VB.NET.

Michael Morrison
SE2 Developer/Consultant
Daugherty Business Solutions
www.daugherty.com

Well, I couldn't get it to do exactly that, but what I've come up with
actually looks better to us as it minimizes the scrolling needed to
see the current running tasks (we have very large packages).  So my
solution lists the steps in workflow order and then by order of
creation.  This may not necessarily mean that each subsequent task
will run one after the other, DTS still will optimize execution as it
sees fit, and there is really no way to arrange this according to true
execution unless you only list the steps as they are run as Darren
noted.  So with the above example, my code will list the steps as
follows:
Task1
Task2
Task3
Task4
Task5
Task6
Task7
Task8

So here goes.  This may not be the best way to implement this, so if
anyone can take this and further refine it to be more concise or
quicker, let me know please
(michael.morrison@(removethis)daugherty.com).

'Assuming you have already created an instance of a package object
called pkg
            pkg.LoadFromStorageFile(pkgPath, "", "", "", pkgName,
Nothing)

            Dim stp As DTS.Step2
            Dim prc As DTS.PrecedenceConstraint
            Dim prcName As String
            Dim stpsCol As New Collection
            Dim stpSort As New Collection
            Dim stpsArr As ArrayList
            Dim stpSorArr As ArrayList
            Dim InstpSort As Boolean
            Dim x As Integer = 0

            'Fill the collection with all the steps
            'This will enter them in order of creation
            For Each stp In pkg.Steps
                stpsCol.Add(stp, stp.Name)
            Next
            'Use ArrayLists Adapter method and send it collection
            'This will give us a copy of the collection
            'Not sure if this has to be an ArrayList, but my earlier
attempts
            'centered on using them, so I kept them and it all works,
so....
            stpsArr = stpsArr.Adapter(stpsCol)
            stpsArr.TrimToSize()

            'Loop through the ArrayList and for each stp that doesn't
have
            'a PrecedenceConstraint add it to the second collection
(the one
            'that will be sorted) and remove from first collection
            Do
                stp = stpsArr.Item(x)
                If stp.PrecedenceConstraints.Count = 0 Then
                    stpSort.Add(stp, stp.Name)
                    stpsCol.Remove(stp.Name)
                End If
                x += 1
            Loop Until x > stpsCol.Count - 1
            stpSorArr = stpSorArr.Adapter(stpSort)
            stpSorArr.TrimToSize()

            'Now loop through first collection again and for each step
check
            'that each of it's PrecedenceConstraints StepName steps is
already
            'in collection two.  If it is it will set variable
InstpSort to
            'true, otherwise it will set it to false and exit that For
loop
            x = 0
            Do
                If x >= stpsCol.Count Then
                    x = 0
                End If
                stp = stpsArr.Item(x)
                For Each prc In stp.PrecedenceConstraints
                    prcName = prc.StepName
                    InstpSort = stpSorArr.Contains(pkg.Steps.Item(prcName))
                    If InstpSort = False Then
                        Exit For
                    End If
                Next

                'For each stp, after all the PrecedenceConstraints
have been
                'checked (or it returned a false), if InstpSort is
true, it
                'will go ahead and move it into second collection and
remove
                'it from the first collection
                If InstpSort = True Then
                    stpSort.Add(stp, stp.Name)
                    stpsCol.Remove(stp.Name)
                End If

                'If InstpSort was false, it will skip that step and go
on with
                'the loop testing the next step.  This will ensure
that a step
                'is not added to the second collection before any and
all of
                'its parent steps have been added
                x += 1
                stpSorArr = stpSorArr.Adapter(stpSort)
                stpSorArr.TrimToSize()
            Loop Until stpsCol.Count = 0

            'Now that the sorted collection exists, we fill the
datagrid with
            'the collection item by item. The Tag property here is
used for
            'identification later so that I can set the icon to what
the task
            'current status is you may or may not need it.  Again, my
grid has
            'has the same columns as the EM version, so column 1 is
the icon
            'followed by Step Description, Status, StartTime,
FinishTime, and
            'ExecutionTime.
            x = 0
            For Each stp In stpSort
                lstVwSteps.Items.Add("", 0)
                lstVwSteps.Items(x).Tag = stp.Name
                lstVwSteps.Items(x).SubItems.Add(stp.Description)
                lstVwSteps.Items(x).SubItems.Add("Waiting")
                lstVwSteps.Items(x).SubItems.Add("")
                lstVwSteps.Items(x).SubItems.Add("")
                lstVwSteps.Items(x).SubItems.Add("")
                x += 1
            Next

'The following commented code will list the steps just as pkg.steps
collection
'lists them in order of creation, regardless of workflow order
'This is not needed, but thought I'd include it in case somebody
wanted it
            'For Each stp In pkg.Steps
            '    lstVwSteps.Items.Add("", 0)
            '    lstVwSteps.Items(x).Tag = stp.Name
            '    lstVwSteps.Items(x).SubItems.Add(stp.Description)
            '    lstVwSteps.Items(x).SubItems.Add("Waiting")
            '    lstVwSteps.Items(x).SubItems.Add("")
            '    lstVwSteps.Items(x).SubItems.Add("")
            '    lstVwSteps.Items(x).SubItems.Add("")
            '    x += 1
            'Next

Hope this helps the next guy having the same problem.
Michael