There must be some way to loop through all the files within a given folder
and load / transform them into a single DB table....

can anyone suggest a solution using DTS or do I really have to code all this?

--
Michael Calvin

Yep

I would look at this article

Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)

Allan


"Michael C" <MichaelC (AT) discussions (DOT) microsoft.com> wrote

You can use an fso object within an activeX script in DTS to get a
listing of all files in a folder. Then loop through each file and
import it.
Usually I set up the fso to get a listing, then loop through each file,
calling dtsrun with the filename as a global variable to import that
particular file. You can call a data pump task within the same dts
package, but I use dtsrun to split it out so that other developers can
follow along easier (if their not that familiar with dts).
Obviously you'll have to do any checks to the filename and contents etc
if there are different files within the folder - you don't want to break
any transformations that you've defined.

Simon Worth

Michael C wrote:

I was actually waiting for you to reply to that Allan, then thought, "ah
heck, why not?".
Then, you replied before I hit send!


Simon Worth

Allan Mitchell wrote:

No problems. Different approaches to the same problem is good for provoking thought.


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote