SSIS Tutorial Part 18-How to Load Multiple Excel Sheets to a SQL Server Table in SSIS Package

[@The-Right-is-Right]

At 5:02 the instructor says "“We have to provide 2 as the number.. the table name was at the number third in the list...so index starting 0,1,2,3….and the third one as we wanted so we have to provide index is equal to 2..so hit OK”. I did not understand any of that whole sentence. Can someone explain why we need to choose 2 ?

[@The-Right-is-Right]

@TechBrothersIT Hi TechBrothers. Is there any update on my question above - can you explain why we need to choose '2' just after 5:02 timestamp in the video?

[@llaved77]

Excellent video. After unsuccessfully following some other tutorials, this one worked perfectly and helped me understand how it works. Now to try and get my users to give me consistent formats of spreadsheets! Many thanks.

[@TechBrothersIT]

Thank you and very happy to know that you were able to make it work. all the best.

[@The-Right-is-Right]

@Phil Devall As you got this package working Phil, I wonder if you might be able to explain why the instructor puts the number '2' in for index at 5:02 in the video? (because I did not understand the Instructor's explanation)

[@rajasekharreddykalluru4419]

hi i can't find out the properties([Ado.net Source]) of data flow can u help me to find

[@PaniPani-z6w]

Hi this is great, but in my case, i want to pass the file path dynamically. i need to deploy it in to different servers and each sever has file in different locations, how do i pass that file path through Variable or Parameter? i appreciate your help.

[@nirmalkumar3282]

Best Instructor ever.... Thanks a lot.....

[@TechBrothersIT]

Nirmal Kumar Thanks Nirmal Kumar:)

[@lyndia9606]

Sir if the sheet is with same format but row start from diffrent cell, how to adj the row start ?

[@ataisieneyo3689]

Thank you for this video. I have a very similar problem but for my OLE DB connection manager, I do not have the option for "Microsoft Office 12.0 Access Database Engine OLE DB Provider". Can you please guide me on that?

[@TechBrothersIT]

You can download this driver. Search on internet and download and install.

[@Astrocoreparasite]

Hey, I downloaded the Microsoft office access database engine but still not getting the access database engine olé dB provider, please help!!

[@nareshgose8796]

I tried to select "Microsoft Office 12.0 Datababase Engine OLE DB Provider" from the .Net Providers for OleDb drop down list. However, I don't see this option in my SSIS. I've checked around on Google and downloaded and the 64 bit version of Microsoft Access Database Engine 2016 Redistributable (www.microsoft.com/en-us/download/details.aspx…) Since I have a 64 bit laptop and I have 64 bit, 2019 version of MS Office/Excel). I'm using SSDT 2017 to run SSIS. However I'm still not getting the the MS Office Access DB Engine OLE DB Provider option. Any advice?

[@Nataly180108]

it's a great video explaining step by step, thank you!

[@sultonboydavletov-cm4ld]

Hello Mister. I have a bit problem here. I don’t have Microsoft Office 12.0 Access Database Engine Ole DB Provider on my Laptop. How can I fix it.🙏

[@krismaly]

I found a solution and would like to share as below: 1. Right click on SSIS Project and choose Properties 2. Choose Configuration Properties 3. Choose Debugging 4.Set Run64BitRunTime to FALSE. and run the package and it works, My suggestion is people who are having 64 Bit machine (Mine Windows 7 64 Bit O/S) should ensure to set as above just before working on the Package.

[@TechBrothersIT]

krismaly Thanks for sharing the information. I am sure, other people will get benefit from this.

[@nirmalkumar3282]

krismaly This is already instructed by TechBrothersIT

[@parimal2267]

Hi Amir, Do you have any videos regarding the Data conversion.As I am trying to convert the one data column from string to currency as this column exist as money in DB .I am getting error "conversion can be done as it can loss potential data " please help me

[@feng3625]

I have failed regard error message as: TITLE: Connection Manager Test connection failed because of an error in initializing provider. Could not find installable ISAM. is this about my version?

[@itsoverat6000]

Video is great. I have a problem when reading the tabs. I'm getting "xlnm#_FilterDatabase" as a table. The tab doesnot exists. Any thoughts?

