SSIS Tutorial Part 20-Load Multiple Sheets from Multiple Excel Files to Different SQL Server Tables

In this video post, you will learn how to load multiple sheets from multiple excel files to different SQL Server Tables.
We often has these types of requirements where on single Excel file we have multiple sheets and each sheet is different by columns and we have to load the data from those sheets to different
SQL Server Tables.

In this video you will learn following Items

How to Load Data from Excel To SQL Server Table
How to use Data Conversion Transformation in SSIS To Convert Data thta we extracted from Excel to required Destination Data type
How to use OLE DB Destination to Load Data to SQL Server Table
Use Foreach Loop Container to loop throgh Excel files from a Folder
How to use Variables in SSIS Package to save Folder path
How to Write Expressions on Excel Connection Manager to Handle Multiple File Loads.
How to use DelayValidation property in SSIS Package

To Follow Step by Step this SSIS video tutorial By Tech Brothers

[nikolaybaranov]

An excellent solution for loading data from files with the same structure. Thanks, extremely helpful!

[fidelixwashington]

I did exacly the same shown in the video and it worked with 17 files.

Great content!

[mkh]

Thank you So much ! God Bless you Tech Brothers.


keep making fantastic tutorials like this.

[lakshmis]

awesome videos and explanations.Keep sharing

[jen]

Wonderful SSIS package demo video for education! Thank you so much.

[juanvelasquez]

This is an excellent series of videos. I'm just starting on SSIS and these videos are quite good. Thanks

[thirupathiambati]

Nice video, i learn new thing in SSIS. Thanks...Aamir

[sonjoysengupto]

Very eloquent demo, and thanks a lot for sharing this ... Very helpful indeed!!!

[devexpost]

Amir,
Thanks and well done on this series. Two notes on this one...
1. At 14:10 maybe it should be explained how looping is controlled by setting the Enumerator parameter to “Foreach File Enumerator”.
2. At 15:54 maybe it should be explained how user-variable “FullPath” is being assigned the Collection Value with Index=0. The mechanism is not clear.

Best Regards.

[domfp]

Really nice video, i´ve been looking for something like this before, thanks bro.

[dergimorka]

THANK U VERY MUCH FOR UR TIME AND GOOD EXPLANATIONS

[jagdeepjulka]

Thank you very much... sir for sharing your knowledge...

[lksmhjn]

thank you so much for that post its really helpful for me .. keep uploading.. thanks..

[madhaviravoori]

Thank you, it is really nice, very helpful to me.

[kimiqin]

Thank you so much~~ That's really clear and helpful!

[shivamothukuri]

Awesome video and explanation. I have one doubt in the above video. Why we have taken Excel path as Fullpath? Please answer

[devexpost]

*Description of what is occurring:*
1. The input-file folder name is user supplied by the Package level variable "InputFolderPath" to the Foreach Loop Container.
2. Within the Foreach Loop Container, the looping occurs based on iteration over an enumerator type, and the enumerator type chosen here is “Foreach _File_ Enumerator” in order to cause iteration over files in the Directory based on the Enumerator Configuration which was set to include Files: “*.xls”.
3. With each iteration of the Foreach Loop, the Foreach Loop Container retrieves the current (next) “Fully qualified” .xlsx file name from the Directory folder as a “Collection Value”, and user-defined Container-level variable “FullPath” is mapped to the Index=0 string of this Collection Value (which now contains the fully-qualified path and file name of the current Excel file).
4. The Container then passes control to the Data Flow task which calls the Connection Manager and supplies the Container-level user variable “FullPath” whose value is dynamically assigned to the Connection Manager’s “ExcelFilePath” parameter.
5. The Connection Manager then opens the current Excel file identified by the “ExcelFilePath” parameter and permits the Data Flow task to process it. When the Data Flow task is complete, control passes back to the Foreach Loop Container which then looks for a next Excel file in the Directory. If another file is found, the Container continues again with step #3 above.
6. If no more files are found in the Directory then the Foreach Loop terminates.
.

[DharaStudies]

Hi Amir, Really good, if you don't mind, can you please share something using one data flow

[sonalisingh]

If you already configured your mutiple source and destination then what was the purpose of loop, how loop is helping. Can you please explain

[hari]

really worth watching. thanks a lot :)
Is it possible to Export sql data to different excel file based on category ??