Showing posts with label ENUMERATOR. Show all posts
Showing posts with label ENUMERATOR. Show all posts
Case
I want to create my own custom SSIS enumerator with a GUI. How do you do that?

Solution
For this example I will create a very basic enumerator which you can extend for your own needs.
The enumerator is for SSIS 2008 and 2012 and I will use Visual Studio 2010 to create the enumerator. Programming language is C#. Use this page to translate the code to VB.Net if you prefer that language.
Very very basic enumerator example, but it works























1) Create Visual Studio project
For my enumerator I used two C# Class Library projects. One for the GUI/editor and one for the code. For SSIS 2008 I will use .Net framework 3.5 and for SSIS 2012 I will use .Net framework 4.0
Two project for my enumerator solution




















2) Create key for strongname
You need to strongname your DLL's so that SSIS can use them. More about that in this Codeguru article: Giving a .NET Assembly a Strong Name. Open the Visual Studio 2010 Command Prompt (in Windows start menu). Browse to your project folder and execute the following command to create a key file: sn.exe -k myEnumerator.snk
Microsoft (R) .NET Framework Strong Name Utility


















3) Add key to project
The key file should be added to both projects.

Add key to projects




















And after adding them, you need to sign the projects. Go to the properties of the projects and then to the Signing page. There you can sign the assembly with your newly generated key. Do this for both projects.
Sign Assembly



















4) Adding SSIS reference
We need to add references to SSIS libraries. The GUI project needs two references:
  • Microsoft.SqlServer.Dts.Design
  • Microsoft.SQLServer.ManagedDTS
And the code project only needs one reference:
  • Microsoft.SQLServer.ManagedDTS

For SSIS 2008 they can be found in the program files folder. Something like:
C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
And for SSIS 2012 they are located in the GAC. Something like:
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ManagedDTS.dll
Add references



















5) Build Events
To use the enumerator dll's in SSIS you need to copy them to the GAC and to the enumerator folder of SSIS. With the Build Events you can do that automatically when you build the visual studio project. Go to the properties of your projects and then to the Build Events. Add the following command to the Post-Build events.

2008
cd $(ProjectDir)
@SET ENUMDIR="C:\Program Files (x86)\Microsoft SQL Server\100\DTS\ForEachEnumerators\"
@SET GACUTIL="C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\gacutil.exe"

Echo Installing dll in GAC
Echo $(OutDir)
Echo $(TargetFileName)
%GACUTIL% -if "$(OutDir)$(TargetFileName)"

Echo Copying files to Enumerators
copy "$(OutDir)$(TargetFileName)" %ENUMDIR%


2012
cd $(ProjectDir)
@SET ENUMDIR="C:\Program Files (x86)\Microsoft SQL Server\110\DTS\ForEachEnumerators\"
@SET GACUTIL="C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\gacutil.exe"

Echo Installing dll in GAC
Echo $(OutDir)
Echo $(TargetFileName)
%GACUTIL% -if "$(OutDir)$(TargetFileName)"

Echo Copying files to Enumerators
copy "$(OutDir)$(TargetFileName)" %ENUMDIR%

Post-Build Events




















6) Gui project code
To keep everything clear and easy to explain I created a simplified file enumerator. The GUI has only two textboxes and a button. See the code comments for the explanation.
Two properties: folder and wildcard filter





















// C# code
// The code-behind from our GUI.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime; // Added

