Loading flat files with DTS

[Peponne]

Let's consider several (100) flat files WITH THE SAME STRUCTURE in a directory.

How can we load them (script ?) into a single table with DTS, without using the Bulk insert task repetively (100 times !!!) ?

(Bulk insert task points on only one file, not on a folder content).

Thnaks if you have a proper solution.

[andi_g69]

If they all have the same structure you might concat the files into one file.

E.g. your DTS package calls a batch file as first step and your data pump is the second step.

just a thought...

[skhanal]

How about writing a procedure doing this

1. create a temp table with one varchar column.

2. Get list of files in a temp table using - insert into #table exec (master.dbo.xp_cmdshell 'dir /b c:\yourdir')

3. Then call bulk insert for each file in the temp table in a while loop or a cursor.