CODEWITHSUNDEEP
sqlsql-serverssis
Anilkumar
Anilkumar

Reputation: 11

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

I have folder, which contains several excel files all files are contains same structure. Now I need to load these excel data of each excel file into different table and the the table need to be created dynamically by using source excel file name and the same schema of the excel.

Like File A need to be loaded to file a table and File B need to be loaded to File b table and the tables also need to be created dynamically through ssis package and if there is any error in one file while loading the data it should not stop there and need to go to next file by leaving the error file in the folder.

Please help me...

Upvotes: 1

Views: 1343

Answers (1)

David Rushton
David Rushton

Reputation: 5030

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:

  1. Use the For Each Loop Container, to read the filenames from the source folder into a SSIS variable.
  2. Within the loop; use the filename variable to create your table, by passing this as a parameter to the Execute SQL Task.
  3. 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.

Upvotes: 1

Related Questions