This project is read-only.

Readme_ForEachDirectory Sample

11/05/2008 21:36:06

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
This code sample demonstrates how to create a custom enumerator and a custom user interface to set properties for the enumerator using the .NET Framework and managed code.
This sample is not supported on Itanium-based operating systems.

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Running the Sample

If you already know how to locate, build, and install code samples, you can go directly to the section, testing , and read about how to configure and run the code sample.


The ForEachDirectory sample requires that the following components are installed.
  • .NET Framework SDK 2.0 or Microsoft Visual Studio. You can obtain .NET Framework SDK free of charge. See the Books Online topic, Installing the .NET Framework Documentation.
  • Microsoft SQL Server Integration Services


If the code sample was installed to the default location, the sample is located in the following directory:
C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\ForEachDirectory Sample
The C# solution for the code sample is located in the CS directory, and the Visual Basic solution is located in the VB directory.
For information about the two-step process required to install the samples, see Considerations for Installing SQL Server Samples and Sample Databases.

Building the Sample

If you have not already generated a strong name key file, use the following procedure to generate this key file.
To generate a strong name key file
  1. To open a Microsoft Visual Studio command prompt, click Start, point to All Programs, point to Microsoft Visual Studio 2008, point to Visual Studio Tools, and then click Visual Studio 2008 Command Prompt.- or -To open a Microsoft .NET Framework command prompt, click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.
  2. At the command prompt, use the change directory (CD) command to change the current folder of the command prompt window to the Samples folder. The key file that you create in this folder will be used by all SQL Server code samples. Note: To determine the folder where samples are located, click Start, point to All Programs, point to Microsoft SQL Server 2008, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <drive>:\Program Files\Microsoft SQL Server\100\Samples.
  3. At the command prompt, run the following command to generate the key file:sn -k SampleKey.snk Important: For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.
  4. You will need the public key token from the key file in a subsequent step. To obtain the public key token, first extract the public key from the key file by running the following command at the command prompt:sn -p SampleKey.snk SampleKeyPublic.snk
  5. Display the public key token by running the following command at the command prompt: sn -t SampleKeyPublic.snk
  6. Copy the public key token to the clipboard or save for later use.
To build the sample
  1. Open Microsoft Visual Studio 2008.
  2. From the File | Open menu, click Project and open the ForEachDirectory.sln in the desired Samples directory, which is CS for the C# language, or VB for the Visual Basic language.
  3. In the Solution Explorer, select the ForEachDirectory.cs or ForEachDirectory.vb code.
  4. In the code, locate the DtsForEachEnumerator attribute. In that line of code, replace the existing value of the public key token in the* UITypeName* attribute property. The alphanumeric value after "PublicKeyToken =" should be replaced with the one displayed in the sn -t command from a previous step.
  5. Click Build / Build ForEachDirectory on the menu to build the DLL for the project.

Installing the DLL into the ForEachEnumerator folder and the Global Assembly Cache

This sample is provided in both Visual Basic and C#. To distinguish between the assemblies for each version of the sample, the name of the output assembly has CS or VB appended. After successfully building the component, the component must be copied to the ForEachEnumerators folder, and then added to the Global Assembly Cache in order to use it in Business Intelligence Development Studio.
To install the component into the ForEachEnumerators folder
  1. Open Windows Explorer or your preferred application for working in the file system.
  2. Copy the assembly (ForeachDirectoryCS.dll or ForeachDirectoryVB.dll) to the folder located at %system%\Program Files\Microsoft SQL Server\100\DTS\ForEachEnumerators.
To install the component into the Global Assembly Cache by dragging and dropping the assembly
  1. Open Windows Explorer or your preferred application for working in the file system.
  2. Drag the assembly from the ForEachEnumerators folder to the folder where the global assembly cache (GAC) is located, at %system%\assembly.
To install the component into the Global Assembly Cache by using gacutil.exe
  1. Open a Command Prompt window.
  2. Type the following command to install the C# version of the component and the user interface into the global assembly cache:** gacutil.exe -iF "c:\Program Files\Microsoft Sql Server\100\DTS\ForEachEnumerators\ForEachDirectoryCS.dll"
  3. Type the following command to install the Visual Basic version of the component and the user interface into the global assembly cache:** gacutil.exe -iF "c:\Program Files\Microsoft Sql Server\100\DTS\ForeachEnumerators\ForeachDirectoryVB.dll"
For simplicity, the user interface for this sample has been put in the same project as the task code; therefore, copying the DLL into the assembly directory is all that is required. If you decide to code the user interface in a different project, then you must add the DLL that contains the user interface to the global assembly cache, in addition to the DLL that contains the code. Only the code must be compiled into a specific directory, however.

After you complete these steps the enumerator is available in the For Each Loop Editor of the For Each Loop container.
To use the sample in Business Intelligence Development Studio
  1. Open Business Intelligence Development Studio.
  2. From the menu, click File | New | Project.
  3. From the New Project dialog box, in the Project Types, click Business Intelligence Projects and in the right pane, click Integration Services Project.
  4. In the Name box, type ForEachDirectory as the name for the new project.
  5. In the Location box, select the location where you want to save the file. Click OK.
  6. From the Solution Explorer dialog box, double-click Package.dtsx.
  7. In the Location box, select the location you want to save the file to. Click OK.
  8. From the Solution Explorer dialog box, double-click Package.dtsx.
  9. Add the ForEachLoop container to the Control Flow surface of a package.
  10. Open the Foreach Loop Editor by double-clicking the ForEachLoop container added in step one.
  11. Click the Collection node, and select the ForEachDirectory enumerator from the Enumerator dropdown.
  12. Specify the values of the properties of the ForEachDirectory enumerator, and then click OK.

Testing the Sample

After you have added the sample component to a Data Flow task in a package, you can configure it in SSIS Designer. The ForEachDirectory enumerator has five properties that control its execution behavior.

Property Description
RootDirectorySource Specifies the source for the RootDirectory property which can be set by a ConnectionManager, Variable, or DirectInput.
RootDirectory Specifies the directory where the enumerator begins its enumerating.
IncludeRootDirectory Specifies whether the root directory is included in the directories that are enumerated.
EnumerateSubFolders Specifies whether the subfolders of the folders that is contained in the root directory are enumerated.
SiblingFoldersBeforeSubFolders When EnumerateSubFolders is true, specifies whether the enumerator enumerates the sibling folders first, or whether the complete directory structure is enumerated first.

© 2008 Microsoft Corporation. All rights reserved.

Last edited Mar 19, 2009 at 6:29 AM by leilu, version 10


GeorgeQiao Oct 14, 2010 at 4:54 AM 
The control is what I need exactly. However, when the for each loop encounters a folder with "access denied", the for each directory loop throws an error and do not continue to scan further folders. Where and how do I make changes to change this behaviour? I want the loop to continue regardless any error (e.g. doesn't have access to the folder/subfolders).