sql - Dynamically creating table based upon the source excel file name and rowset -

i have folder, contains several excel files files contains same structure. need load these excel data of each excel file different table , the table need created dynamically using source excel file name , same schema of excel.

like file need loaded file table , file b need loaded file b table , tables need created dynamically through ssis package , if there error in 1 file while loading data should not stop there , need go next file leaving error file in folder.

please me...

completely agree @thorstenkettner. if @ possible load entries 1 table, additional column captures source file name. easier build, administrate and, crucially, use.

if, reason, have separate each file it's own table:

1. use for each loop container, read filenames source folder ssis variable.
2. within loop; use filename variable create table, passing parameter execute sql task.
3. use data flow task import file. source , target connections, within data flow, must use filename variable set source file , target table.

step 2 hardest part. rough statement little this:

/* @filename passed parameter.  * @createtable template statement.  */ declare @filename       nvarchar(255)   = ? declare @createtable    nvarchar(255)   =      '         create table <replace/>              (                 id      int identity(1, 1)  primary key,                 field1  nvarchar(255)       not null             );     ';   /* update @createtable current table name.  */ set @createtable = replace(@createtable, '<replace/>', @filename);  /* create new table.  */ exec (@createtable); 

further actions

this rough outline. each of steps require further research. if not comfortable ssis variables , expressions should research these first (starting microsoft's developer network). @ least need know how use variables update properties of connection.

you need consider how handle files have been imported.