143 How to import multiple excel files into sql server using ssis

[@LocNguyen-cr7tm]

why I see no any data connection afer clik "new" in OLE DB connection manager step ?? help me please

[@iNtellectual.paradoX]

Bro when i select excel with xlsx extenstion in excel source I do not get name of excel sheet.it says no table or view could be loaded. same is working fine with .xls extention. Plz help.

[@learnssis]

Try to Install Access database engine drivers from here and try again drive.google.com/drive/folders/1iTtcu6EMuRMjCPnLnIkr-QfePEfJPwny First install 32-bit and try and if that does not works then install 64-bit. If you face any issue installing 64-bit then uninstall the 32-bit drivers and then install 64-bit.

[@iNtellectual.paradoX]

@@learnssis Thank you so much bro. :)

[@samderrty123]

the microsoft access database engine cannot open or write to the file. But I do not have it opened. any ideas?

[@ziadbensaada]

not working for me! it import one file only in sql server with flag x in the schema of excel source ? i dont know whaat is the problem?

[@learnssis]

Maybe you can take a look at this video and use this code inside foreach loop container and pass the FilePath to Script task to import multiple excel files kzbin.info/www/bejne/sIKWpKyLqKatj7s

[@ziadbensaada]

@@learnssis but i have many files excel i wanna add them to one table in sql server , when i change the runtime not working told me not supported

[@learnssis]

@@ziadbensaada What is the error message ? And How you are trying to do it for all excel files ?

[@afiqghazali6826]

Hi there, great tutorial. In minute 4:59 you selected ExcelFilePath and the expression is User::FilePath. If im flat file source(.csv), what option should I choose?

[@learnssis]

In flat file source, we will be using the flat file connection manager and in flat file connection manager we would need to use Connection String property. kzbin.info/www/bejne/h3W2g619rbGYZ8k

[@putrigilang1516]

Hi I've edited the expression and it turns out error "the acquire connection method call to the connection manager failed with error code 0xC0202009" can you help me with this?

[@learnssis]

Set the DelayValidation property of SSIS package to True. And you should be good if the excel file schema is same.

[@beyzanuraltikardes2743]

I want to do this for excel files in different folders in the same project for example with 2-3 foreach loop containers. is it possible?

[@learnssis]

Yeah you can use multiple foreach loop containers, one foreach loop container with one folder.

[@HeadshotAndCruise]

