Phil Parkin (4/21/2015)

---

OK, I'll give you an outline. There will be many ways of achieving this, some of which will have fewer calls to the DB engine, but here is just one.

Assumptions

1) All of the files are in the same folder.

2) All of the files have the same structure (same columns, same column order)

3) All of the files are to be loaded into the same table

Preparation

Before creating your package, create a new 'scratch' table wrk.FileLoadDetails(FileNo int PK, FilePath varchar(255)).

This table will be refreshed and repopulated every time that the package executes.

Package logic

a) Execute SQL task truncates wrk.FileLoadDetails

b) Execute SQL task populates wrk.FileLoadDetails with file info (using something like xp_DirTree) for all of the files to be loaded. FileNo is the equivalent of an identity(1,1) column, though I'd use Row_Number() in my source query just to maintain full control.

c) Return the value of max(FileNo) to a package variable (MaxFileNo).

d) Configure the FOR loop to run from 1 to MaxFileNo.

e) Within the FOR loop

-- An ExecuteSQL task gets the filepath for the current value of the loop counter

-- A dataflow (flat file source to target table) comes next. The data flow's source file path is set by an expression to use the variable populated in (c)

-- Archive the flat file to ensure that it does not get processed more than once.

Job done.

But a foreach loop avoids all of this faffing about.