namespace SSISJoost
{
public partial class myEnumeratorEditor : ForEachEnumeratorUI
{
// Variables needed to communicate with SSIS.
// You could for example get a list of all
// variables or connection managers.
private ForEachEnumeratorHost _feeHost;
private Connections _connections; // not used in this solution
private Variables _variables; // not used in this solution

// Constructor
public myEnumeratorEditor()
{
InitializeComponent();
}

// This method is executed when you open the editor.
public override void Initialize(ForEachEnumeratorHost FEEHost, IServiceProvider serviceProvider, Connections connections, Variables variables)
{
// Filling those variables so that we read values of two properties
base.Initialize(FEEHost, serviceProvider, connections, variables);
this._feeHost = FEEHost;
this._connections = connections; // not used in this solution
this._variables = variables; // not used in this solution

// Get the properties from the Enumerator. I have added these properties in the
// other project. They are used to store the folder and filter in the package.
if (this._feeHost != null)
{
// Get the Directory value and fill the textbox.
if (this._feeHost.Properties["Directory"] != null)
{
this.txtFolder.Text = (string)this._feeHost.Properties["Directory"].GetValue(_feeHost);
}
// Get the Filter value and fill the textbox.
if (this._feeHost.Properties["Filter"] != null)
{
this.txtFilter.Text = (string)this._feeHost.Properties["Filter"].GetValue(_feeHost);
}
}
}

// This method is execute when you press OK in the editor
public override void SaveSettings()
{
base.SaveSettings();
// When you close(/save) the enumerator then you need to write the values from the
// form to the properties of the enumerator so that they are saved in the package.
this._feeHost.Properties["Directory"].SetValue(this._feeHost, this.txtFolder.Text);
this._feeHost.Properties["Filter"].SetValue(this._feeHost, this.txtFilter.Text);
}

// Open browse folder dialog when you click the button
private void btnBrowse_Click(object sender, EventArgs e)
{
if (DialogResult.OK == folderBrowserDialog.ShowDialog())
{
txtFolder.Text = folderBrowserDialog.SelectedPath;
}
}
}
}


7) Get PublicKeyToken
For the other project you need the PublicKeyToken of the GUI assembly. So first build the GUI project and then, via the same command prompt of step 2, execute the following command in the BIN folder of your GUI project: sn.exe -T SSISJoost.myEnumeratorUI.dll
Copy the number generated. You need it in the next project.














8) The code for the actual work
This is the code from the project that does the actual enumeration. See the comments for the explanation.
// C# code
// The code from the project that does the actual work.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime; // Added
using Microsoft.SqlServer.Dts.Runtime.Enumerators; // Added
using System.IO; // Added

namespace SSISJoost
{
// Connection to the editor assembly. Copy the PublicKeyToken from the previous step.
[DtsForEachEnumerator(
DisplayName = "myEnumerator",
Description = "A very basic enumerator",
UITypeName = "SSISJoost.myEnumeratorEditor, SSISJoost.myEnumeratorUI, Version=1.0.0.0, Culture=Neutral,PublicKeyToken=629e0ff8812f1e93")]
public class myEnumerator : ForEachEnumerator
{
// The properties of my enumerator that will
// be saved in the xml of the SSIS package.

// A property for the folder
private string _directory;
public string Directory
{
get { return this._directory; }
set { this._directory = value; }
}

// A property for the wildcard filter
private string _filter = "*.*";
public string Filter
{
get { return this._filter; }
set { this._filter = value; }
}

// This validation method is execute when you run the package, but also when
// you click OK in the editor. You can fire warnings and errors.
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSInfoEvents infoEvents, IDTSLogging log)
{
// Directory is mandatory
if (string.IsNullOrEmpty(_directory))
{
// Fire error and fail package
infoEvents.FireError(0, "myEnumerator", "Directory is mandatory", "", 0);
return DTSExecResult.Failure;
}

// Filter isn't mandatory, but fire warning that a default will be used
if (string.IsNullOrEmpty(_filter))
{
// Fire warming, but continue
infoEvents.FireWarning(0, "myEnumerator", "Filter is empty. The default *.* will be used.", "", 0);
}
return DTSExecResult.Success;
}

// This is the method that fills the enumerator.
// You can enumerate through just about anything
// you like. This is a simplified file enumerator.
public override object GetEnumerator(Connections connections, VariableDispenser variableDispenser, IDTSInfoEvents events, IDTSLogging log)
{
// Determine the options for getting the files
string startFolder = this._directory;
string filter = "*.*";
System.IO.SearchOption searchOption = SearchOption.TopDirectoryOnly;

// If the filter is "", then just use the "*.*" as default
if (!string.IsNullOrEmpty(this._filter))
{
filter = this._filter;
}

// Create a list that will be returned by this method
List<String> filePaths = new List<String>(); ;

try
{
// Take a snapshot of the file system.
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(startFolder);

// Get all the files that match the critery
IQueryable<System.IO.FileInfo> fileList = dir.GetFiles(filter, searchOption).AsQueryable<System.IO.FileInfo>();

// Loop through list and only get the 'Fully Qualified Name' => the complete path
foreach (FileInfo myFile in fileList)
{
filePaths.Add(myFile.FullName);
}
}
catch (Exception ex)
{
// Fire error if something unexpected fails
events.FireError(0, "myEnumerator", "Unexpected Exception: " + ex.Message, "", 0);
}

// return the list
return filePaths.GetEnumerator();
}
}
}

