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
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.
@@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?!
@@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.
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.
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?
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. ua-cam.com/video/QDSRzGxLe1g/v-deo.html
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
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 ua-cam.com/video/RVRAuFWrMcE/v-deo.html
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
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. ua-cam.com/video/c2lSKLQPb_I/v-deo.html ua-cam.com/video/duaR_PhLdmc/v-deo.html ua-cam.com/video/aQJCky2qfCs/v-deo.html
@@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.
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.
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
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
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.
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.
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 😢
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?
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
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 ua-cam.com/video/zQ3syUsAzYY/v-deo.html
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.
Hi Priya, the video you are referring is for loading multiple excel file and to load multiple csv files you can watch this video ua-cam.com/video/BjpaSxMZMxs/v-deo.html 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.
@@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.
@@priyavishalkulkarni2415 In the flat file connection manager, put the double quote " in the text qualifier option. ua-cam.com/video/KA-u6oVkWw0/v-deo.html
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.
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
@@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.
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
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!
Hi, if you want to import multiple excel sheet then you can take a look at this video ua-cam.com/video/1YELvo4FCKE/v-deo.html 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 ua-cam.com/video/BjpaSxMZMxs/v-deo.html
@@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👍
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.
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
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 ?
@@learnssisyes I found the problem (I read the wrong extensions ) I’m sorry to interrupt you thank you again for the great UA-cam channel it is very helpful ✌️
@@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 !
![How to Load Multiple Excel Files Data to SQL Data Table using SSIS Components [Live Example]](/img/n.gif)
Thank you so much!!! Very Crisp and clear , as always
Thank you.
Thanks, it was amazing. I followed the steps and it worked perfectly. Thank you 🥰
Glad it worked for you.
Simple and on point!
Thank you Dilip.
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
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.
@@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?!
@@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.
why I see no any data connection afer clik "new" in OLE DB connection manager step ?? help me please
Thanks for this great tutorial.
I have a question: If files are in SharePoint, is this approach will work?
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.
Great tutorial and very easy to follow!!! Thanks @Learn SSIS
You are most welcome Sir.
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?
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.
ua-cam.com/video/QDSRzGxLe1g/v-deo.html
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
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
ua-cam.com/video/RVRAuFWrMcE/v-deo.html
the microsoft access database engine cannot open or write to the file. But I do not have it opened. any ideas?
Thanks, It was good and all easily understandable
Its good to know Prashant that you found the video useful.
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
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.
ua-cam.com/video/c2lSKLQPb_I/v-deo.html
ua-cam.com/video/duaR_PhLdmc/v-deo.html
ua-cam.com/video/aQJCky2qfCs/v-deo.html
Excellent one. Please do a video on importing multiple excel files into sql server as separate tables with scheduled incremental refresh.
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 ?
@@learnssis yes, i mean when data changes in excel it need to update in sql server on a schedule.
@@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.
@@learnssis Got it , thank you so much.
Very good tutorial. Can you please show how multiple excel file can be transferred from SharePoint folder to Sql Server? thanks
Hey, did you get your answer?
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.
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?
Yeah you can use multiple foreach loop containers, one foreach loop container with one folder.
Hiii ,
Can you suggest your video for ODBC destination in ssis package
This is one of the video for ODBC destination
ua-cam.com/video/JPn_t60tGCw/v-deo.html
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
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
@@learnssis thanks for clarification bhaiyya
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.
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.
@@learnssis Thank you so much bro. :)
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 😢
You can use C# as well to import all excel files form a folder to sql server tables.
ua-cam.com/video/KzuA7ImEAJE/v-deo.html
@@learnssis but I don't know c#. Thank you.
@@bzflowerbeeThen you can manually import them.
@@learnssis I know 😭😭
Good
Thank you.
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?
Set the DelayValidation property of SSIS package to True. And you should be good if the excel file schema is same.
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
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.
please let me know how to SSIS package call from job and run automatically at configure time please
i am stuck please
Hi Zoya Saleem, Take a look at this video, I have shown this in detail
ua-cam.com/video/qrV2FbQpU3I/v-deo.html
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?
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
ua-cam.com/video/zQ3syUsAzYY/v-deo.html
@@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
@@ziadbensaada What is the error message ? And How you are trying to do it for all excel files ?
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.
Hi Priya,
the video you are referring is for loading multiple excel file and to load multiple csv files you can watch this video
ua-cam.com/video/BjpaSxMZMxs/v-deo.html
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.
@@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.
@@priyavishalkulkarni2415
In the flat file connection manager, put the double quote " in the text qualifier option.
ua-cam.com/video/KA-u6oVkWw0/v-deo.html
But I don't have quotes in my flat file.@@learnssis
Hi , do you provide consulting services ?
Sorry right now I am very busy and don't provide consulting service.
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.
It seems like MASTER_FEB_2024$ sheet name does not exists in the excel file.
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
@@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.
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
Try this one
"Insert into TableA (a1,a2,a3,FileName) Select a1,a2,a3,'" + @[User::FileName] + "' from TableB"
@@learnssis I see what I am wrong. just a '. Thank you so much
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!
Sure will try to make a video on this one.
Hi,
if you want to import multiple excel sheet then you can take a look at this video
ua-cam.com/video/1YELvo4FCKE/v-deo.html
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
ua-cam.com/video/BjpaSxMZMxs/v-deo.html
@@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👍
@@OleYoule Glad it worked for you.
i heard sql and msbi vanished many years ago is it true
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.
@@learnssis Very true
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
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 ?
@@learnssisyes I found the problem (I read the wrong extensions ) I’m sorry to interrupt you thank you again for the great UA-cam channel it is very helpful ✌️
@@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 !
@@akramabushmais2949No problem, you are most welcome.
@@akramabushmais2949Just choose the fast load option and select the table from the database.
Good sir 😀
Thanks 😃