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: MSDN, Dougbert, Graham
