SQL Server BI step with step SSIS 3

This article supporting source code

The last time we introduced a simple data export and import, but only to operate on a single file, what if we wanted to perform a data import on all the files under a directory at the same time? Quite simply, SSIS provides a Foreach Loop container in the control flow, which is easy to understand, It is capable of looping traversal execution compared to a sequence container, and can repeatedly execute control flow on each member of the specified enumerator:

As you can see, the Foreach Loop window traverses a folder with some settings or a regular match, using the Foreach Loop container to enumerate:

ADO Record set row and schema information

• File and directory structure

• Systems, packages, and user variables

SQL Management Objects (SMO)

First we prepare the data, rename the last generated Excel file to Product1.xls, create a new Product2.xls, copy the productid<800 rows from Product1.xls to the new Product2.xls ( Note that the two Excel sheet names are consistent), and then create a new Foreachinput package in our SSIS project, the Foreach Loop container comes in, double-click to set the collection, specify the file path of our Excel file in the folder, such as F:\, Write *.xls at the file name, this will be able to match all of the suffix named xls file, and then execute the container inside the process, but in our container to access each of the Excel files, we must obtain each enumeration variable is the name of the file, you can set the variable mapping implementation.