Code Reuse with Foreach Loop Containers – Part II

In last week’s episode, we saw how Foreach Loop Containers can be implemented to reuse code. However, that example hard coded values within the Foreach Loop Container to accomplish that goal. Many people cringe when they hear hard coded, and this post should address their concerns. As a reminder, 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.

Reuse Option II – Variable Driven Foreach Loop Container

Here’s a version for those of you that never hard code anything, because you don’t want to redeploy code and/or you don’t think YAGNI (You Aren’t Gonna Need It).

Creating your own ForEach Loop Container

This will be slightly more complicated than last week’s post, but allows for run time flexibility. In other words, when running the package, you will be able to pass in a list of values to be used in the Foreach Loop Container. This would let us pass in 7;14;21;30;60;90 to the package without having to edit the package, and that is very powerful.
NOTE: The ForEach Loop Container we’re creating 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. 

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 From Variable Enumerator.
5. In the Variable listbox, click New Variable…
6. Create the variable FELVariableCollection as shown below, to hold a list of values that can be passed in at runtime
   
7. Click OK.
8. Click on Variable Mappings.
9. Select <New Variable…> in the Variable listbox, leave the Index = 0 and create the following variable to hold the value for the current loop.
   
10. Click OK to close the Add Variable dialog.
11. Click OK to close the Foreach Loop Editor.
12. Create a new package Variable (xFELVariableList, as specified in the image below) that will be passed in to provide runtime values. If you look at your list of variables now, it should look like the following.
    
    NOTE: For SSIS 2012, I would create xFELVariableList as a Package Parameter instead of a variable, because its value will not change during package execution.
13. Add a Script Task named SCR Initialize before FEL Export to initialize FELVariableCollection.
    
14. Edit SCR Initialize and set up the variables as shown below.
    
15. Click Edit Script…

    public void Main() {
      char[] charSeparator = new char[] { ';' };
      Dts.Variables["FELVariableCollection"].Value =
        Dts.Variables["xFELVariableList"].Value.ToString().Split(charSeparator, StringSplitOptions.RemoveEmptyEntries);
      Dts.TaskResult = (int)ScriptResults.Success;
    }
    

16. Let’s do something to show that the value is changing. Create a new Script Task within FEL Export named SCR Prepare.
    
17. Double-click on SCR Prepare to open it for editing.
18. Add FELVariableItem to the list of ReadOnlyVariables.
19. 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;
    }
    

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

Whether you hard code the values in a Foreach Loop Container like in Part I, or pass the values in at runtime, code reuse is generally a good thing. I hope you learned a thing or two, and will think of this the next time you catch yourself using copy/paste reuse in an SSIS package. Please stay tuned for more posts on SSIS reuse in the future.