91 How to import multiple excel file with different sheet name using SSIS

[@yonisupersaiyanyoni3693]

Also inteop don't work when I diploy the package, and it's not permission problem, the sql agent have permission to the folder and excel file. Is there a way without code?

[@harimsbi1728]

Is it allow to read excel files from ssis package like space values present in Excel Sheet Name like (Annual Report)

[@learnssis]

Yes those are totally fine.

[@kristy.rlopez]

Thank you so much for sharing this tutorial! Everything ran well but I was prompted with a window if I wanted to save changes to each file each time it was processed. How can we prevent this from happening or to automate the respond to "Yes"?

[@learnssis]

Did you checked at which task the popup is coming ? Can you put a breakpoint on data flow task and run the package and see at which task the popup is coming ?

[@danialilyasak3941]

Hi, I try to search reference Micorsoft excel object library but there is no item found.

[@learnssis]

As per this url they are saying that you would need to install Office stackoverflow.com/questions/58528300/visual-studio-cant-find-reference-microsoft-excel-object-library

[@soumyabhat9841]

could not find Microsoft Excel 16.0 object library in references part of Script task

[@learnssis]

You would need to install the Office to have this library social.msdn.microsoft.com/Forums/azure/en-US/3dd233f9-8d26-44af-854b-cdbb86897637/how-to-download-the-microsoft-excel-160-object-library?forum=visualstudiogeneral

[@soumyabhat9841]

after doing all the steps still its not working

[@pns4441]

what if there are multiple sheets in the excel file and I would like to import all the sheets ?

[@learnssis]

I have created 2 videos on importing data from multiple excel sheets. Maybe you can take a look at them. kzbin.info/www/bejne/j4jUgGyBe7mEedE kzbin.info/www/bejne/eX7So2asjpyMY6c

[@pns4441]

@@learnssis Thank you very much.

[@pallavikrishna7591]

Thank you very much for sharing this content!!! Really helped me

[@learnssis]

Glad it helped you. You are welcome.

[@kanikeveeresh4448]

Good morning sir. Your explanation is very nice but I don't about c# code then how can I do it without script task in ssis.

[@kanikeveeresh6029]

i don't know about C#

[@learnssis]

@@kanikeveeresh6029 You don't need to write any new code in the C#, the code is already written, just copy and paste it inside your existing package and it will work.

[@blackisblack22]

What if destination table depends on Sheet name? For example, we have A to E tables with different structures, one Excel file with tabs [A, C,D] and another [A,B,E]. How could we send data to the right table dynamically?

[@learnssis]

Yes we can do that one as well, check this video kzbin.info/www/bejne/j4jUgGyBe7mEedE

[@blackisblack22]

@@learnssis Thank you, I really appreciate what you do for us with your videos.

[@learnssis]

@@blackisblack22 No problem.

[@backgrounding4821]

Hello, how about multiple excel files and all of them have the same Sheet's Name?

[@learnssis]

You can watch this video for that kzbin.info/www/bejne/inmcoad3oKh7hKM

[@backgrounding4821]

@@learnssis Thank you very much! much appreciated for fast reply. (Y)

[@Stan-tb1qz]

Hi this video is amazing! But what if the excel file is a csv? I followed all steps until the part of making sheet name dynamic in excel source because I have a flat file source...

[@Stan-tb1qz]

Actually no need, this process also worked for CSV, my FilePath variable was using an expression

[@learnssis]

@@Stan-tb1qz Glad it worked for you.

[@Stan-tb1qz]

@@learnssis Actually a new problem. Your video works when I run the package locally. However it fails when sql server agent job runs the package. I get following error Source: Script Task - Assign sheetname to local variable Description: Exception has been thrown by the target of an invocation. I am on SQL Server 2016

[@Stan-tb1qz]

More info: In the same package I have another script task that sends out an email when the package fails, and a file system task to archive files from source folder to archive folder. The sql server agent job is able to run these tasks without issue. This leads me to beleive it is not a permission issue because the job schedule can move files and send emails, but can not edit file?

[@learnssis]

@@Stan-tb1qz add error handling to the script task so that you know at which line the code is failing. kzbin.info/www/bejne/qGiqo2anbt-LkLM You can also install the drivers needed for Excel kzbin.info?event=comments&redir_token=QUFFLUhqbndCY2hibEduSVBXdEhYNU1mMTA4ZGpheHdld3xBQ3Jtc0ttXzV2WTVPWXJrejFEZnp1ZHhZTW4zek50bjlRVV83ZzJhXzd1cjlQcmdpNGgyejAzZkt6OHB5ZkpsRDM4X0NyeGRMQnFUT2VWN0NtSkpDcGNqa0o4R0ZsNUZKbVladGNmcmhBUzZjQXU3ZlRUTzh4TQ&q=https%3A%2F%2Fwww.microsoft.com%2Fen-za%2Fdownload%2Fdetails.aspx%3Fid%3D13255&stzid=UgysARviHoG8yY3UVKx4AaABAg.9DU3XMPJqTU9DXqeLXIYCW

[@yonisupersaiyanyoni3693]

How to do it without code?

[@learnssis]

Can you check this video if it can help ? kzbin.info/www/bejne/j4jUgGyBe7mEedE

[@yonisupersaiyanyoni3693]

Can't you say take first sheet?

[@OleYoule]

Nothing can work without $ Bro) Thanks for useful video tutorial 👍

[@learnssis]

You are most welcome.

[@soumyabhat9841]

how to add Microsoft Excel 16.0 object library

[@learnssis]

You would need to install MS Office to get access of this library social.msdn.microsoft.com/Forums/azure/en-US/3dd233f9-8d26-44af-854b-cdbb86897637/how-to-download-the-microsoft-excel-160-object-library?forum=visualstudiogeneral

[@soumyabhat9841]

Please do reply..

[@soumyabhat9841]

[Excel Source [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. [Excel Source [2]] Error: Opening a rowset for "a1_BK26_RFBILA00_2020$" failed. Check that the object exists in the database. [SSIS.Pipeline] Error: "Excel Source" failed validation and returned validation status "VS_ISBROKEN". The first file rund properly....a1_BK26_RFBILA00_2020$ but the second fine sheet name is a2_BK26_RFBILA00_2020 for this getting above error

[@ak47ava]

any resolution/fixes for this??