This article explains the Foreach File enumerator functionality of SSIS for each loop container.
OverviewThe For each loop container implementation is similar to a foreach loop structure in programming languages. SSIS provides 7 types of enumerators for each loop container.
This article explains the Foreach File enumerator.Foreach File enumeratorThe file enumerator loops through a collection of files within a folder and makes it possible to execute something, for example a Data Flow Task, for each of the files without manually changing the connection string.ScenarioThe scenario is to load country specific customer data in a text file to a SQL Server database table. See the following screen of source folder to get a better idea.Implementation OverviewWe will be using a data flow task inside the Foreach Loop Container. The Foreach loop container will iterate through each file and each time the data will be loaded to the destination tables using the dataflow task. To hold the vale of the file path in each iteration, we need to create a string variable.Create variableRight-click on the Control Flow and select variables. Create a variable of type string under the scope of the package or Foreach lopp Container.Foreach Loop ContainerDrag and drop a Foreach Loop Container into the control flow and double-click on it. Navigate to the collection tab on the left panel.
Navigate to Variable Mappings tab to assign the file path in each iteration to the variable that we created earlier.Data flow TaskAdd a dataflow task inside the Foreach Loop Container and navigate to it's dataflow tab. Since the source is a text file, our source will be a Flat File Source. Drag and drop a Flat File Source into the dataflow and double-click on it.Create a new connection manager by clicking on the new button. Here we need to provide an input file path for design purposes and later we can change this to an expression to make use of the file path from the variable.Create an OLE DB Destination and point to the dataflow path towards the destination. Ensure that all source columns are pointed to the correct destination.The final and most important step is to change the flat file connection path to the loop variable to read each file in the iteration. Right-click on the connection manager and select Properties.Click on the expressions property and make a connection string as an expression.Package ExecutionExecute the package and verify the result in the destination table.ConclusionI hope you now have a clear idea of the implementation of the Foreach File enumerator which is very helpful for file parsing to Databases. Thank you for reading.
Building Standalone Data-Driven Applications using SQL Server Compact 4.0