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

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

Great tutorial!

In my scenario, I need to loop through all sheets of all files but the sheets and files are not the same. I tried to create 2 loops, 1 for the file enumerator and another for the sheets, but I can't make it work. Can you help me with that?

To add to that I need to specify the cell range at each sheet that is different. 😂

bro could you please share the excel sheets also for the practice

Hi sir

my loop is not stopping after entering the records from both the files and both the sheets

Sir please tell me that how can I export adventureworks 2019's person table from SQL to excel

Hi thanks for the content, but when running the package i am recieving error at "excel source", and error is as
follows: [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 Access Database Engine" Hresult: 0x80004005 Description: "External table is not in the expected format.".


Any got resolution for this please help me out. thank you once again.

hi..nice video.great help but i am working this task but not worked..it is second excel file not dumped data in sqlserver..please help waht issue?

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

If we have multiple file but we dont have same sheet name in files. Column names are same in every file. Can we do it through SSIS..?

Thank you So much ! God Bless you Tech Brothers.


keep making fantastic tutorials like this.

Hi Techbrothers,


I am having trouble loading the excel data into SQL tables. The excel text column i have has alphanumeric values and when it is loaded in to SQL table value becomes NULL.
I tried converting it using data conversion from float to string but it isnt working

Very helpful, thanks a lot.

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

I have never used SSIS. Why are you in Visual Studio to create an SSIS package, instead of being in SSIS itself?

THANK U VERY MUCH FOR UR TIME AND GOOD EXPLANATIONS

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

I am having files 5 number of files with multiple sheets with same metadata kindly advise how to load in Sql by SSIS

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

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

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

My pkg is executing but I am not getting any data in my destination file . Getting this in the output list - Any help will appreicate. I recheked few time the file path and file in the folder and its all correct. What could be causing this. Driving me crazy - "For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.
SSIS package "C:\Users\thunoli\Documents\Visual Studio 2010\Projects\fullnameDerivedTest\multipleSheet source.dtsx" finished: Success."

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.
.

awesome videos and explanations.Keep sharing always!!!!!!!!!!!!!!!!!!

did you do encryption and decryption through ssis ?

Hello Amir,

I have scenario ,
I have to load data from multiple excel file (having multiple sheet and same meta data) into single sql table using 1 (Single) DFT
can you please tell the steps .

if the name of the sheets are different in two files,will the package work?

How to  Load Multiple Sheets (with diferent columns) from Multiple Excel Files to Different SQL Server Tables + skip first xy rows

Will it work with flat files instead of excel files? I have some flat files to load some tables in db

Hi Amir ,
I have one doubt . I am trying to load two excels with 2 sheets per excel .
Problem is i am able to load the second sheets but first sheet data is not getting loaded and when i put the data viewer in the data flow tab , i am able to see the data is moving toward OLEDB . so what can be the reason that data is not getting loaded to the OLEDB

nice one on the excels... lets say there are multiple sheets(about 30) on each of the excel file and lets say there are about 20 excel files. in such case, we can not create 30 DFT's for 30 sheets. So creating another FOR EACH LOOP for sheets inside the outer FOR EACH LOOP which runs for 20 excel files would work right.. ? Please correct me if I am wrong...

Since I am using excel files containing large data to load to one SQL server table, there are so many sheets in every excel workbook. After importing the excel files to SQL Server the rows of one sheet is getting mixed with another in the final table. How can we correct that so that data comes as it is in the excel workbook sheets?

great thanks a lot for videos....

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

Hi Amir I just want to know is there any video for this one using  Script task for the Same Task

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

It would be great if u make one deployment(SQL server) video for this package.

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

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

HI Aamir can u let me know is their any way to run multiple packages using a single
execute package task

hello aamir sorry to ask u so many question at a time
iam trying to learn as many as packages u have showed
nad my problem is in
Load Multiple Sheets from Multiple Excel Files to Different SQL Server Tables in SSIS Package
u said to have same excle connection manager in the second data flow and select the sheet
but if select the same excel connection manage iam unable to get the second excel instead iam getting the details (columns) of first connection manger is their any thing iam missing

Thank you for useful video, but I want to load multiple individual Excel files to multiple data tables in the same database (e.g. filename = "abc" and oledb = "abc") then continue with teh same thing for 61 files. How can I do this? can I have some help?

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

I enjoyed watching this video and recommend others to watch.

Can we use Nested ForeachLoop so that Outer loop will be getting the file and inner loop will be reading sheets and inserting data to database?

Thanks a bunch