Import multiple Excel files data into SQL Server Destination (SSIS)

How Import multiple  Excel files data into SQL Server Destination in SSIS?

solution:

Take a package scope variable

Name : Excelsourcepath
Datatype : string
Value :C:\Avi\msbiinput\emp1.xslx

Go to  Control flow and place a for each loop container 

Double click and set the properties like below 


collection page :

Enumerator : For each file enumerator
Folder         :C:\Avi\msbiinput
Files          : *.xlsx

check the button Fully qualified 

  


Go to Variable mapping Page 

Variable

Set User  : Excelsourcepath


Place dataflow task in the container :
 

Goto Dataflow tab 

---> Place Excel source adapter and configure it

 


Go to column page 

To check which adapter using which connection manager

Just right click on adapter
and select the show advanced editor

Then right click on the Excelconnectionmangers --> Properties

Property                    
 Connection String              

               Expression

"Provider =Microsoft.OLEDB.12.0
data source ="+@[User:Excelpath]+";
Extended Properties =\"Excel12.0;HDR=yes\";"

Place a DATA Conversion

and configure it

We need data  Conversion transformation to convert the data types when we are sending  the data from excel to SQL.

place a SQL Server Destination

Configure it as same above dataflow task

Then Press F5 to run the package .