[@gjulidh]

Hello, just one comment. In Visual Studio 2010, the task scripts creates a kind of loop and just execute the load of the first ms-excel spreadsheet. If I disable the task script object, the process run sucessfully and load all the spreadsheets in my xls file. Regards and thanks for share your videos.

[@rama364]

Without script task is it possible

[@rohityeole3897]

Doubt: I'm using 2016 Excel ,In connection manager ,the .Net provider is not displaying Microsoft office 16

[@kurochankg]

can you help me, how fix this error. Error 1 The type or namespace name 'Runtime' does not exist in the namespace 'Microsoft.SqlServer.Dts' (are you missing an assembly reference?)

[@rashmi933]

Our talents are the gift that God gives to us... What we make of our talents is our gift back to God and i must say Aamir you are fortunate one to have return gift for God , just keep rocking !

[@TechBrothersIT]

+Rashmi Sinha Thanks for kind words Rashmi, You are right and just trying out best to share we know and hoping it will help others. Thanks for watching and leaving comments!

[@harikhadka2228]

Excellent videos. The list under connection manager for FOR EACH LOOP doesn't have Microsoft Office 12.0 Access Database Engine OLE DB provider. In this case what can I do to load multiple excel sheets to SQL table using SSIS.

[@nareshgose8796]

Hi Hari , Did you get answer for this Because same thing i am getting. Please can you help me in this if you got this answer.

[@benfriger]

Hi, thanks show much for your videos. I have an issue, when load data to table in sql I have duplicates per page Example:. Sheet1 and Sheet1$, who knows why?

[@ItsmethiruBlogspotIn]

If I want to load multiple sheets into multiple tables can we do using same process, i tried but am not able to change the destination tables, can you please help here?...

[@databasemsbi7727]

why we choose index =2 when we design new variable = sheetname ?

[@quannguyenanh6753]

When I have clicked into Foreach ADO NET Schema, the Enumerator Config is empty. Help me!!

[@krismaly]

I enjoyed watching this video and it is useful in real time. Thanks for educating the community and appreciate your volunteer-ship. I think you need to clearly explain Delay Validation with an example because it's an important property. Over all video is good and what you are trying to say or teach audience could understand. Please give links or related as you said will be helpful. Thanks a bunch

[@TechBrothersIT]

Hi Kris, Thanks for reminding me about that. Sorry totally forgot. I have update the description with links. here are the links as well if somebody is interested to learn more about Delay Validation sqlage.blogspot.com/2014/04/ssis-how-to-create-use-temp-table-in.html sqlage.blogspot.com/2014/09/ssis-what-is-delay-validation-property.html

[@lifeisabrew]

Perfect! I just followed the steps and it worked properly...

[@KarthikDeena]

Hi Techbrother, how to extract data from Sql database to multiple sheets in a excel workbook ?

[@balajinaidu37]

Hi TechBrothers, Could you also please post the video for loading data (flat files) from different folders into different SQL server tables with in a single SSIS package. As metadata, source file columns are same in different folder but with the different data. Which will be very helpful. Will be awaiting for your post or video on these. Regards, Balaji C K

[@tajyaseen93]

hello, My ado.net source is displaying "NO table or view could be loaded" when clicked on dropdown...please help...

[@ksh-v8x]

what if i want to load from multiple excel workbooks having many sheets ?

[@DuncanEduardo]

Hi TechBrothersIT can you say which version of data tools you are using with SQL Server 2014?Is it Visual Studio 2012 Shell (Integrated)?

[@TechBrothersIT]

+DuncanEduardo Hi, yes.

[@gulzarsiddique8908]

Microsoft 12.0 access database engine is not visible in my case, please help ...i am using visual studio 2017. I have also installed Access database engine 2010 in my system still not visible , is there any alternative or solution ?? thanks

[@nareshgose8796]

Hi Gulzar , I hope you have got answer for above you revised question . Because Microsoft 12.0 access database engine is visible in my case. I am also using visual studio 2017 . Please can help me on this. Here is the my Email: [e-mail]

[@chandrasekharp3217]

Hi, I have tried this but i am getting the following error please help me where i did wrong. Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80131509 "(null)". This occurs when the ForEach Enumerator cannot enumerate. I have seen so many times and do all things correctly but i don't know where it was occurred. Please assist me.

