1

I'm hoping someone can offer another couple of points of view on an issue I am having with SSIS sequence containers. I am relatively new to sequence containers and they are accomplishing what need to be done, but here's the situation: Let's suppose I have three sequence containers and they all process files. Containers A, B, and C. Container A will always process because it unzips files, but container B may not perform actions based on whether or not a file exists and the same with container C. All containers contain other tasks which work fine. I have tried using constraints between the containers by evaluating the file name as such using expressions, but that doesn't seem to work. I'm not sure if the answer is to put a script task inside the dataflow task in the sequence container to look at the file name and then say, if the file is present, process it and if not, move on.

Any help would be appreciated. Thanks so much.

billinkc
  • 59,250
  • 9
  • 102
  • 159
streible
  • 33
  • 2
  • 6

1 Answers1

3

If I understand your problem. Sequence Container A always runs because it is looking for .zip files which downstream containers are dependent upon. The desire is to disable the dependent items if the parent didn't satisfy some criteria (found archives to unzip).

The quickest/codefree way would be to wrap the two Sequence Containers inside a Foreach Loop Container. That would fire off, find no file to process and never invoke the inner containers.

A script task, which is a control flow element, is fine and dandy and you can either enable/disable the containers or enable/disable the precedence constraint.

Either one can be the right approach, depending on your business needs.

Based on the comments below, I would probably pattern my package as this. The Sequence container encapsulates all the things that need to happen if data is available. The Sequence Container is the easy check that my file is there. This does assume a simple file existence check is sufficient. If files A and Z were required, then a Script Task will be easier.

enter image description here

Configuration of a ForEach Loop Container. I generally use the Expressions under the Enumerator section (and assign based on Variables instead of the hardcoded values displayed) as this allows me the greatest flexibility.

enter image description here

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • The problem I am having is the files within the zip file. I may have four or five files at any given time. Let's say the files are A, B, C, D, and E. If file A doesn't exist, the sequence container that services file A's contents doesn't have to execute, so it can move onto the next sequence container and so on. It's a serial process since I may not have all the file available based on the customer's data for the day. The other tasks are dependent upon the contents of the zip file which are CSV files with standardized names. – streible Nov 18 '13 at 21:08
  • I updated my answer with a picture of how I'd lay out the dataflow for minimal effort on my part – billinkc Nov 18 '13 at 21:21
  • Thank you for the example. The screenshot helps very much. The only part I am working on at the moment is getting the expression to say whether or not the file exists in the FELC. Would I want to evaluate the filename in the collection expressions or just the expressions selection? – streible Nov 18 '13 at 21:39
  • Updated with another screenshot. The short is the Expressions under the enumerator (top half) will overwrite the value in the Enumerator configuration section (bottom half) – billinkc Nov 18 '13 at 21:50
  • Thank you for your help. The screenshots really helped and I think I got it working properly now for one of the containers. I'll take care of the rest tomorrow. I really appreciate your posts and help. – streible Nov 18 '13 at 22:02
  • Dear downvoter, (hi [@TheOptimusPrime](http://stackoverflow.com/users/2517150/theoptimusprimus)), I don't understand why you marked this answer as "not helpful" Could you help me understand the downvote so that I may improve this answer? – billinkc Nov 21 '13 at 02:37
  • @streible Did this solve your question? If it does not, could you help me understand what else needs to be addressed? – billinkc Nov 26 '13 at 02:24
  • Yes, it resolved my problem completely. Thank you for your help. If I could have upvoted your answer, I would have, but I don't have enough points to do so yet on SO. – streible Nov 27 '13 at 13:51
  • No worries on the upvote. What you can do then, is click the green checkmark alongside answers that resolve your problem. Then my OCD can stop looking at it in the unanswered questions list ;) – billinkc Nov 27 '13 at 13:56