Showing posts with label FOREACH LOOP. Show all posts
Showing posts with label FOREACH LOOP. Show all posts
Case
I have a Foreach Loop Container with a file enumerator. The wildcard is *.xls, but it also returns *.xlsx files. How do I prevent that?

Loop through *.xls also includes xlsx files





















My xls loop includes xlsx and xlsm files


















Solution
This is actually similar to the DIR command in a DOS/Command Prompt.
All xls files? (/b is to remove my Dutch header/footer)











The workaround is simple. And if you don't like the solution then you could use my Sorted File Enumerator that also supports regular expression wildcards.

1) Dummy
Add an empty/dummy task or Sequence Container in your Foreach Loop Container. And connect it to your first task.

Empty/collapsed Sequence Container added

























2) Precedence Constraint Expression
Add an expression on the Precedence Constraint between the dummy and your first task. It should look something like LOWER(RIGHT(@[User::FilePath], 4)) == ".xls" (replace the variablename and/or file extension).

Expression with LOWER and RIGHT to check the file extension

















3) The result
Now test the package (Replace my example Script Task with your own tasks).
The result: only two xls files and no xlsx or xlsm files





















Case
I want to continue my loop when in one of the iterations a task fails. It should continue with the next iteration/file. I tried changing the task properties FailParentOnFailure and MaximumErrorCount, but nothing seems to work.
Task fails, so loop fails

















Solution
There are a couple of solutions. You could set the MaximumErrorCount to a higher number on the parent container (not on the task that fails). With FailParentOnFailure property on the failing task you can override that setting and fail the parent on the first error.

The easiest/stable solution is to use an empty OnError event handler on the failing task with the system variable Propagate set to false.

1) Empty event handler
Go to the event handlers tab. Select (1) the failing task as Executable and (2) OnError as the event handler. Next (3) click on the link in the middle of the page to create the event handler: Click here to create an 'OnError' event handler for executable 'DFT - Stage files'.
Create empty event handler
















2) System variable propagate
Open the variable pane in the event handler. Click on (1) the Variable Grid Options button. Check (2) the radio button "Show system variables". Search for the Propagate variable and set (3) it to false.
System variable Propagate

























Show system variables for SSIS 2008








3) Annotation
An empty event handler could confuse other developers. Adding a simple annotation could solve that.
Add an annotation























4) The result
Now an error in the Data Flow Task won't fail the Foreach Loop Container. This solution works with all containers (Sequence, For Loop, Foreach Loop and package).
























Note: this solution wont work with parent child packages. Propagate can't be disabled from a child package to a parent package. This is by design according Microsoft. Here is a workaround for that.
Case
I have a list of files in a database table which I want to loop through. Can I use a Foreach Loop Container for that?
My table with files













Solution
You can use the Foreach Loop Container with a Foreach ADO Enumerator for this task.


1) Variables
Create two variables. A variable named myFiles of the Object data type. This will contain the list of files. A string variable named filePath. This variable will be used in the Foreach Loop Container and will contain a single filepath.
Variables











2) Execute SQL Task - General
Add an Execute SQL Task to the Control Flow and edit it. Select Full result set as ResultSet; Select the Connection Manager to connect to the database with your table and add the SQLStatement.
Execute SQL Task - Editor - General


















3) Execute SQL Task - Result Set
Go to the Result Set pane and click add. Set the Result Name to zero and select the Object variable from step 1.
Execute SQL Task - Editor - Result Set





















4) Foreach Loop Container - Collection
Add a Foreach Loop Container and connect it to your Execute SQL Task. Edit the Foreach and go to the Collection pane. Select the Foreach ADO Enumerator and select the Object variable from step 1 as the ADO object source variable.
Foreach Loop Container - Collection























5) Foreach Loop Container - Variable Mappings
Go to the Variable Mappings pane and select the string variable from step 1. The index should be zero. It's the first column in a zero based column index (second column has index 1 and so on).

Foreach Loop Container - Variable Mappings























