Hi, I want to load multiple excel files to a table in db. I saw the particular link http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html and created the package but i am getting the error: [Excel Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. [Excel Source [1]] Error: Opening a rowset for failed. Check that the object exists in the database. [SSIS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_ISBROKEN". [SSIS.Pipeline] Error: One or more component failed validation. Error: There were errors during task validation. the error occur after loading the first file where 233 records are loaded, there are about 3 excel files to load in the db. but when I created sample excel files and added some data and used the same example link.. i am able to load the file. but when i used my excel files it is not able to load, i followed the same steps as he told to create in the link. created the dataflow task, created the excel source manger attached the first file to it and added the OLEDb destination added the foreach loop container, in the collection i choosed foreachfile enumerator, and given the path etc. create the package variable FileName and given the path. in the properties of the excel manager, i have given the expresion as "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";" i dont know where it is going wrong ..i am scaching my head from past 1 day? can any body help me out.

Set a breakpoint for each loop iteration of the ForEach Loop to inspect the @[User::FileName] variable content thus making sure it gets populated with the correct value. You can use the Watch window to see the variable contents.Arthur My Blog

here the problem is SSIS not able to read the format of your excel. it seems you are using a forloop container and looping all the files dynamically. try these steps 1. remove the variable for path in the excel source and give the direct path to the excel sheet which you are trying to upload and see whether its producing any error, if yes then reset the format and make it perfect.-- this is the most common problem. Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

first check http://plexussql.blogspot.com/2010/04/looping-through-excel-files-and-sheets.html i can even send you the package i see that you are missing a IMEX = 1 in you r connection string , how do i know? well you mentioned that a file was loading untill row 233 so the only reason that i can see is that the data type is miss mached you have to 1- redirect the bad records from the excel object 2- right click on the Excel source connection object in DFT and select the ADVANCE object and set the right DATA TYPE output for each field good luckSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

when i removed the variable and run the package , i am able to get the data of file1 3 times(233x3=) because there are 3 files in the folder. and also i tried other solultion like i have created 3 excel sources and used union all transformation and direct it to the destination it worked fine for me.

I am assumig that all of the 3 files have the same metadata so you only need 1 package (like the one in my link) that loops through files and maybe sheets dont use the 3 excel source scenario that you mentioned and if you do it will only work for FIXED file names,Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).