here's an example i like to post using BULK insert. you can do this via SSIs as well, but it's a different technique.

in my example below, i have 4 known folders, and an unknown number of files inside the folders.

I'm assuming all files are the same structure, and get imported into the same table. If you are doing something different, this solution is not for you.

note this also uses xp_cmdshell to get the list of files...if you have that disabled, and cannot enable it, you can't use this technique either.

--BULK INSERT MULTIPLE FILE

--a table to loop thru filenames drop table ALLFILENAMES

CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))

--the source table: yours already exists, but needed for this example.

CREATE TABLE BULKACT(RAWDATA VARCHAR (8000))

--some variables

declare @filename varchar(255),

@path varchar(255),

@sql varchar(8000),

@cmd varchar(1000)

--get the list of files to process:

--#########################################

SET @path = 'C:\DB\'

SET @cmd = 'dir ' + @path + '*.txt /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

SET @path = 'C:\DB2\'

SET @cmd = 'dir ' + @path + '*.txt /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

SET @path = 'C:\DB3\'

SET @cmd = 'dir ' + @path + '*.txt /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

SET @path = 'C:\DB4\'

SET @cmd = 'dir ' + @path + '*.txt /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

--#########################################

--cursor loop

declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.txt%'

open c1

fetch next from c1 into @path,@filename

While @@fetch_status <> -1

begin

--bulk insert won't take a variable name, so make a sql and execute it instead:

set @sql = 'BULK INSERT BULKACT FROM ''' + @path + @filename + ''' '

+ ' WITH (

DATAFILETYPE = ''char'',

FIELDTERMINATOR = '','',

ROWTERMINATOR = ''\n'',

FIRSTROW = 2

) '

print @sql

exec (@sql)

fetch next from c1 into @path,@filename

end

close c1

deallocate c1