6) The Result
Now the construction is ready. The variable filePath will be filled with a filepath from the query. You can for example use this variable in an expression on a Flat File Connection Manager or in a File System Task. If you want to know how the expression on the connection manager works. Then go to this post about the File Enumerator and start at step 3. I added a Script Task with a messagebox to test the foreach loop.

MessageBox.Show(Dts.Variables["User::filePath"].Value.ToString());










































Note: I have an other Foreach ADO Enumerator example that uses a Script Task to fill the object variable instead of the Execute SQL Task.
Case
I want log all my connection managers, delete all my excel files, check whether my flat files exists. And I don't want to add task for all of them.

Solution
You can use a Script Task to loop through the collection of Connection Managers and do something with the connection manager (log, delete, check, etc.). Either do the task in .Net code or fill a SSIS object variable which can be used in Foreach From Variable Enumerator. I will show you both.

Note: these solutions don't work for dynamic Connection Managers. For example with a foreach loop and expressions on the connectionstring.


A) Script Task only solution
Add a Script Task to the Control Flow and edit it. Copy the code of this main method to your main method. There are a couple of examples so adjust it to your own needs.
// C# Code
// This isn't a complete solution.
// There are a couple of examples.
// Adjust them to you own needs.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO; // Added
namespace ST_ae645b8974b54c7abd6d5058ded524b6.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
// Used for information events
Boolean fireAgain = true;


// Loop through all your package connections
foreach (ConnectionManager connectionManager in Dts.Connections)
{
// Optional filter on TYPE. For example FLATFILE, OLEDB, EXCEL
// Let's check if the flatfile exists
if (connectionManager.CreationName.Equals("FLATFILE"))
{
if (File.Exists(connectionManager.ConnectionString))
{
Dts.Events.FireInformation(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString + ") does exist", string.Empty, 0, ref fireAgain);
}
else
{
Dts.Events.FireError(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString + ") does not exist", string.Empty, 0);
}
}


// Optional filter on PREFIX. For example starting with "tmp"
// Let's delete all tmp files.
if (connectionManager.Name.StartsWith("tmp"))
{
try
{
File.Delete(connectionManager.ConnectionString);
Dts.Events.FireInformation(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString + ") was deleted", string.Empty, 0, ref fireAgain);
}
catch (Exception Ex)
{
Dts.Events.FireError(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString + ") not deleted. Error: " + Ex.Message, string.Empty,0);
}
}


// Optional filter on TYPE.
// Let's move all excel files to a certain folder.
if (connectionManager.CreationName.Equals("EXCEL"))
{
// Excel connection string contains a lot more then just the file path. We need to extract the ExcelFilePath property
// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";
string ExcelFilePath = connectionManager.Properties["ExcelFilePath"].GetValue(connectionManager).ToString();

FileInfo myExcelFile = new FileInfo(ExcelFilePath);
File.Move(ExcelFilePath, @"D:\MyExcelFiles\Archive\" + myExcelFile.Name);
}


// Just log all connection managers
Dts.Events.FireInformation(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString + ")", string.Empty, 0, ref fireAgain);
}

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

or with VB.Net
' VB.Net code
' This isn't a complete solution.
' There are a couple of examples.
' Adjust them to you own needs.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO ' Added

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

Public Sub Main()
' Used for information events
Dim fireAgain As [Boolean] = True


' Loop through all your package connections
For Each connectionManager As ConnectionManager In Dts.Connections
' Optional filter on TYPE. For example FLATFILE, OLEDB, EXCEL
' Let's check if the flatfile exists
If connectionManager.CreationName.Equals("FLATFILE") Then
If File.Exists(connectionManager.ConnectionString) Then
Dts.Events.FireInformation(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString & ") does exist", String.Empty, 0, fireAgain)
Else
Dts.Events.FireError(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString & ") does not exist", String.Empty, 0)
End If
End If


' Optional filter on PREFIX. For example starting with "tmp"
' Let's delete all tmp files.
If connectionManager.Name.StartsWith("tmp") Then
Try
File.Delete(connectionManager.ConnectionString)
Dts.Events.FireInformation(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString & ") was deleted", String.Empty, 0, fireAgain)
Catch Ex As Exception
Dts.Events.FireError(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString & ") not deleted. Error: " & Ex.Message, String.Empty, 0)
End Try
End If


' Optional filter on TYPE.
' Let's move all excel files to a certain folder.
If connectionManager.CreationName.Equals("EXCEL") Then
' Excel connection string contains a lot more then just the file path. We need to extract the ExcelFilePath property
' Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";
Dim ExcelFilePath As String = connectionManager.Properties("ExcelFilePath").GetValue(connectionManager).ToString()

Dim myExcelFile As New FileInfo(ExcelFilePath)
File.Move(ExcelFilePath, "D:\MyExcelFiles\Archive\" & myExcelFile.Name)
End If


' Just log all connection managers
Dts.Events.FireInformation(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString & ")", String.Empty, 0, fireAgain)
Next

Dts.TaskResult = ScriptResults.Success
End Sub
End Class

B) Foreach Connection Manager Enumerator

1) Variables
Add an Object variable and name it connectionManagers and add a String variable named connectionString.
Variables










