Import multiple Excel files into SQL Server 2008 R

Question

I have to insert data into two tables from two different excel sheets using SSIS packges.

So, please some one tell me How to import multiple Excel files into SQL Server 2008 R2 using SSIS Packages?

Answer 1

Use Foreach Loop Container --> Foreach File Enumerator. Then put your Data Flow in Foreach Loop Container. Table structure and sheet name have to be equal in every Excel file.

Here is nice tutorial: http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html

Answer 2

You can create and use variables to hold Excel file path, then implement the package within a for-each-loop container to loop trough each excel files in the folder. Make sure that the files are having similar data format and same extension. Create an integration service project in Visual Studio and follow the steps below:

1. Right Click on canvas and add Variable say "FilePath" and set type as String

2. Drag a for-each loop to canvas

3. Right click on for-each-loop and select "Edit" and add path and file extension and then select the variable from "Variable Mapping" tab as shown:

4. Add a Data-flow task in to the loop and double click to open it.

5. Add an Excel source and OLEDB destination (if destination is SQL Server). By right clicking on these controls and choosing "Edit", you can select appropriate folder for Excel and its Data Sheet as well as specify SQL server database credentials in the destination. Connect source and destination using blue arrow. Check and modify mappings between columns, if needed.

7. Create an expression to read each file to the variable. This can be done by selecting created "ExcelConnectionManager" and select its "Expression" property and expand to set variables to "ExcelFilePath"

8. And that is it. You are ready to execute the package by coming to Control flow tab and select "Start".

Ref: https://www.encorebusiness.com/blog/import-data-from-multiple-excel-files-sql-ssis/

Answer 3

since you have only 2 excel files and only two tables I don't see why use a foreach loop. Just follow this link and do it directly

If you need to insert from both excel files to both databases you can use a multicast component to create a copy of a dataset