I am Getting below mentioned two error while importing multiple excels. When I run the package the data in excel mentioned in log got imported and the package terminates with the following error. Can you please suggest what wrong I am doing. [Excel Source [84]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. [Excel Source [84]] Error: Opening a rowset for "MASTER_FEB_2024$" failed. Check that the object exists in the database.

[@learnssis]

It seems like MASTER_FEB_2024$ sheet name does not exists in the excel file.

[@HeadshotAndCruise]

But the file is there because when i run the package the rows of this excel got imported, after that it gives this error. Also in the log it shows package return the status success @@learnssis

[@learnssis]

@@HeadshotAndCruiseSet the delay validation property of package to success. And make sure that you have followed other steps like made the excel connection manager dynamic by setting the excel file path property from ssis variable And getting the sheet name dynamically using the C# code.

[@bzflowerbee]

Is there an alternative way to do this without ssis? Our company has 2017 visual studio and there isn't a business intelligence on visual studio available 😢

[@learnssis]

You can use C# as well to import all excel files form a folder to sql server tables. kzbin.info/www/bejne/gavYcmp_oqp0gKc

[@bzflowerbee]

@@learnssis but I don't know c#. Thank you.

[@learnssis]

​@@bzflowerbeeThen you can manually import them.

[@bzflowerbee]

@@learnssis I know 😭😭

[@chvnetchvnet4481]

Thanks for this solution. But this wont work for me . I use visual studio 2022. When i change the filepath expression in the excel connection. my excel source fails immediately. When i go to excel source and check the issue for columns - i see error below which says " An OLE DB record is available . Source:Microsoft.Access.Database.Engine. HResult:)0x80004005. Description : Invalid Argument. Is this error due to having a different sheet Name ?. Please help I am blocked. Thanks in advance

[@learnssis]

Yes if your sheet name is different then before loading the data inside data flow task, you must get the sheet name inside a variable and then use that variable in excel source to dynamically fetch data from that sheet. kzbin.info/www/bejne/nXKthqdsasSHmdU

[@chvnetchvnet4481]

@@learnssis Thankyou for the response . This worked . Also thanks for sending the information in detail via email too. Appreciate the help

[@zoyasaleem5689]

please let me know how to SSIS package call from job and run automatically at configure time please

[@zoyasaleem5689]

i am stuck please

[@learnssis]

Hi Zoya Saleem, Take a look at this video, I have shown this in detail kzbin.info/www/bejne/p6O5Y3mYhtWIaas

[@vochau9806]

Hello, I build a SSAS to load data from SQL server. Firstly, I load fact table 500 lines. I deploy SSAS also load 500 lines. However, when I update the fact table by SSIS increate to 600 lines. SSAS does not load enough to 600 line. I have to process SSAS again, it loaded to 600 lines. But when I deploy SSAS again, it still loads 500 lines. Therefore, the Excel report does not refresh. How do I fix this. Thank you for your help

[@learnssis]

Hi Vo Chau, frankly speaking I don't have any experience working with SSAS thus not sure what might be wrong here, sorry for that.

[@vochau9806]

Do you have a video that load Dim and Fact table from an Excel file. For example, I have a E-com data, I want to load to Dim and Fact table. Thank you so much

[@learnssis]

No I don't have a video on that one. but loading a Dim and Fact table from excel file is as simple as loading it from CSV file. The only change for excel will be that instead of flat file source, now you would need to use excel source. And if in the destination table the data type is varchar, then you would need to add a data conversion task before the lookup transformation and it can convert the data type to varchar those you can use inside lookup transformation. I have made videos on loading data to Dim and Fact tables and on Data conversion task as well. kzbin.info/www/bejne/mWPPhH6ChrWVlas kzbin.info/www/bejne/mqbEg5KGnbGXo8U kzbin.info/www/bejne/l4KtdJ6vZ9aZedU

[@kichuchinnu3303]

Hiii , Can you suggest your video for ODBC destination in ssis package

[@learnssis]

This is one of the video for ODBC destination kzbin.info/www/bejne/gIHRkKdsZdl6edk

[@funbutterjam4734]

We have 3 tables it have millions record. so last 3 year before data move source to stg table and then stg to archival and delete record from source and stg table after moved archival (source and stg table same server but archival is different server). How to create siss package. please make one video.

[@vochau9806]

Hello When I manual write this command in SQL Insert into TableA (a1,a2,a3, FileName) Select a1, a2, a3, ‘abc.txt’ from Table . This SQL works. No problem But when I create a variable in SISI "Insert into TableA (a1,a2,a3,FileName) Select a1,a2,a3," + @[User::FileName] + " from TableB" .it does work The error is [Execute SQL Task] Error: Executing the query "Insert into [Table A].." failed with the following error: "The multi-part identifier "FileName230123.TXT" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Could you please help me. I try to search but cannot resolve. Thank you very much

[@learnssis]

Try this one "Insert into TableA (a1,a2,a3,FileName) Select a1,a2,a3,'" + @[User::FileName] + "' from TableB"

[@vochau9806]

@@learnssis I see what I am wrong. just a '. Thank you so much

[@lifeisawesome4566]

Thanks for this great tutorial. I have a question: If files are in SharePoint, is this approach will work?

[@learnssis]

No. If the files are at sharepoint then first you would need to download the files from sharepoint to a folder on the server and then you can use this method.

[@DeUraq]

Excellent one and it worked with me! I have a quick question. If my excel files are their respective folders with the same file names, the how to use the dynamic variables? like c:\data\emp1\emp1.xlsx, c:\data\emp2\emp2.xlsx etc. Thank you

[@learnssis]

Thanks, in that case either you can give path of parent folder like c:\data and then tick the option traverse sub folder and it will try to find files from sub folders (emp1,emp2) as well.

[@DeUraq]

@@learnssis thanks for the reply. if I have multiple excel files (up to 50 files) as (emp1.xlsx, emp2.xlsx ...emp50.xlsx) and load them to sql table emp1 to emp50 . Do i have have to create a different workflow for 50 times for each table to load data?!

[@learnssis]

@@DeUraq If the layout of all files is same then you can use a single data flow task with foreach loop container to load them, however if their layout is different then you can create separate data flow for each file.

[@decentmendreams]

Hi , do you provide consulting services ?

[@learnssis]

Sorry right now I am very busy and don't provide consulting service.

[@priyavishalkulkarni2415]

The same steps are followed. The only difference is that I have a CSV file. But getting data from only the first file.Not the second one. And if I execute the package again then the same rows are added repeatedly.

[@learnssis]

Hi Priya, the video you are referring is for loading multiple excel file and to load multiple csv files you can watch this video kzbin.info/www/bejne/eJvTkoaugr-ArtU I think you have not configured the flat file connection manager correctly. You would need to make it dynamic, by passing the file path to connection string property using expressions. Thanks.

[@priyavishalkulkarni2415]

@@learnssis Thank you for your quick response. Now the package is working fine. But the only issue is records from the second file from SSIS come with double quotes.

[@learnssis]

@@priyavishalkulkarni2415 In the flat file connection manager, put the double quote " in the text qualifier option. kzbin.info/www/bejne/gXKQpmmli9CKrZI

[@priyavishalkulkarni2415]

But I don't have quotes in my flat file.@@learnssis

[@TheMan-wd1pv]

i heard sql and msbi vanished many years ago is it true

[@learnssis]

I am working on these technologies for last 13 years and still I see a lot of jobs in these areas. SQL Server is the widely used database management system in the world.

[@piraviperumal2544]

@@learnssis Very true

[@shaneparkes7105]

Thank you, I would move uploaded files to another folder instead, this should help if ever a file needs to be replaced, not sure how it will handle when there is no file present though. I will also add an identifier to the filename to avoid duplicate filenames

[@learnssis]

Moving the processed files to another folder is also a good approach, it will work fine if there are no files to process. You can watch a video on this one here kzbin.info/www/bejne/iIe1cqh8jNeAmac

[@shival7096]

Excellent one. Please do a video on importing multiple excel files into sql server as separate tables with scheduled incremental refresh.

[@learnssis]

Sorry did not get this one completely. You mean each excel file will be loaded to a separate table ? Then how it will be incremental ?

[@shival7096]

@@learnssis yes, i mean when data changes in excel it need to update in sql server on a schedule.

[@learnssis]

@@shival7096 Okay got it. but you want to update data to multiple tables. If we can write the code for one table then we can use the same logic for another table as well. If we will create video doing it for multiple tables then the video can be very lengthy.

[@shival7096]

@@learnssis Got it , thank you so much.

[@pallavikrishna7591]

Thank you so much!!! Very Crisp and clear , as always

[@learnssis]

Thank you.

[@farzadsaedi1954]

Very good tutorial. Can you please show how multiple excel file can be transferred from SharePoint folder to Sql Server? thanks

[@lifeisawesome4566]

Hey, did you get your answer?

[@lonestarparrot]

Great tutorial and very easy to follow!!! Thanks @Learn SSIS

[@learnssis]

You are most welcome Sir.

[@prashantsinha123us]

Thanks, It was good and all easily understandable

[@learnssis]

Its good to know Prashant that you found the video useful.

[@ArwaAldoudOmer]

Thanks, it was amazing. I followed the steps and it worked perfectly. Thank you 🥰

[@learnssis]

Glad it worked for you.

[@dilipchauhan5598]

Simple and on point!

[@learnssis]

Thank you Dilip.

[@TheLighty37]

Thank you it worked for me

[@learnssis]

Glad it worked for you.

[@OleYoule]

Hi Aqil, Thank you for your tutorials 👍. I got huge amount of useful information) Could you please make video how to record sheet names in extra column inside main database (main sql table) for each data record(line). For instance if excel file has multiple sheets Sheet1, Sheet2, and so on they have to be added to each record inside main database(main sql table). Thank you in advance!