2) Script Task
Add a Script Task to the Control Flow and give it a suitable name.
Script Task
















3) ReadWriteVariable
Add the object variable from step 1 as ReadWriteVariable.
ReadWriteVariable
























4) The Script
Edit the script and copy the code from the main method to your main method.
// C# Code
// This isn't a complete solution.
// There are a couple of examples.
// Adjust them to you own needs.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_ae645b8974b54c7abd6d5058ded524b6.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
// Create array list for storing the connection managers
System.Collections.ArrayList connectionManagers = new System.Collections.ArrayList();

// Loop through all your package connections
foreach (ConnectionManager connectionManager in Dts.Connections)
{
// Optional filter on TYPE. For example FLATFILE, OLEDB, EXCEL
// Let's check if the flatfile exists
if (connectionManager.CreationName.Equals("FLATFILE"))
{
// Add item to array list
connectionManagers.Add(connectionManager.ConnectionString);
}


// Optional filter on TYPE.
// Let's move all excel files to a certain folder.
if (connectionManager.CreationName.Equals("EXCEL"))
{
// Excel connection string contains a lot more then just the file path. We need to extract the ExcelFilePath property
// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";
string ExcelFilePath = connectionManager.Properties["ExcelFilePath"].GetValue(connectionManager).ToString();

// Add item to array list
connectionManagers.Add(ExcelFilePath);
}
}

// Fill object variable with array list
Dts.Variables["User::connectionManagers"].Value = connectionManagers;

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

or VB.Net

' VB.Net code
' This isn't a complete solution.
' There are a couple of examples.
' Adjust them to you own needs.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

Public Sub Main()
' Create array list for storing the connection managers
Dim connectionManagers As New System.Collections.ArrayList()

' Loop through all your package connections
For Each connectionManager As ConnectionManager In Dts.Connections
' Optional filter on TYPE. For example FLATFILE, OLEDB, EXCEL
' Let's check if the flatfile exists
If connectionManager.CreationName.Equals("FLATFILE") Then
' Add item to array list
connectionManagers.Add(connectionManager.ConnectionString)
End If


' Optional filter on TYPE.
' Let's move all excel files to a certain folder.
If connectionManager.CreationName.Equals("EXCEL") Then
' Excel connection string contains a lot more then just the file path. We need to extract the ExcelFilePath property
' Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";
Dim ExcelFilePath As String = connectionManager.Properties("ExcelFilePath").GetValue(connectionManager).ToString()

' Add item to array list
connectionManagers.Add(ExcelFilePath)
End If
Next

' Fill object variable with array list
Dts.Variables("User::connectionManagers").Value = connectionManagers

Dts.TaskResult = ScriptResults.Success
End Sub
End Class

5) Add Foreach Loop
Add a foreach loop and choose Foreach From Variable Enumerator. Select the Object variable from step 1 as the variable to loop through.
Foreach From Variable Enumerator




