[@karthicksavasoft6348]

How about the same when having to load from sheets that belong to different versions of Excel?

[@dragontrans]

hi. i have a problem. I can not do anythig because of error 0x80004005.How to fix this? Error: 0xC0014023 at Foreach Loop Container: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate. Warning: 0x80019002 at test: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

[@krismaly]

I enjoyed watching this video and learned functionality and also encountered problems and resolved. Recommend other to watch. Thanks a Bunch

[@BhushanMehetre]

Hi, Thanks for the great video. I tired this and have some difficulty like, same sheet is imported no of time(count of sheet in excel). I follow the same as in the video. So any idea Thanks in advance...

[@noramartins7706]

Yes, had the same issue after doing all steps. package runs well but loads the same sheet with different sheet names, you will best understand what we are saying if your sheets have different no of rows and you are using a data viewer. watch this video to get desired results, way shorter and easier: kzbin.info/www/bejne/Z4qofamlaat2gac I appreciate TB-IT and they have helped me a bunch. but am just helping out some one here. Thanks TBs

[@JaswantSingh-di8nu]

This is very helpful and easy to follow. Thank you so much!

[@kuldeephirade]

[SSIS.Pipeline] Error: "ADO NET Source" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

[@chiraluvu1]

Very informative and helpful. Keep up the good work !! Doubt : The column phonenumber in source Excel sheet has got values which are alphanumeric. While loading it to destination the data loads but with NULL values. Do we use IMEX=1 even here ?

[@TechBrothersIT]

+CHIRANJEEB DAS Hi, in that case yes. Please use that setting and see if works.

[@chiraluvu1]

+TechBrothersIT Thank you for your quick response. It was successful after adding IMEX=1 in ADO.NET connection string extended properties. [Extended Properties] EXCEL 12.0;HDR=YES;IMEX=1; You are doing a commendable job. Thanks again and hope you keep doing your good work.

[@krismaly]

Question After creating Foreachloop and ScriptTask When I run I get below message Error: 0xC0014023 at Foreach Loop Container: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80131509 "(null)". This occurs when the ForEach Enumerator cannot enumerate. Warning: 0x80019002 at LoadData2SQLTblFromMultipleExcelSheets: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "C:\Users\Saroja\Documents\Visual Studio 2012\Projects\SSIS\MySSIS_Projects\MySSIS_Projects\LoadData2SQLTblFromMultipleExcelSheets.dtsx" finished: Failure. Any ideas for solution

[@TechBrothersIT]

Hi Kris, Never got this error. Do you think your connection is pointing to correct excel file? here is one link that I found. Please take a look and see if this helps social.msdn.microsoft.com/Forums/sqlserver/en-US/4db906d8-8771-4209-aed0-4034775a67f7/foreach-loop-fails-with-null

[@Venulogs]

Tutorial is really good!! Keep going

[@quannguyenanh6753]

my enumerator configuration is empty. I'm using VS2015

[@mrugeshpatel2775]

After reviewing other sources to solve my issue; you provided good solution and thank you very much to share this and its really helpful to learn. Your method is so simple and easy; I really appreciate for your effort and sharing this kind of technical video with others. I had resolve my issue by watching this video finally. I have simple question in it: On this method we have 1 excel file with more than 1 sheet ; how to work with more than 1 excel file which has more than sheet. Can you help me in that ? I have to transfer many data from excel where excel file has more than 1 sheet. Thank you

[@DuncanEduardo]

TechBrothersIT can you help? I have tried this solution but the variable is initially assigned to the "hidden" object "_xlnm#_FilterDatabase" and it can't seem to escape this; it iterates around this same variable 9 times and then just hangs. It doesn't move on to the remaining tabs in the spreadsheet.

[@TechBrothersIT]

+DuncanEduardo Hi, I am not sure what could be the issue. it is really hard to guide when you don't have actual files etc. You might want to try this with Script task. I have created tons of videos on how to use Script task for Excel source. Hope this will help. www.techbrothersit.com/2016/03/how-to-load-multiple-excel-files-with.html Check the post under Excel Source and Destinations (Script Task- Dynamic) heading on below link www.techbrothersit.com/2014/12/ssis-videos.html

