Code Reuse with Foreach Loop Containers – Part I

Given that the options available for code reuse in SSIS are limited, it’s important to understand containers like the Foreach Loop Container. It’s frustrating that reusing code in SSIS tends be more complicated than in other programming paradigms. But, that doesn’t mean that we should stop trying. Here is a representative example of copy/paste code reuse I’ve seen in the wild, that could have been easily avoided.

The Script Tasks and Data Flow Tasks above contained identical code, other than the variable value being set to 7 in the first Script Task, and 14 in the second Script Task. I’m going to illustrate two ways that a Foreach Loop Container could have been used to avoid the dreaded copy/paste reuse. This post (Part I) will show the hard coded version, and an upcoming post (Part II) will show a variable driven Foreach Loop Container that can be controlled at runtime by changing the value of an externally accessible variable.

Reuse Option 1 – Hard Coded Foreach Loop Container

I’m usually the first to say YAGNI (You Aren’t Gonna Need It), because I have seen far too much code that is more complicated than necessary. So, we’re going to start with a rewrite that assumes that 7 and 14 are the only values that this package will ever care about. In other words, we are going to hard code it. Here is what the example above would look like, if a Foreach Loop Container would have been used:

Creating your own ForEach Loop Container

The ForEach Loop Container we create will not contain a Data Flow Task like the example above, because it isn’t needed to illustrate the value we get from doing this. A simple single Script Task within the Foreach Loop Container will do.

1. Create a new Foreach Loop Container named FEL Export.
2. Double-click FEL Export.
3. Click on Collection.
4. Change the Enumerator to Foreach Item Enumerator.
5. Click on the Columns button.
   
6. Click Add to add a single column and change the data type to int32.
7. Click OK.
8. Click in the empty cell and enter the first value (7).
9. Press Enter.
10. Enter the next value (14).
11. Click on Variable Mappings.
    
12. Select <New Variable…> in the Variable listbox, leave the Index = 0.
13. Create a new variable
    
14. Click OK. FELVariableItem will now be populated with 7 in the first loop and 14 in the second loop.
15. Let’s do something to show that the value is changing. Create a new Script Task within FEL Export named SCR Prepare.
16. Double-click on SCR Prepare to open it for editing.
17. Add FELVariableItem to the list of ReadOnlyVariables.
18. Click Edit Script

    public void Main() {
      bool fireAgain = false;
      Dts.Events.FireInformation(0, "SCR Prepare"
        , String.Format("FELVariableItem = {0}"
        , Dts.Variables["FELVariableItem"].Value.ToString())
        , String.Empty, 0, ref fireAgain);
      Dts.TaskResult = (int)ScriptResults.Success;
    }
    

19. Run the package and your Output Window should show something similar to the following
    

This post has shown how you can eliminate copy/paste reuse with just a little extra work. Stay tuned for our next episode, where we will remove the hard coded values (in the Enumerator) and control the package externally, by using a package variable to control how the loop is executed.