6) Variable Mapping
Go to the Variable Mappings tab and add the String variable from step 1.
Variable Mappings






















7) The Result
I added a Script Task with a Messagebox to test the loop.
The result
Case
I have an Excel file with multiple identical worksheets (one foreach month) and I want to add the data to a single database table. Is there a foreach loop solution so that I don't need to add multiple data flows or sources.?

Solution
a) You could use a union all query in the Excel Source. Not very flexible, but very easy.
SELECT  Column1
, Column2
, Column3
FROM [Sheet1$]
UNION ALL
SELECT Column1
, Column2
, Column3
FROM [Sheet2$]
UNION ALL
SELECT Column1
, Column2
, Column3
FROM [Sheet3$]
b) You could loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator. One downside: it returns both worksheets (which have the $ suffix) and named ranges. So you need an extra dummy task in your foreach loop with an expression on the precedence constraint to the next task. Something like: RIGHT(@[User::WorksheetName], 1) == "$"
c) You could use a Script Task to fill a SSIS object variable and use that to loop through. Let's elaborate that solution.

*update: Custom Excel Worksheet Enumerator.


1) Excel Connection Manager and Variables
Add an Excel Connection manager with a link to an Excel File with multiple Worksheets. I named mine "MyExcelFile". If you choose an other name, then make sure to also change that in the script task of step 3.

And we also need two SSIS variables. One object variable (ExcelWorksheets) to loop through in the foreach loop and one string variable (WorksheetName) to be filled by the foreach loop.
Two variables



















2) Script Task
Add a Script Task to your Control Flow and give it a suitable name. Edit the Script Task and add the SSIS Object variable "ExcelWorksheets" from step 1 as ReadWrite variable.
ReadWriteVariables






















3) The script
Edit the Script Task (open VSTA editor) and copy the following script.
// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb; // Added

namespace ST_0607edbf2c834733a551cd01039cb715.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
try
{
// Get connectionstring from Excel Connection Manager and use it to connect through OLE DB
OleDbConnection excelConnection = new OleDbConnection(Dts.Connections["MyExcelFile"].ConnectionString);
excelConnection.Open();

// Once connected, get the table schema and close the OLE DB connection
DataTable dtDatasetsInExcel = excelConnection.GetSchema("Tables");
excelConnection.Close();


// NOTE: The datatable from GetSchema includes both worksheets (which have the $ suffix)
// and named ranges. So we need to exclude those named ranges.

// Create a dataset.
DataSet dsWorksheetsInExcel = new DataSet();

// Create a new table in the dataset
DataTable dtWorksheetsInExcel = dsWorksheetsInExcel.Tables.Add();
dtWorksheetsInExcel.Columns.Add("WorksheetName", typeof(string));


// Loop through all tables and only get those ending with a $
foreach (DataRow drWorksheet in dtDatasetsInExcel.Rows)
{
// Check for $ suffix
if (drWorksheet["TABLE_NAME"].ToString().EndsWith("$"))
{
dtWorksheetsInExcel.Rows.Add(drWorksheet["TABLE_NAME"].ToString());
}
}

// Fire information event with the total number of worksheets
bool fireAgain = true;
Dts.Events.FireInformation(-1, "Foreach Worksheet", dtWorksheetsInExcel.Rows.Count.ToString() + " worksheets found.", string.Empty, 0, ref fireAgain);


// Fill SSIS Object variable with worksheet dataset.
Dts.Variables["User::ExcelWorksheets"].Value = dsWorksheetsInExcel;

// Success
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
// Something went wrong. Log error and fail Script Task
Dts.Events.FireError(-1,"Foreach Worksheet", ex.Message, string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
}


or VB.Net

' VB.Net code
Imports System
Imports System.Data
Imports System.Data.OleDb ' Added
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="" > _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

Public Sub Main()
Try
' Get connectionstring from Excel Connection Manager and use it to connect through OLE DB
Dim excelConnection As New OleDbConnection(Dts.Connections("MyExcelFile").ConnectionString)
excelConnection.Open()

' Once connected, get the table schema and close the OLE DB connection
Dim dtDatasetsInExcel As DataTable = excelConnection.GetSchema("Tables")
excelConnection.Close()

' NOTE: The datatable from GetSchema includes both worksheets (which have the $ suffix)
' and named ranges. So we need to exclude those named ranges.

' Create a dataset.
Dim dsWorksheetsInExcel As New DataSet()

' Create a new table in the dataset
Dim dtWorksheetsInExcel As DataTable = dsWorksheetsInExcel.Tables.Add()
dtWorksheetsInExcel.Columns.Add("WorksheetName", GetType(String))

' Loop through all tables and only get those ending with a $
For Each drWorksheet As DataRow In dtDatasetsInExcel.Rows
' Check for $ suffix
If drWorksheet("TABLE_NAME").ToString().EndsWith("$") Then
dtWorksheetsInExcel.Rows.Add(drWorksheet("TABLE_NAME").ToString())
End If
Next

' Fire information event with the total number of worksheets
Dim fireAgain As Boolean = True
Dts.Events.FireInformation(-1, "Foreach Worksheet", dtWorksheetsInExcel.Rows.Count.ToString() & " worksheets found.", String.Empty, 0, fireAgain)

' Fill SSIS Object variable with worksheet dataset.
Dts.Variables("User::ExcelWorksheets").Value = dsWorksheetsInExcel

' Success
Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
' Something went wrong. Log error and fail Script Task
Dts.Events.FireError(-1, "Foreach Worksheet", ex.Message, String.Empty, 0)
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub
End Class



Worksheets and named ranges











4) Foreach Loop
Add a Foreach Loop Container to the Control Flow and give it a suitable name. Connect it to the Script Task from step 2.
Foreach Loop


























