Completely agree with @ThorstenKettner. If at all possible load all entries into one table, with an additional column that captures the source file name. This will be much easier to build, administrate and, crucially, use.
If, for some reason, you have to separate each file into it's own table:
- Use the For Each Loop Container, to read the filenames from the source folder into a SSIS variable.
- Within the loop; use the filename variable to create your table, by passing this as a parameter to the Execute SQL Task.
- Use a Data Flow Task to import the file. The source and target connections, within the data flow, must use your filename variable to set the source file and target table.
Step 2 is probably the hardest part. A very rough statement would look a little like this:
/* @FileName is passed as a parameter.
* @CreateTable is a 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 with the current table name.
*/
SET @CreateTable = REPLACE(@CreateTable, '<Replace/>', @FileName);
/* Create the new table.
*/
EXEC (@CreateTable);
Further Actions
This is just a very rough outline. Each of the steps will require further research. If you are not comfortable with SSIS variables and expressions you should research these first (starting with Microsoft's Developer Network). At the very least you will need to know how to use variables to up update the properties of a connection.
You will also need to consider how to handle files that have already been imported.
