Tutorial - How to Load Multiple Excel Files into SQL Server via SSIS

[@mazkaibil9108]

This is probably the best video i have ever seen regarding loading multiple excel files to database. Thank you! :) Is it possible please to also include the part where you archive the files?

[@msk2982]

Here you go: kzbin.info/www/bejne/a5-Vp2BmZ96tgc0

[@fercho810116]

Great! I had a hard time with the 32-64bit version thing. Just found out how to jump over that and this video worked perfectly to upload the Excel files into a SQL table. Thanks!

[@msk2982]

Yea it's crazy how often people hit that issue. I even made a separate video. Glad the video helped!

[@jasonli4892]

You are my man. you make my life and my career to be easier! Thank you so much!

[@msk2982]

No problem Jason glad I could help. I just wish I had more time to make more videos. I've got so many solutions to complicated issues but no time.

[@jasonli4892]

@@msk2982 Understand. I hope you can make more videos in the future! Thank you :)

[@matthew_brown]

At 1:48, How did you open the results below?

[@sa2144]

Very simple to understand. Great job !!!

[@tedkrapf1302]

Dude! Such a great video. THANK YOU so much for taking the time to put this together.

[@hallythejester1234]

I didn't find an Integration Services Project; does Windows Service work instead? If not, which package is the integration services project part of

[@pampharmakis9003]

Thanks so much Mohamed, saved me a lot of time. I do have one request because your videos are so good and easy to follow, could you PLEASE post how you would capture the file name along with the data being loaded.

[@msk2982]

Great suggestion. I will try to do so in a newer video.

[@EasyStreet88599]

Thank you. You used only two variables, what about the other ones?

[@Guilopes99]

What if I have a mix of excel and access files? And that I want to update in the future with new files

[@matthew_brown]

Love the video but it is very fast. I have no idea which version or workload of VS to install.

[@hweegeem]

Hi, I have another question: How if the data is keeping on increasing as long as machine working. Do you have any ideas? Thanks.

[@hutchm92]

Thank you! This saved me some time and headaches!

[@msk2982]

hutchm92 No problem! Glad I could help.

[@khasan1973]

HI Mohamed Khan this one is very useful. Thanks. Can I also save the filename in the destination file ?

[@nagisupercell]

Thank you. This is helpful.

[@crystalcarpenter8310]

Great grwat video. Can you make more videos from excel file, clean data, to upload into an ETL in visual studio? 🙂 specific the path to each file confuses me something....paths maybe? 🙂 thank you

[@afzaalawan]

Mashallah excellent too the point and quick

[@HumairDawood]

Care to explain how did you get those tables in Server management studio before jumping onto SSIS?

[@msk2982]

Which tables exactly?

[@maximrytych4725]

Thanks a lot! It really helped me.

[@msk2982]

Glad I could help!

[@anithamsb3358]

Hi. Thanks for the video. Could you please help with data conversion for dates, alphanumeric and numbers (integers and decimals).

[@msk2982]

Anitha, can you be more specific? Data conversions are super easy but I can make a video really quick for you if you let me know what you are looking for.

[@anithamsb3358]

