[Don't miss the update at the bottom]
The accepted view of a sequence container is that it is used to:
a) Group tasks that have a logical relationship into a single logical unit of work. This can be beneficial when running transactions in a package or
b) Organise your design surface by hiding tasks that are logically related - thereby promoting a modular approach to building packages
Useful though that is, that's about all all they did.
Well this evening I found another use of them courtesy of a post on the SSIS forum from Chris Kinsman. To sum it up he had a workflow that looked a bit like this:
He always wanted A & C to execute
B would execute depending on the result of an expression
C should execute regardless of whether B executed but (and here's the rub) if B did execute, C should execute after it.
The problem he was having was that it was impossible, using just the objects illustrated here, to make C execute if B didn't.
It was a perplexing problem that was proving difficult to solve but I realised that sequence containers provided a solution by encasing the conditional execution of B so that it didn't affect the execution of C.
Here's how its done. As they say, a picture tells a thousand words...
Here's the steps to fix it:
1) Put B into a sequence container. Lets call our new sequence container "D".
2) Put an OnSuccess precedence constraint from A to D
3) Put an OnSuccess precedence constraint from A to C
4) Place a script task into D. It doesn't need any code in it because its not going to do anything
5) Put an expression precedence constraint from the script task to B.
As you can see the execution of B is still dependent on an expression but because B is inside a sequence container the execution of C is not affected if B does not execute. Very nifty methinks! It seems sequence containers do have a conventional use after all!!
Can you imagine trying to model that in DTS? Sheesh...it doesn't bear thinking about!!!
If you want to have a play with the demo package for this you can download it from here: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20050727PrecedenceConstraintanomoly.zip
-Jamie
UPDATE: Kirk came up with an even simpler method of doing this, still using a sequence container (again, called D here) mind you.
It just seems so obvious when you see it now :)
-Jamie