Friday 15 June 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. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.
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 within foreach loop
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.

7 comments:

  1. Is there a way, short of throwing an exception, to have this component route the processing along a second "Success" path (sort of a multi-cast approach) so that the job won't be failed and, instead, could simply log a successful completion without having processed any files?

    The idea is that, if there are no files, it may not be a "failure" but it may also not be appropriate for some (or most) of the subsequent components to be executed. At the same time, even though there are no files found, it may BE appropriate for SOME of the subsequent components (or some other components) to be executed.

    ReplyDelete
    Replies
    1. The foreach loop wont fail, but will throw a warning event. The extra Script Task is throwing the fatal error. Just remove that task if you don't want to stop.

      If you want to throw an other event type or use a custom text, then check out these examples.

      If you want an alternative path if there are no files then you could fill a variable instead of throwing an error and use the contents of that variable in an expression on the next precedence constraint

      Delete
  2. Hi, from where can you get the warning codes? Like in this case it is -2147368956

    ReplyDelete
  3. how do I not show the warning "For Each File enumerator is empty"

    ReplyDelete
    Replies
    1. 1) You could try something with an event handler for this event...
      2) disable warning loggings for the component
      3) first check if there are any files with for example Script Task and then add a precedence constraint to not execute the loop if there are no files.
      4) Ask this question in the MSND SSIS forum. That will help you getting an answer more sooner...

      Delete
  4. You can also solve this by:
    1) adding a Scripting task BEFORE the FOrEach component with code like:
    2)
    Public Sub Main()
    Dim bOk As Boolean = False
    Dim strFolder As String

    REM The pathname to the target folder on the server was stored in a VS Connection Manager named "Raw", making this code more maintainable
    Dim rawConnection As Object = Dts.Connections("Raw").AcquireConnection(Dts.Transaction)
    strFolder = CType(rawConnection, String)
    bOk = (System.IO.Directory.GetFiles(strFolder, "*.csv").Length > 0) 'this is the test point where we look for any *.csv files in the folder
    Dts.Connections("Raw").ReleaseConnection(rawConnection)

    If Not bOk Then 'No files found
    Dts.Events.FireError(0, "", "NO CSV Files available to process in folder " & strFolder, String.Empty, 0)
    Dts.TaskResult = ScriptResults.Failure
    Else
    Dts.TaskResult = ScriptResults.Success
    End If

    Rem NOTE: You do need to make sure that this error (the .fireerror and the scriptresult.failure both create errors)
    REM surpasses your configured "error limit" count for the job in order to assure termination of the job after throwing the error
    End Sub

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...