HI, i have an excel file with multiple sheets withsame schema. can any one create a sample package to importthe data from all sheets from the excel file into a single destination tablein sql server 2005.. Many Thanks..
I can do it for you!
Just attach or send me the excel file.
Regards,
Pedro
HI Pedro, attached is my test data with 3 sheets.. Many Thanks..
6114.Test.xls
I'll check it during the day.
I have your solution made and I'll post as a StepByStep post.
Please describe the scenario/problem in more detail to publish.
HI Pedro,
Here is the Scenario. I have an excel file to import into SQL Server in a single destination table. The excel file has multiple sheeets with similar schema. I want to import the data from all the sheets into a single table.
Thank You.
and the environment? The data is about customers? Suppliers? from and to where ?
Thanks my friend!
Environment is SQL Server 2005.We are getting the data everymonthfrom the customers in excel files which we need to load itinto SQL Server. the situation above is to import a singleexcel file with multiple sheets with same schema into a single destination table in sql server 2005.
Hope this helps.
Thanks.
Here you have:
http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspx
Check the attached document and give feedback in the blog post.
Helped?
regards,
You saw the blog post? It's usefull for you!?
Improvements?!
Thank you very much pedro. I will check this and will let you know my monday.. many thanks and have a good weekend.
Thanks for this. But i am getting an error message as follows after running the package with the same excel file which i have attached previously. I have attached my package . Can you please let me know where i have gone wrong ?
Thanks.7723.SSIS_EX_03_FELC_vamsi.zip
sorry here is my error message
Error: 0xC0202009 at DFT Extract Data From Excel, EX_SRC Get Each WorkSheet Data [1]: An OLE DB error has occurred. Error code: 0x80004005.Error: 0xC02020E8 at DFT Extract Data From Excel, EX_SRC Get Each WorkSheet Data [1]: Opening a rowset for "'01$' " failed. Check that the object exists in the database.
pedro, when the above package have failed, i have renamed the first excel sheet from 01 to ram, but still i am getting the same error..
Dont forget the point 5.2 and to unsure that the worksheet name is 01
Click on the Excel Source Component and in the properties menu change the AccessMode property to “OpenRowset From variable”
Thank YOu Very much pedro. it works perfectly now. Can youplease add one small requirement to the package. Actuallyi have 31 sheets in the file from 01 to 31 which impliesto 31 days of a certain month. so I will add a new columnnamed 'date' in the destination table. so all the data from 01 shuld be dated as 01-08-2009 and the data from 02 should be02-08-2009 in the 'date' column and so on..
Can you please add this requirement to the package ?
Many Thanks
and the month and year are calculated based on the currentdate, correct?!
it should be for previous month in the current year..theni can change it accordingly..Thanks
HI Pedro, any update on this ?
Thanks
Sorry for the delay!
I'm very busy... but I'll try to do it during the day!
Any update on this. Can you please add that extra datecondition which i have requested in that package which youcreated please ?Thanks
Here you have the updated package.
Take a look.
Improvements?
Am having this error mates...
[EX_SRC Get Each WorkSheet Data [1]] Error: Opening a rowset for "'01$' " failed. Check that the object exists in the database.
I have checked the point 5.2 also in the document.Evereything is set up correctly...But somehow this error keep peeping out......Any ideas please share it mate......This solution i need it urgently.............