Loop through Excel Files using SSIS ForEachLoop Task

Load the Multiple Excel files data in a folder to SQL Server using SSIS ForEachLoop Task:

Using SSIS ForEach Loop Task it is easy to Copy multiple excel in SQL Server.

Suppose you need to load multiple excel files from a folder with same structure in all excel files into SQL Server. Let Say excel contain daily data.

To do this we require Two Tasks. One Data flow task and One For EachLoop Container.

The we have to create an Excel Connection and SQL Server Database Connection for source and destination.

We need to create the below variables for storing the File Path and File name. Actually we require only 2 variables. One is for storing the File Directory and another is for storing the 

File Name that will pick up by the ForEach Loop Container.

In Excel Connection Manager Properties.  Excel Connection creation can be implemented by using the 

Go  to Expression Field.

Double Click the Expression . Select ExcelFilePath Property and Add the expression
as mentioned below.

Set  ServerName Propery to Some hard coded excel file which exist so that it will not fail while running.

Other wise it will throw the below error.

[Excel Source [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[Connection manager "Excel Connection Manager 1"] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "Failure creating file.".

Also use the both servername and ExcelFilePath properties instead of ConnectionString property .Other wise you will get the below error. It always better to use ExcelFilePath property for the excel connection.

[Connection manager "Excel Connection Manager"] Error: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

Error: The result of the expression "@[User::FilePath]+  "\\"  + @[User::FileName]" on property "\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

 Once the Excel Connection Manager Configured then configure Foreach Loop.

 In the ForEach Loop Editor . Select Collection tab.

Go to Expressions. Set the Directory Property to FilePath and FileNameRetrieval to FileName.

The Check box Traverse Sub folders can be checked if need to check sub folders in directory.
Then Go to Variable Mappings . Map the variables to index.

The Index of FilePath should be zero and FileNamr should be one. It should follow the same order as mentioned in the Expression.

Other wise you may get the below error while executing the package.