5) Foreach ADO enumerator
Edit the Foreach Loop Container and select the ADO enumerator in the collection tab. After that select the SSIS object variable from step 1 as ADO object source variable and select Rows in first table as the enumeration mode.
Foreach ADO enumerator
























6) Variable mappings
In the Variable mappings tab select the string variable from step 1. Now the Foreach Loop will fill this variable with the currect Worksheet name.
Variable mappings
























7) Excel Source
Add a Data Flow Task in the foreach loop and add a Excel Source to that Data Flow Task. Select the Excel Connection manager created in step 1 and select Table name or view name variable. Now you can select the variable that contains the Worksheet name.
Excel Source using variable for table name
























Note 1: hidden worksheets are not returned by the OLE DB provider.
Note 2: Worksheets are always sorted ascending on their name
Note 3: If you have different data structures, but worksheets all have some common columns, then you could create a query in a variable and use that as a source query. Something like "SELECT column1, column4 FROM " + @[User::WorksheetName]
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.
In some cases the standard wildcard filter in the Foreach File Enumerator isn't flexible enough. Try to get all files ending with a number or all files starting with an 'a' or 'b'. A filter based on a regular expression gives more freedom. See/vote this request at Microsoft Connect.

My .Net colleague helped me to create a File Enumerator where I replaced the standard wildcard filter by a regular expression filter. And we also added a sorting possibility.
Regular Expression Support























This Regex File Enumerator also alows you to select multiple extensions like *.xls + *.csv:
^.*\.(xls|csv)$

Disclaimer
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Downloads:
You can download a SSIS 2008 version on the download page. All my custom 2012 enumerators have an optional regex filter included.

Installation
The installer registers the DLL in the GAC and copies it to the ForEachEnumerators folder of SSIS (example: C:\Program Files\Microsoft SQL Server\100\DTS\ForEachEnumerators\). After that you have to close Visual Studio/BIDS because it caches the GAC on startup.

SSIS
The enumerator is automatically added to the Foreach Loop when you open a SSIS project.
The new enumerator




























Bugs or feature suggestions
Please contact me or leave a comment if you have any suggestions or found a bug in this Custom Enumerator.
Case
The standard Foreach Loop File Enumerator only has a wildcard filter, but in some cases a regular expression filter would be more useful. See/vote this request at Microsoft Connect.