Thanks for your response. I agree that it should be the basics.. But I am not able to find the actual difference between the various options (such as char, nvarchar, int etc). If that is too simple, can you pls provide the reference links? To be precise with my requirements, I have data with (names, unique id numbers (e.g.. ANS001), dates, currency (decimals) etc. I am really not sure if I am choosing the correct data type while creating the table.

[@msk2982]

For anything that has numbers and letters I always go with varchar unless it's a fixed width. Numbers generally int unless it's a very large number then bigint. Since i mostly work with nonunicode I don't use Nvarchar but you can use that if you work in other languages.

[@josue10hd]

Muchas gracias! Funciono con SQL Server 17 y Managment 18 con Visual S Community 19 :D

[@tomriddle8708]

Great I have a doubt what if we have excel files with different column

[@msk2982]

Hey Tom you are correct. This is for files with the same layout. If you have dynamic file layouts you would need to use a different method.

[@tomriddle8708]

@@msk2982 Thank you 😊 but that's my problem I am stuck coz idk how to do can you give me idea or is there any possibility you do a video on that related

[@razvanzota6433]

Hey Mohamed Khan, thanks for the video, i do encounter one issue: One of the column cannot be converted from unicode to non-unicode string data types. Also please share the link to the video where you changed the package to 32 bits since i also want to run it with .xlsx format. Thanks a lot

[@msk2982]

Hey Razvan. For the column use the data conversion to convert the column into a string. Here is the link for the video to change the package to 32 bit. kzbin.info/www/bejne/q33MeKOQfLeYeZI

[@SandeepYadav-vm5hd]

what about archive files ?

[@joshi7405]

I am currently working on IPBC for Colaberry and I am stuck on an SSIS assignment Do you mind helping me out? I have watched your video times and I am getting an error.

[@erenhan]

perfect tutorial, a serious workload decreases thank you

[@Mebueso07]

been trying this method but won't work with the format .xlsx (Excell 2016), been googling but isn't clear, any advice!!

[@msk2982]

Moises Bueso hey Moises sorry for the late reply. Try setting the package in 32 bit mode. I have another video showing how to do it. I would link it but I'm on my phone

[@perfectgaming788]

I don't think it's a debugging issue, try saving the newer Excel file in a different older format before loading it. Try 97-2003.

[@hackifysecretsau]

Expression cannot be evaluated.Additional Information: There was no expression to compute.An attempt was made to compute or get the string of an empty expression. I'm still getting this error and I added all the variable and strings.

[@maxmeista]

Hi msk2982, firstly, thanks a lot! I've got to import data from an Excel file into a DB on a recurrent basis, I've found a few methods, but so far this one is the neatest! Secondly, I noticed that you created an Archive_Folder variable, however, in your end result, your spreadsheets don't appear to be copied there. After importing the data from the spreadsheet, I would need to transfer it to the archive folder and remove it the original folder. Would you have any source that you can refer me to and that would explain how to get this done? Thanks!

[@msk2982]

Max, first off I'm glad you like the video! Yea I noticed that too. I originally created the video for me and 2 or 3 people to reference to because this process was hard to memorize. I added the archive folder but forgot to add the steps to archive lol. But since you asked, I will make another video showing how to archive files. Go ahead and subscribe to me that way you know when the video is uploaded. (You can unsubscribe after I upload the video if you want)

[@msk2982]

Hey Max, just an update, added the video here: kzbin.info/www/bejne/a5-Vp2BmZ96tgc0

[@maxmeista]

Awesome! Thanks a lot msk and yup I subscribed to your channel too in case if you get video-happy and post more eventually =DOut of curiosity, what's your opinion concerning the pros and cons of this method (SSIS) vs the linked servers method? After some reading, it seems that SSIS is really a sturdier option, however, one of my colleagues seems to really favour the linked server method. I know that often ppl just stick to a certain way of doing things as long as it allows them to accomplish their work, so I was curious whether you tried both, have any preferences and why?

[@phil7218]

You created an archive folder but didn't move the files.

[@JohnnyBlack985]

SOLVED! Hi! Thanks for the tutorial! I could recreate the same for my files with no problem and also the archiving part. First run was successful with loading & archiving. But when I tried to run it for the second time with other files, it didn't run as it was looking for the file that is specified in the File_Path variable. I need this solution to run scheduled and load all *.xlsx files from a folder. Can I make SSIS not to look for the exact file specified in the File_Path variable at each run? Thanks a lot in advance!

[@msk2982]

Glad you figured it out!

[@capitankak]

Could you share your resolution? I'm having the same issue - the exact file name needs to exist otherwise it errors upon execution.

[@gdsjr93]

@Mohamed Khan can you help us with this? @Jonatán Fekete

[@rolely1545]

Thanks for this. I encountered a problem, when I add new data to the excel file, and generate it again in sql, the old rows are repeated. Can you help me with this? Thanks.

[@princeshivansh18]

Truncate your previous tables then you are done. I hope it helps

[@IamEffiom]

Hello Pls I have a problem which needs a solution I want to know how to load multiple excel sheets as tables in SSIS In such a way that the sheets in an excel file becomes SQL table Examples an excel file named Customer with sub sheets with names credentials and products They will be loaded to SQL Server as different tables with names dbo.credentials and dbo.products A video would be well appreciated Thanks

[@ayushisahu9926]

Thanks the vedio was so helpful but facing few error

[@LL-hj8yh]

Not sure why it keeps giving me an error, saying Opening a rowset for "xxxxxx" failed. Check that object exists in the database. I'm not sure why, I changed all delay validations to true. Still no luck. Any suggestions?

[@msk2982]

Try recreating a new data flow task with the same stuff. Let me know if it's still not working.

[@LL-hj8yh]

@@msk2982 yes, I sure did. I completely rebuilt the package twice following your video; and also followed other people's online tutorials for another 2,3 times complete build. Still same issue. Was able to import the excel without loop, yet never got much luck looping today.

[@msk2982]

Leo Leo ok so the issue is probably in the loop. Now for the Excel file variable name, did you put in anything?

[@chucktodd717]

Very Good! Thanks!

[@andresss85]

I followed the steps. I could load the first file but when I tried to load it using "For each" I got this error [SSIS.Pipeline] Error: "Excel Source" failed validation and returned validation status "VS_NEEDSNEWMETADATA". If I load the files one by one it works just fine. But I have more than 60 files. Life is too short to do it one by one

[@msk2982]

Hey Andres. I'll try and replicate this error and see if I can find you a solution.

[@andresss85]

Mohamed Khan I found the error. One of the 60 files had one more column

[@msk2982]

Awesome. Glad you figured it out. I just ran some tests and found the same thing. That if the number of columns don't match it throws up that error. Glad you were able to figure it out.

[@tommyx.gomezgarcia6090]

You saved my life yo!! jaja keep the good work!

[@msk2982]

Thanks! I will try and do more as I get time.

[@eduardoherrera4302]

can it be done with txt files?

[@msk2982]

Yup! Just make sure to change the file types accordingly.

[@aisha-1237]

Hi, thanks for posting this. Would this work if your excel files are not normalized and the normalization is happening in SQL?

[@msk2982]

Aisha, I would consider an Excel file only normalized if the data is split up in Sheets to coincide with the database tables. Other than that, Excel data is considered de-normalized since the data is split up in SSIS before going into the database. In my video, I am taking non-normalized Excel data and using SSIS to clean and split the data to make it normalized in my database. So basically, as long as the columns are lining up, this should work.

[@shriramrao5856]

Hi MSK, do you happen to have a video to load exc el data across different tabs into different tables ? Like in Sheet1>>Tble1, Sheet2>>Tble2 and so on.

[@msk2982]

Shriram, sorry for the late reply I just saw this comment. You can do this pretty easily. All you need to do is create multiple excel data sources in your data flow task. Each source would be one sheet. That way when the loop runs it would do each sheet into each table.

[@sabkuchdhela191]

How to do the same with MYSQL... And it shud be a live connection also...

[@msk2982]

So you want to use ssis to load excels into MySQL?

[@sabkuchdhela191]

@@msk2982 ya..i am able to integrate excel to MySQL, but my excel doesn't push updates to MySQL.. i tried to save XLS into ODBC file also

[@sabkuchdhela191]

@@msk2982 or the same procedure can be done with GSHEETS to POWER BI... Without publishing Gsheets as public file... Basically wants to make it secure only...pls suggest n guide

[@DebayanKar7]

How about loading incrementally, with new files coming every day

[@willianalves404]

I have the same question

[@msk2982]

Personally I would setup a SQL Server job to run daily. I can probably make a new video for that.

[@DebayanKar7]

@@msk2982 that's nice to hear sir

[@shanebow10]

@@msk2982 have you by chance made a video that covers this topic? If so, can you provide the link? This is very helpful information, but I was wondering the same thing as relates to importing/appending new data from Excel to SQL on a daily basis as new files are added to the source (folder containing excel files). Thanks!

[@msk2982]

@@shanebow10 No I haven't. To be honest I've been so busy and I actually wanted to start a whole series of videos covering new topics including using BIML to make scalable SSIS packages.

[@Dataanalyticspro]

Awesome Video! Thank you so much.

[@Ask65k]

Thanks a lot..!! It really solved 😀 Do you have a by idea how to run this SSIS package on every 1 hour intervals.

[@mazkaibil9108]

You can achieve this by creating a SQL Server Agent job which can be then scheduled.

[@danielled5883]

Thank you for this!!!

[@msk2982]

Danielle D you're welcome! I hope it helped.

[@joshi7405]

Why didn't you use SQL destination instead of OLDB destination?

[@msk2982]

Hard for me to explain so I found this on the web explaining it better "SQL Server Destination may improve the data load performance over large data size but it has many restrictions. OLE DB Destination is more generic and widely used and by using fast load data access mode, it increases the data loads performance and it is almost the same than SQL Server Destination on small and medium data size. In addition, many SSIS experts recommend using OLE DB Destination based on their experience."

[@华胥-g5c]

Thank you sooo much, the video really helps:-)

[@ALLCPL]

Yes me too

[@manikantamokidi5631]

Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate. can you resolve this error sir pls

[@msk2982]

Hello Manikanta, did you set the debugging runtime of 64bit to false? Is the package working without the ForEach Loop? 6:23

[@AnujSharma-ge3mr]

msk2982 .....hi, just wanted to ask, how do we populate multiple db tables with these multiple .xls files insted of populating just one table. As according to my requirement i need to load certain fields in one table an rest in other tables from same file.

[@walidchamour2708]

thank u

[@SarishaDangalSaloniDangal]

WILL YOU MIND REFERENCING THE LINK TO DOWNLOAD MULTIPLE EXCEL FILES WHAT YOU USED?

[@ALLCPL]

Good