9) The Result
After building / deploying the solution, you need to close/reopen BIDS because the GAC is cached on startup. Now you can use your new enumerator.
New enumerator in action


























10) Download
You can download the complete Visual Studio 2010 example solutions:
SSIS 2008 version
SSIS 2012 version


Note: this is a very basic example. Keep in mind that some subjects, like upgrading, are not explained in this blog.
More info: MSDNDougbert, Graham
Recently I did a post on how to loop through all sheets in an Excel file. For those who don't like scripting and for those who think the Foreach ADO.NET Schema Rowset solution is ugly, I created a custom enumerator. Same result, but with an easier interface and a possibility to filter with both wildcards and regular expressions.

Foreach Excel Worksheet Enumerator























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.

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
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]

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.


































Last year I did a post on how to create a Foreach Sorted File 'Enumerator' with a Foreach ADO Enumerator and a preceding Script Task that created a sorted collection.
A sorted Foreach Loop Container




















But Microsoft.Net colleague Marc Potters helped me to create a real enumerator. The SSIS Foreach Sorted File Enumerator loops through the files in a folder like a regular file enumerator, but now you can sort  it (name and/or date)! How handy is that?
Foreach Sorted File Enumerator
























The 2012 version also has regular expression support.
2012 version Regex Support
























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.

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.

A while ago I did a post on how to create a Foreach FTP File 'Enumerator' with a Script Task and a Foreach Variable Enumerator.
Foreach FTP File 'Enumerator'





















But Microsoft.Net colleague Marc Potters helped me to create a real enumerator. The SSIS Foreach FTP File Enumerator loops through the files on a FTP site. You can sort, filter and traverse subfolders. It will use one of the FTP Connections from the Connection Managers.
Foreach FTP File Enumerator






















Foreach FTP File Enumerator 1.4


















How to use it


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: Minor bug fixes
Version 1.2: Added Regular Expression filter
Version 1.3: Fix sorting bug and added possibility to add a connection manager
Version 1.4: Added option to fail on empty enumerator and option to return either filename/extension or complete path. Added 2016 version and made 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


























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
I want a Foreach Folder Enumerator, but the Foreach Loop component only loops through files.

Solution
Unfortunately this isn't supported by the standard Foreach Loop component, but there are a couple of workarounds:
- Use an open source component Directory Foreach Enumerator or my own Custom Foreach Folder Enumerator
- Use a Script task to accomplish this.

This example uses the Script task and a Foreach Loop to enumerate through the folders. The Script tasks generates a list of Directories and the Foreach Loop loops through that list.

1) Control Flow
Drag a Script task and a Foreach Loop container to the Control Flow like the image below.
Control Flow




















2) Variables
Create three string variables:
  1. startlocation that indicates the parent folder wherein we gonna search for subfolders. Fill it with a path like "d:\foreachfoldertest\".
  2. xmldoc which we gonna use for communicating between the Script task and the Foreach Loop.
  3. folder which is used for the variable mapping in the Foreach Loop container.
Varibles (right click in Control Flow)










3) Script Task
Select the startlocation as a readonly variable (we only read the path inside it) and select xmldoc as a readwritevariable (an xml string will be stored in it).
Variables



















4) The Script
Copy the following script to your Script task
// C# code 
// This script loops through a folder
// and adds all subfolders to an xml string
using System;
using System.Data;
using System.IO; // Added
using System.Xml; // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_5da96344f1c4411ab56207579f2e5e91.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 all subfolders within the parent folder
string[] subFolders = Directory.GetDirectories(Dts.Variables["User::startlocation"].Value.ToString());

