How to import Excel files with different names and same schema into database?

Question

How to import data into a sql server table in SSIS from an excel source file that has different file names each time (sample excel filenames: abc123, 123abc,ab123c etc.,)

Solution

One possible way of achieving this is by using ForEach Loop container in the Control Flow tab and then placing a Data Flow task within the Control Flow task. I have explained the process in the below example. This example uses SQL Server back-end as the destination and Excel 97-2003 format .xls as the source files. Please note that the Excel files should be of same format.

Ste-by-step process:

1. Create a table named dbo.Location as shown in screenshot #1. This example will populate this table by reading three different Excel files having the same layout. The screenshot shows an empty table before the package execution.

2. Create two Excel files in path c:\temp\ as shown in screenshots #2 - #4. Notice that both the Excel files have the same layout but different content.

3. On the SSIS package, create three variables as shown in screenshot #5. Variable FolderPath will contain the path where the Excel files are located; FileExtension will contain the Excel file extension (here in this case it is *.xls) and FilePath should be configured to point to one valid Excel file (this is required only during the initial configuration of the Excel connection manager).

4. Create an Excel connection in the connection manager pointing to one valid Excel file as shown in screenshot #6.

5. Create an OLE DB Connection in the connection manager pointing to the SQL Server.

6. On the SSIS package, place a ForEach Loop container and a Data Flow task within the ForEach loop container as shown in screenshot #7.

7. Configure ForEach loop container as shown in screenshots #8 and #9. By doing this, variable User::FilePath will contain the full path Excel files located in the folder c:\temp\ with the help of variables FolderPath and FileExtension configured on the Collection section.

8. Inside the data flow task, place an Excel source to read Excel file data and OLE DB destination to insert data into SQL Server table dbo.Location. Data flow task should look like as shown in screenshot #10.

9. Configure the Excel source as shown in screenshots #11 and #12 to read the data using Excel connection.

10. Configure the OLE DB destination as shown in screenshots #13 and #14 to insert the data into SQL Server database table.

11. On the Excel connection in the connection manager, configure the Expressions ExcelFilePath and ServerName as shown in screenshot #15.

12. Sample execution of the data flow task is shown in screenshot #16.

13. Screenshot #17 displays the data in the table dbo.Location after package execution. Please note that it contains all the rows present in Excel files shown in screenshots #3 and #4.

14. On the Data Flow task properties, Set the DelayValidation to True so that the SSIS doesn't throw errors when you open the package.

Hope that helps.

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Screenshot #11:

Screenshot #12:

Screenshot #13:

Screenshot #14:

Screenshot #15:

Screenshot #16:

Screenshot #17:

Screenshot #18:

Comments

[Lrn]

What expressions should be used on the "Flat File Connection Manager" for the same process to work with .csv files? I am talking about Screenshot #15 but with .csv files. The Expressions are different for csv files that uses flat file connection manager.

[Lrn]

That helped. Would it be possible for you to show how different XML files can be imported as well? The XML source doesnot seem to have a Expression Editor?! Thanks for your help and your work to the community.

[Faizan Mubasher]

Awesome explanation! Excellent

[Si8]

How can I use your answer for my question here: stackoverflow.com/questions/23158021/… (I am getting an error every time I change the Expression)

[Tab Alleman]

Why hasn't this answer been accepted as the answer?