In SSIS, using the Foreach Loop Container we can loop around 7 different things: file, item, ADO, ADO.NET Schema Rowset, Variable, Nodelist and SMO.
- File: we loop around a collection of files in a folder
- Item: we loop around rows of data that we define ourselves (can be more than 1 column)
- ADO: we loop around rows of a existing table
- ADO.NET schema rowset: we loop around a collection of any of these: Catalogs, Character Sets, Check Constrains, Collations, Column Privileges, Columns, Columns Domain Usage, Constraint Column Usage, Constraint Table Usage, Foreign Keys, Indexes, Key Column Usage, Indexes, Primary Keys, Procedure Columns, Procedure Parameters, Procedures, Provider Types, Referential Constraints, Schemata, Statistics, Table Constraints, Table Privileges, Tables, Translations, Usage Privileges, View Column Usage, Views, View Table Usage, SQL Languages, DB Info Keywords, DB Info Literals, Check Constraints by Table, Table Statistics, Tables Info, and Trustees.
- Variable: we loop around the values of a variable, for example a DataTable where we loop around the rows.
- Nodelist: we loop around a set of XML nodes, which are the results of applying XPath expression to an XML file.
- SMO: we loop around SQL Server Management Objects, such as tables, users, statistics, jobs, traces, brokers.
In this article we will use ADO enumerator. We’ll create a package that:
- Create a DataSet
- Create a DataTable in the DataSet
- Populate the DataTable with 2 rows, row 1: file1.txt, row 2: file2.txt
- Assign that DataSet to variable Var1
- Have a FELC ADO Enumerator with source = Var1 to loop around the rows of the DataTable
- Set the Variable Mapping of this FELC to Var2
- Inside this FELC loop create a Script Task that checks if a file with file name = the value of Var2 exists or not.
The overall package looks like this:
True, we don’t need to use FELC to loop around the DataTable rows. We could do it in the first C# Script task, right after step 3 (populate the DataTable). The reasons of we might want to use FELC are a) to use SSIS functionality such as transaction and checkpoint, b) to reuse the variable that was transferred to the FELC in downstream task(s).
1-4 Create & Populate DataTable, assign to Var1
OK let’s start. First create a variable called Var1, of type object, scope = package:
Create the Script Task, and set Var1 as ReadWrite variable:
Edit Script, and create the DataSet and DataTable:
// Create the table
DataSet DS1 = new DataSet();
DataTable T1 = new DataTable();
DataColumn C1 = new DataColumn();
C1.DataType = Type.GetType(“System.String”);
C1.ColumnName = “Col1”;
T1.Columns.Add(C1);
DS1.Tables.Add(T1);
Usually we populate the datatable from a database table, but in this instance I’m going to populate it from a file:
// Populate the table from the file
StreamReader sr1;
sr1 = new StreamReader(“C:\\Data\\FELC per row\\list.txt”);
String s1;
DataRow DR1;
while ((s1 = sr1.ReadLine()) != null)
{
DR1 = DS1.Tables[0].NewRow();
DR1[“Col1”] = s1;
DS1.Tables[0].Rows.Add(DR1);
}
sr1.Close();
StreamReader sr1;
sr1 = new StreamReader(“C:\\Data\\FELC per row\\list.txt”);
String s1;
DataRow DR1;
while ((s1 = sr1.ReadLine()) != null)
{
DR1 = DS1.Tables[0].NewRow();
DR1[“Col1”] = s1;
DS1.Tables[0].Rows.Add(DR1);
}
sr1.Close();
The List.txt file only contains 2 lines:
After populating the DataSet, we assign it to Var1:
// Set VariableDts.Variables[“Var1”].Value = DS1;Dts.TaskResult = (int)ScriptResults.Success;
5. Create the Foreach Loop Container
Have a FELC ADO Enumerator with source = Var1 to loop around the rows of the DataTable
Set the collection of the FELC to ADO enumerator, rows in the first table:
6. Set the Variable Mapping
Set the Variable Mapping of this FELC to Var2 (create Var2 first, scope = package, type = string):
This means that every row on the DataTable will be assigned to Var2. It’s the first column only that will be assigned (Index 0 = first column).
7. Add a Script Task inside the loop
Add script task inside the loop to process each row. Set Var 2 as ReadOnlyVariables because we want to use within the script.
Display the value of Var2 and check the existance of the files mentioned in Var2:
string Var2 = Dts.Variables[“Var2”].Value.ToString();
System.Windows.Forms.MessageBox.Show(Var2);
if (File.Exists(“C:\\Data\\FELC per row\\”+Var2))
{
System.Windows.Forms.MessageBox.Show(Var2 + ” exists”);
}
else
{
System.Windows.Forms.MessageBox.Show(Var2 + ” does not exist”);
}
Dts.TaskResult = (int)ScriptResults.Success;
System.Windows.Forms.MessageBox.Show(Var2);
if (File.Exists(“C:\\Data\\FELC per row\\”+Var2))
{
System.Windows.Forms.MessageBox.Show(Var2 + ” exists”);
}
else
{
System.Windows.Forms.MessageBox.Show(Var2 + ” does not exist”);
}
Dts.TaskResult = (int)ScriptResults.Success;
Apologies about the indentation. Within {} the code should be indented, but WordPress wouldn’t let it. Unless I format it as “preformatted”. But using preformatted the font would appear very small.
Now if we run it, it will display the value of Var2 and whether that file exist or not:
