ImportMultipleExcelFiles

STEP1:Open BIDS

Create new integration Service Project, create a new
package say “ImportMultipleExcelFiles.dtsx”

STEP2: Create a Folder ExcelFiles (C:\ExcelFiles)and then create three
excel file inside namely First.xls, Second.xls and Third.xls, having
identical schema in all three 

In my case, all excel files above contain data in “Sheet1”
with below schema

City	Value
AY	2000
BY	3000
CY	4000

Obviously you can have your sheet names but keep a note, Sheet
name has to be uniform across all excel files that contains data or data that
has to be uploaded

STEP3:Right Click on Control Flow
Window, Select Variables

Add a variable “FileName”
at Package Level having type string and assign path to your first Excel File, C:\ExcelFiles\First.xls

STEP4: Go to Control Flow add a “For Each Loop Container” Component.

Go to “Collection” tab. Assign folder path and file type as
shown below

Then go to “Variable
Mappings“ tab and map variable created above like
below

STEP5: Drag a “Data Flow Task “inside
“Foreach Loop Conatainer”, double clickson Data flow task

Drag one “Excel Source” task ,
double click on this to get “Excel Source Editor “ Window

Now choose new and new window will
open , now browse to your first excel file that is C:\ExcelFiles\First.xls

Under “Name of the Excel Sheet” on “Excel
Source Editor “, choose Sheet1$

STEP6: Now drag “OLE DB Destination”
task  , connect “Excel Source” to “OLE DB Destination”

Now similar to step 6 point to
your database and create a table

Map both like below

STEP7:. IF above is successful, check your database values.
Now its time to make it Dynamic

Go to the Properties
of ”Excel Connection Manager”

Expand “Expressions”

Choose “Connection
String” property and assign value of Expression like below

“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + @[User::FileName] +
“;Extended Properties=\”Excel 8.0;HDR=YES\”;”

STEP8: Click on Evaluate Expression
for any errors

STEP9: Finally Execute Package