[@learnssis]

Sure will try to make a video on this one.

[@learnssis]

Hi, if you want to import multiple excel sheet then you can take a look at this video kzbin.info/www/bejne/Z4qofamlaat2gac And if you want to add a new column Sheet Name to the data flow and want to import it to a sql server table, then you can use a derived column transformation and in the derived column transformation just drag and drop the sheet name ssis variable into Expression and create a new column SheetName, this will create a new column SheetName with the value from SSIS variable Sheet name, then you can map this column with your sql server destination table. I have done the similar kind of thing in below video by creating a new column FileId kzbin.info/www/bejne/eJvTkoaugr-ArtU

[@OleYoule]

@@learnssis Hi Aqil, thank you for your tip. Changes based on your recommendations were implemented and SSIS task works perfectly! You are the best SSIS tutor👍

[@learnssis]

@@OleYoule Glad it worked for you.

[@sureshareti2843]

Good one bhaiyya... Thanks alot.... I request you do one video on Source Excel Read all excel header with data dynamically based on that create table dynamically in sql server and map columns dynamically excel source and sql server destination and load the data

[@learnssis]

Thank you. In data flow task, SSIS does not allow to change the columns dynamically using the excel source. We can only use Script task and write all code there may be in C# or VB.NET

[@sureshareti2843]

@@learnssis thanks for clarification bhaiyya

[@newtechnofayda]

Good

[@learnssis]

Thank you.

[@akramabushmais2949]

I need help I did the same steps and I have two excel sheet in the folder but when I applied the steps and check the database I found one of the excel twice in the database table how can I solve this !!? Thanks @leanssis

[@learnssis]

Did you made the excel connection manager dynamic by going to expressions of excel connection manager and setting the ExcelFilePath property to come from the variable as shown in the video ?

[@akramabushmais2949]

@@learnssisyes I found the problem (I read the wrong extensions ) I’m sorry to interrupt you thank you again for the great KZbin channel it is very helpful ✌️

[@akramabushmais2949]

@@learnssisone more question If I already have table in database I just need to don’t choose fast load in database and choose the existing table in database and do the correct mapping in mapping option right !

[@learnssis]

@@akramabushmais2949No problem, you are most welcome.

[@learnssis]

@@akramabushmais2949Just choose the fast load option and select the table from the database.

[@learner_SSIS]

Good sir 😀

[@learnssis]

Thanks 😃