Solution
At the moment I'm working on a custom File Enumerator with regular expression support, but for those who don't want to wait or don't want to use custom components... You could achieve the same result with a Script Task.

*update: Now also available as Custom Foreach Enumerator. *

1) Foreach Loop
For this case I will use a standard Foreach File Enumerator that fills a variable FilePath. The filter is *.* so all files will be returned.
Standard foreach loop


















2) Variables
I will use two extra variables in this example: RegexFilter (string) for storing the regular expression and PassesRegexFilter (boolean) for indicating whether the filename passes the regular expression filter.
Variables













3) Script Task
Add a Script Task in front of the Data Flow Task and give it a suitable name.
Script Task






















4) Add variables
Edit the Script Task and add the FilePath and RegexFilter as ReadOnlyVariables and the PassesRegexFilter as ReadWriteVariable.
Variables






















5) The Script
Copy the following script to the Script Task.
// C# Code for filtering filenames with Regex
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Text.RegularExpressions; // Added
using System.IO; // Added

namespace ST_02b6595da2274d7182409fb43af929ae.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
// Get the filename from the complete filepath
String FileName = Path.GetFileName(Dts.Variables["User::FilePath"].Value.ToString());

// Create a regex object with the pattern from the SSIS variable
Regex FileFilterRegex = new Regex(Dts.Variables["User::RegexFilter"].Value.ToString());

// Check if it is match and return that value (boolean) to the SSIS variable
Dts.Variables["User::PassesRegexFilter"].Value = FileFilterRegex.IsMatch(FileName);

Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
or with VB.Net
' VB.Net Code for filtering filenames with Regex
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Text.RegularExpressions ' Added
Imports System.IO ' Added

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

Public Sub Main()
' Get the filename from the complete filepath
Dim FileName As String = Path.GetFileName(Dts.Variables("User::FilePath").Value.ToString())

' Create a regex object with the pattern from the SSIS variable
Dim FileFilterRegex As Regex = New Regex(Dts.Variables("User::RegexFilter").Value.ToString())

' Check if it is match and return that value (boolean) to the SSIS variable
Dts.Variables("User::PassesRegexFilter").Value = FileFilterRegex.IsMatch(FileName)

Dts.TaskResult = ScriptResults.Success
End Sub

End Class


6) Precedence Constraints Expression
Connect the Script Task to the Data Flow Task and add an expression that does the real filtering:
@[User::PassesRegexFilter] == true
Precedence Constraint with expression





















7) The result
For testing the result, I added an other Script Task below the Data Flow Task that logs all files. My regular expression in this test case: .*[0-9]\.csv$    (all csv files that end with a number in the filename)
The result























Note 1: to gain some performance I should have changed the Foreach Loop File Enumerator wildcard from *.* to *.csv

Note 2: this method could be a bit inefficient if your file collection contains thousands of files and you only need two of them.

About a year ago I did a post on creating a Foreach Folder Enumerator with a Script Task.
Foreach Folder Enumerator with Script Task














Now I have created a real Foreach Folder Enumerator and you can even sort on name and date. There is also an option to include the root folder (if it passes the filter) and the standard Traverse Subfolders is also included. In version 1.1 has regular expression support.
Foreach Folder Enumerator V1.1






























Disclaimer
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Downloads:
You can download a SSIS 2008 and 2012 version on the download page.
Version 1.1: Regular Expression support
Version 1.2: 2014 and 2016 version added, Option to fail if folder is empty. Upgradable

Installation
The installer registers the DLL in the GAC and copies it to the ForEachEnumerators folder of SSIS (example: C:\Program Files\Microsoft SQL Server\100\DTS\ForEachEnumerators\). After that you have to close Visual Studio/BIDS because it caches the GAC on startup.

SSIS
The enumerator is automatically added to the Foreach Loop when you open a SSIS project.
The new enumerator V1.0



























Bugs or feature suggestions
Please contact me or leave a comment if you have any suggestions or found a bug in this Custom Enumerator.