[@Sandani_Aduri_Group]

Hi Amit,Please create the videos for SSAS

[@thee_draagon]

Thank you Amir, your videos are excellent and helped a lot to grow my knowledge in SSIS. You are truly a legend for me 💖

[@The-Right-is-Right]

Hi Imthiyaz Ali. From your comment, It seems like you understood this video really well. At 5:02 the instructor says "We have to provide 2 as the number.. the table name was at the number third in the list...so index starting 0,1,2,3….and the third one as we wanted so we have to provide index is equal to 2..so hit OK”. I did not understand any of that whole sentence. Do you know why we need to use the number '2' ?

[@ibbies.3324]

Thank you for this video I wanna know how can I run the package from the SQL not from the SSIS Thank you for your help!!!!!

[@ankitauppal6961]

Very Helpful video .... thanks for uploading .... appreciate the effort!!

[@shubhamasati728]

simple explanation and very useful

[@RishabGoonoa]

Thank you - this was a big help!

[@MikeBaron2009]

Great, I love those videos

[@TechBrothersIT]

Thank you for kind words.

[@lakshmihomefoods960]

Thank you very much for the detailed explanationand keep more video's

[@TechBrothersIT]

+Sree Kadiri you are most welcome. Thanks for watching. Will make More.

[@SabharwalMr]

Good video on SSIS excel linkage

[@TechBrothersIT]

+Sanjay Sabharwal Thanks!

[@anan7839]

it is a good video, I have learnt something, thank you very much indeed.

[@TechBrothersIT]

nightowl We are glad to hear that it was helpful:) You are most welcome!

[@wisdomtube9681]

Thanks . It helped me a lot.

[@romankuzmenko2716]

Nice Job, brothers =)

[@TechBrothersIT]

+Roman Kuzmenko Thanks! Glad you liked our effort!

[@sarovanithaarmy4563]

I need ur help

[@reggieo4889]

Thank you so much for this tutorial. I was using excel source instead of ADO NET Source and it wouldn't work. And thank you for the script part of the training.

[@shoppingaggregator3079]

Sir, You are amazing teacher.. I am learning a lots from you and implementing all of your exercises. i am very much thankful to you.

[@manishsharma7329]

Very nice video

[@wasimt3832]

nice information, thanks for this, same lik this i want to transfer the data from multiple excel file with each file having multiple sheets to a single database table

[@TechBrothersIT]

+wasim T hi, please check dynamic excel on this link. I have videos what you have asked www.techbrothersit.com/search/label/SSIS%20Video%20Tutorial?m=1

[@wasimt3832]

sorry but my requirement is lill bit change,, can i get ur email id so that i can send u the whole scenario, thanks in advance

[@udayelluru2813]

Very nice video, but is it possible to upload multiple excel files with mutiple excel sheets without using script task...Thanks in advance...

[@mikhailvakhlis835]

Not clear why Variable mapped to index 2

[@The-Right-is-Right]

@Mikhail Vakhlis Hi Mikhail! I have the same question also. Did you ever find an answer as to why he chose "2" for the Index at 5:02

[@idaleung5307]

I also got the some question why we need to send index = 2

[@stevengarcia7277]

I wonder how you know all of this..

[@atulsalunke2402]

Thanks Really helpfull.

[@sarlachoudhary4401]

EXCELLENT

[@MatthewTorontoParis]

in fact you just need to download : 1. Download and install this: www.microsoft.com/download/en/confirmation.aspx?id=23734 restart you Visual Studio. Use Excel Source. then you excel .xlsx will be available for any sheets to load into SQL DB all the other articles told me to use For Each Loop. but what I want is just to Sheets("X") to Table "Y"

[@girishkariappa7386]

Nice 1

[@shivakanth9525]

it is very gud thanq

[@selvamkumar1982]

Thank u bro...

[@choobplaya]

This is amazing.

[@TechBrothersIT]

Thank you!

[@kuldeephirade]

Great!

[@chaudhryalisalam3668]

amir sir great video everything work well , please check your silage mail box i sent an email , please reply if you have time .