In SQL Server 2000 Data transformation Services (DTS) it was a bit of a
hack to be able to loop over files of a given type in a certain
directory and import them into your destination. It involved a lot of
"Glue Code" and a certain amount of fooling the package into going back
to a previous task because it still had work to do. Well thankfully in
SQL Server 2005 Integration Services (SSIS) that has all changed and
this article is going to show you how.
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 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 Flat
File Connection Manager (My Source File) and our OLEDB Connection
Manager (My Destination). The Flat 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 Flat File 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 ths 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.
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.
Here is how our variable looks with all its properties set.
Because the enumerator will only return us at most one value on every iteration we map our variable to an index of 0.
We have now configured everything as far as the Foreach enumerator is
concerned. We now need to set the rpoerties of the Flat File Connection
Manager. Highlight the manager in the tray at the bottom, right click
and choose properties.
The important part of this dialog is highlighted and that is
"Expressions". Click on the ellipses and we will be taken through to the
next screen where we can start to create the expression. In the screen
that follows, from the Property column drop the list down and choose
ConnectionString
Now hit the ellises button to the right and we are taken through to the
expression editor where we will build the actual expression itself.
Our requirements are pretty simple here and all we want to do is to
retrieve the variable we defined earlier. To do this simply drag the
variable from the list at the top to the expression text box at the
bottom. Property Expressions can become very complex and we shall no
dount be seeing more of them in future articles. After you have chosen
the variable click
OK
We now see that our expression is mapped to our ConnectionString property. Click
OK
Finally 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.
Source: http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx
