How to Load Multiple Excel Files Data to SQL Data Table using SSIS Components [Live Example]

[@raghavendrapani6353]

Thank you bro ! This was helpful, I was missing delay validation and you video helped me lol , should have watched long back

[@alirezamogharabi8733]

Really helpful thank you ❤

[@gurunathad13]

welcome happy learning

[@tadivamsi4759]

Nice Explination sir

[@prashanthtalla]

Thank you so much. How to change this logic to read the files in parallel and load into the target in parallel instead of sequentially? The actual requirement is a SQL task prior to the Foreach Loop is feeding a list of databases against which same SQL has to be run in parallel (as the SQL runs for a long time) on all the databases that the Foreach Loop receives and the data extracted from each database should be loaded to the same target table in the target database.

[@prateeksharma5662]

do i have to give a db name while writing the truncate table query...as we are already ging it the database location *New to ETL*

[@gurunathad13]

no not required ...

[@bhavikdaruwala5135]

Thank you Guru Sir.. Learned something new today.

[@gurunathad13]

Thanks

[@santhoshburra8396]

Can you please suggest me how to load 10csv. Files into different tables through ssis? ( At the same time we need to create tables and different columns and different data) is it possible with single ssis package

[@gurunathad13]

Use foreach loop component and in DF use Flat file source as expression

[@selambedada5490]

How do you know your Excel version is 2016 on connection manager????

[@sumanyarlagadda6271]

Hi Sir, your efforts are commendable. Helping a lot to improve my skills. Could you please do more videos on Deployment and Configurations. Thank you

[@gurunathad13]

Sure 👍

[@anukolushivareddy]

very useful information, tq you so much mam. can you upload how to load text file to table in ssis

[@abebetaye6003]

This is really helpful and it provides a lot of useful information. Would you please make a video on how to load multiple JSON files in SSIS using script component? Thank you!

[@gurunathad13]

Sure will do in few couple of days

[@abebetaye6003]

Thank you Gurunatha.

[@milagroscuyafrancia4995]

Hello sir, i'm getting this error Failure inserting into the read-only column "CustomerID".

[@gurunathad13]

Yes in an identity column you cannot insert any record .....make it simple int column to insert record

[@saddalaharinadh6368]

Hi sir, how load one Excel file have multiple sheets ,i need to load one sheet in table and second sheet into another table as & third sheet into another table, please make i video on this

[@gurunathad13]

Yes you can achive multiple sheets with foreach loop where you have use variable for sheet name ... As an incremental option...sheet 1 and sheet 2

[@saddalaharinadh6368]

@@gurunathad13 Please share the related videos sir

[@gurunathad13]

@@saddalaharinadh6368 kzbin.info/www/bejne/rGfXf3ubg5d-l8U

[@manojsiddharths7792]

hi sir , i m getting error in excel connection manager configuration , could you please tell me how to solve it .

[@gurunathad13]

Kindly change connection type to 32 bit

[@KCchamp007]

Thanks for the detailed video, but just one thing is For me it's working for a single file but when I tried to do it for multiple, am facing this issue:"[Excel Source [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed".Any idea about this? Appreciate if you take sometime and reply

[@gurunathad13]

Hi thanks for your feedback... Now coming back to your question just need to convert project setting to 32 bit...go to project properties convert configuration to 32 bit ..it will work fine...

[@KCchamp007]

@@gurunathad13 Thanks for the quick reply, have fixed it and working...is there any way to fetch the filename and insert it into the SQL table from the excel files.TIA

[@madhurao404]

Liked your video. But in my case the Excel filenames are in an SQL table with some other data in that table. How to loop through this table and connect to the filenames and get data from these files to sql server.

[@gurunathad13]

Hi sir, Concat full file path with SQL column row having excel file name For example : string filepath="c:\\excelfiles\ Query : select excelfilename from tablename Expression = filepath+datarow["excelfilename"]; Use foreach loop having ado enumerator. Then pass full file path to data flow task having excel source and set excel source path . Hope this will help to get an idea of execution. Thanks Happy Learning!!!

[@nehal4094]

I have this error could you help me solve it SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB r....

[@sathiyamoorthi9224]

Nice explanation sir. Can please upload the same but multiple excel files from different directory

[@rajd621]

Thank you Gurunath. Nice explanation. I am new to SSIS. I have 4 different sources and need to combine all into one target table in SQL server db. Can you please let me know how to proceed. I tried pulling all into one data flow task, then tried to merge join, but unable to do it. Here is the required columns from the 4 sources that needs to be in one target table. CUSTOMER_Info (SQL DB): Customer_ID, First_Name, Last_Name, Email, Phone_No Required All above Columns CUSTOMER Data (.txt file): First_Name, Last_Name, Age, DOB, Gender, Marital Status Required columns: Age, DOB, Gender, Marital Status CUSTOMER Address (.xls file): First_Name, Last_Name, Address1, Address2, City, State,zipcode Required columns: Address1, Address2, City, State, Zipcode CUSTOMER Region (.csv file): First_Name, Last_Name, State_Cd, Region Required columns: Region, State_Cd

[@gurunathad13]

For this what columns are needed that you can check and uncheck it at source component level...So for this combining all 4 sources data you can easily use union all component also you need to use sorting as per name .....use union all it will work fine.... 👍