The image below shows us how incredibly simple and clean the package will look when finished. There are some things worth pointing out at this stage. In the centre of the screen we see the Script task, Foreach Enumerator container and inside that we see the Data Flow task which houses the pipeline. At the bottom in the Connection Managers tray we see our Excel File Connection Manager (Customer List) and our OLEDB Connection Manager (CIT Global Destination). The Excel File Connection Manager is the one in which we are most interested for this article. Both of these managers are used in the Data Flow behind the DataFlow task. We will not be detailing the pipeline behind the DataFlow task in this article but it consists of a Excel Source moving data to an OLEDB destination.
Let's begin then by opening up the Foreach enumerator and moving straight to the Collection node in the tree on our left. Below we see our information already populated.
What we see on the screen is pretty self explanatory but let's go through it anyway. We have chosen to enumerate over a file collection and this is indicated by the value next to the Enumerator property at the top. We need to specify a folder over which to loop and for which type of files to look and we do that in the centre of the form. We are given three options as to what is returned when the loop finds a file in the folder at the bottom of the form. We can return the whole filename including extension and path, the name and extension or simply the name of the file found. Because our connection manager is going to need to know exactly where to find the file and it's name we have hosen the first option. The final thing we see on this screen is the ability to traverse subfolders. In our example we do not need to do this.
When the Foreach enumerator finds a file it needs to tell us about what it found and it does this by populating a variable. Click on to the Variable Mappings node now. Our package currently has no variables able to accept the name of the file so we are going to create a new one.
When the Foreach enumerator finds a file it needs to tell us about what it found and it does this by populating a variable. Click on to the Variable Mappings node now. Our package currently has no variables able to accept the name of the file so we are going to create a new one.
The next screen we see allows us to set the values of the variable. As we can see variables can be scoped in SSIS to certain executables in the package or to the package itself
We have now configured everything as far as the Foreach enumerator is concerned. We now need to set the properties of the Excel File Connection Manager. Highlight the manager in the tray at the bottom, right click and choose properties. foreach loop container configuration above image available just refer..
We now see that our expression is mapped to our ConnectionString property. Click OKFinally we can now see our File Manager's Connection string property being mapped to an expression in the properties of the manager.
That's all there is to it. When the enumerator finds a file matching our requirements it will set the correct property on the connection manager and this will be used by the pipeline at runtime.
