Balanced Data Distributor for 2012 has been released

Last year Microsoft released the Balanced Data Distributor for 2008 and today they released the 2012 version. This transform takes a single input and distributes the incoming rows to one or more outputs uniformly via multi-threading.



BDD in SSIS 2012

Stop package when foreach loop does not find any files

Case
When the Foreach Loop File Enumerator is empty, SSIS will throw a warning, but I want to fail the package. How do I do that?

The For Each File enumerator is empty.

Solution
This solution will count the number of loops of the Foreach Loop and fire an error if the count is zero.

Validate number of files

1) Add variable
Add an integer variable, named "FileCount", to count the number of files. The variable scope is package.

integer variable

2) Add Script Task for counting
Add a Script Task within the Foreach Loop and name it "Increment Counter". You can connect it with a Precedemce Constraint to other tasks within your Foreach Loop, but that's not necessary.
Edit the Script Task and add the variable from step 1 as a ReadWrite variable.

ReadWriteVariables

3) The script for counting
Edit the script and add the follow C# code to the Main method.

// C# Code
public void Main()
{
    // Get value of counter variable and increment with 1
    Dts.Variables["User::FileCount"].Value = Convert.ToInt32(Dts.Variables["User::FileCount"].Value) + 1;

    Dts.TaskResult = (int)ScriptResults.Success;
}

or VB.Net

' VB.Net Code
Public Sub Main()
 ' Get value of counter variable and increment with 1
 Dts.Variables("User::FileCount").Value = Convert.ToInt32(Dts.Variables("User::FileCount").Value) + 1

 Dts.TaskResult = ScriptResults.Success
End Sub

4) Add Script Task for validating
Add a Script Task outside the Foreach Loop and connect it with a Precendence Constraint to your Foreach Loop. Name it "Validate Counter". Edit the Script Task and add the variable from step 1 as ReadOnly variable.

ReadOnlyVariables

5) The Script for validating
Edit the script and add the follow C# code to the Main method.

// C# Code
public void Main()
{
    // Check if counter is zero
    if (Dts.Variables["User::FileCount"].Value.ToString() == "0")
    {
        // Throw error event and fail Script Task
        Dts.Events.FireError(-1, "Foreach Loop", "The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.", String.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
    else
    {
        // Files where found so no error
        Dts.TaskResult = (int)ScriptResults.Success;
    }
}

or VB.Net

' VB.Net Code
Public Sub Main()
 ' Check if counter is zero
 If (Dts.Variables("User::FileCount").Value.ToString() = "0") Then
  ' Throw error event and fail Script Task
  Dts.Events.FireError(-1, "Foreach Loop", "The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.", String.Empty, 0)
  Dts.TaskResult = ScriptResults.Failure
 Else
  ' Files where found so no error
  Dts.TaskResult = ScriptResults.Success
 End If
End Sub

6) The Result
Run the package and check the Progress tab in your package.

The result

Alternative solution
If there are no tasks behind your Foreach Loop you could also try something with an event handler. Because you know the Foreach Loop will throw a warning you could check for that warning and thrown an error. Haven't test it thoroughly, but it will look something like this:

A) Add Script Task in OnWarning event handler
Go to the eventhandler tab (1) and add an OnWarning event handler (2) for your Foreach Loop and add Script Task (3) that reads two system variables (4) System::ErrorCode and System::ErrorDescription as ReadOnly variables

OnWarning Event handler

B) The script
Edit the script and add the follow code to the Main method.

// C# Code
public void Main()
{
 // Check if last error(/warning) is about empty foreach loop: 
 // Warning: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.
 if (Dts.Variables["System::ErrorCode"].Value.ToString().Equals("-2147368956"))
 {
  // Then throw error with message of last warning (or throw your own message)
  Dts.Events.FireError(0, "Foreach Loop", Dts.Variables["System::ErrorDescription"].Value.ToString(), String.Empty, 0);
  Dts.TaskResult = (int)ScriptResults.Failure;
 }
 else
 {
  Dts.TaskResult = (int)ScriptResults.Success;
 }
}

or VB.Net

' VB.Net Code
Public Sub Main()
 ' Check if last error(/warning) is about empty foreach loop: 
 ' Warning: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.
 If (Dts.Variables("System::ErrorCode").Value.ToString().Equals("-2147368956")) Then
  ' Then throw error with message of last warning (or throw your own message)
  Dts.Events.FireError(0, "Foreach Loop", Dts.Variables("System::ErrorDescription").Value.ToString(), String.Empty, 0)
  Dts.TaskResult = ScriptResults.Failure
 Else
  Dts.TaskResult = ScriptResults.Success
 End If
End Sub

Let me know if you have an other alternative.