Wednesday, 20 June 2012

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.

Friday, 15 June 2012

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.

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:

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