// Create variables for the XML string
XmlDocument xmldoc = new XmlDocument();
XmlElement xmlRootElem;
XmlElement xmlChildElem;
XmlText xmltext;

// Add the root element: <ROOT>
xmlRootElem = xmldoc.CreateElement("", "ROOT", "");

// Variable for directory information
DirectoryInfo directoryInfo;

// Loop through the parent folder
foreach (string currentFolder in subFolders)
{
// Fill directoryInfo variable with folder information
directoryInfo = new DirectoryInfo(currentFolder);

// Create the child element that contains the path:
// <Folder>d:\foreachfoldertest\subfolder1\</Folder>
xmlChildElem = xmldoc.CreateElement("", "Folder", "");
xmltext = xmldoc.CreateTextNode(directoryInfo.FullName);
xmlChildElem.AppendChild(xmltext);

// Add the child element to the root element
xmlRootElem.AppendChild(xmlChildElem);
}
// Add the root element to the xml document
xmldoc.AppendChild(xmlRootElem);

// Store the xml in the SSIS variable
Dts.Variables["xmldoc"].Value = xmldoc.InnerXml.ToString();

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

This script will result in:
<ROOT>
  <Folder>d:\foreachfoldertest\subfolder1</Folder>
  <Folder>d:\foreachfoldertest\subfolder2</Folder>
  <Folder>d:\foreachfoldertest\subfolder3</Folder>
</ROOT>

5) Foreach Loop Container
Edit the Foreach Loop Container and change the enumerator on the Collection tab to Foreach NodeList Enumerator. This means that it will loop through an xml string. Change the DocumentSourceType to Variable and select the xmldoc variable as the DocumentSource. This is the variable that contains the xml string from the Script task. Last... add the following string to the OuterXPathString: "/ROOT/*" (without the quotes). This means that it's gonna look within the root element.
Foreach NodeList Enumerator



















6) Variable Mappings
Map the variable folder to Index 0. This will store the path in this variable.
Variable Mappings



















7) Test the result
Now you can add your own tasks to the Foreach Loop container and use the variable folder to get the path. I added a simple Script Task for testing that shows the path in a MessageBox.
Example with MessageBox














8) More advanced script
You can even add a Traverse subfolders option with this recursive method.
// C# code
// This script loops through a folder
// and adds all subfolders to an xml string
using System;
using System.Data;
using System.IO; // Added
using System.Xml; // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_5da96344f1c4411ab56207579f2e5e91.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

// Variables for the xml string
private XmlDocument xmldoc;
private XmlElement xmlRootElem;

public void Main()
{
// Inialize XMLdoc
xmldoc = new XmlDocument();

// Add the root element: <ROOT>
xmlRootElem = xmldoc.CreateElement("", "ROOT", "");

// Add Subfolders as Child elements to the root element
GetSubFolders(Dts.Variables["User::startlocation"].Value.ToString());

// Add root element to XMLdoc
xmldoc.AppendChild(xmlRootElem);

// Fill SSIS variable with XMLdoc
Dts.Variables["xmldoc"].Value = xmldoc.InnerXml.ToString();

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

// Recursive method that loops through subfolders
private void GetSubFolders(String parentFolder)
{
// Get subfolders of the parent folder
string[] subFolders = Directory.GetDirectories(parentFolder);

// XML child element
XmlElement xmlChildElem;
XmlText xmltext;

// Variable for file information
DirectoryInfo directoryInfo;

// Loop through subfolders
foreach (string currentFolder in subFolders)
{
// Fill fileInfo variable with file information
directoryInfo = new DirectoryInfo(currentFolder);

// Create child element "Folder":
// <Folder>d:\foreachfoldertest\subfolder1\</Folder>
xmlChildElem = xmldoc.CreateElement("", "Folder", "");
xmltext = xmldoc.CreateTextNode(directoryInfo.FullName);
xmlChildElem.AppendChild(xmltext);

// Add child element to root element
xmlRootElem.AppendChild(xmlChildElem);

// Recursive call
GetSubFolders(directoryInfo.FullName);
}
}
}
}

Note: there are more options to accomplish the communication between the Script Task and the Foreach Loop container, but I wanted you to show an infrequently used option of the Foreach Loop container. Let me know what your solution was.