September 5, 2012 at 2:43 pm
can anyone please help and provide me an example to loop over a set of excel files from a given folder and extract all the information onto a sql server destination
thanks in advance
September 5, 2012 at 2:48 pm
SS53217 (9/5/2012)
can anyone please help and provide me an example to loop over a set of excel files from a given folder and extract all the information onto a sql server destinationthanks in advance
does this help?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 5, 2012 at 3:08 pm
i have tried the example but i'm getting an error saying
"[Excel Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager-source" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed."
September 5, 2012 at 3:53 pm
i have tried this example but encountered with a prob i.e., in FIG 9 the database file are to be added
When i try to add database file name the folder seems to be empty as i have only .xls files in that folder
and when i try to add one of .xls file and test the connection it give an error
so could you please help me resolve this error and execute the package asap
Thanks
September 6, 2012 at 2:11 am
SS53217 (9/5/2012)
http://beyondrelational.com/modules/2/blogs/571/posts/14364/extracting-data-with-ssis-from-multi-excel-file-placed-in-a-folder-having-multiple-excel-sheet.aspxi have tried this example but encountered with a prob i.e., in FIG 9 the database file are to be added
When i try to add database file name the folder seems to be empty as i have only .xls files in that folder
and when i try to add one of .xls file and test the connection it give an error
so could you please help me resolve this error and execute the package asap
Thanks
what type of connection manager are you using ...can we assume its the excel connection manager?
you dont give us the error message,,,,troubleshooting without it is going it will be a long winded process....so pls provide
for clarification you are using SSIS 2005?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 6, 2012 at 11:21 am
hi J Livingston SQL,
as mentioned in the example i have specified
In the Collection I have provided Enumerator As: "Foreach ADO.NET Schema Rowset Enumerator"
Provider: .net provider for Oledb\microsoft jet 4.0 ole provider
for inner For each loop- iterate through excel sheets
and the error when i test connection
"test connection failed because of an error in initializing provider. unrecognized database format"
September 6, 2012 at 11:37 am
SS53217 (9/6/2012)
hi J Livingston SQL,as mentioned in the example i have specified
In the Collection I have provided Enumerator As: "Foreach ADO.NET Schema Rowset Enumerator"
Provider: .net provider for Oledb\microsoft jet 4.0 ole provider
for inner For each loop- iterate through excel sheets
and the error when i test connection
"test connection failed because of an error in initializing provider. unrecognized database format"
Hi
SSIS is not my strongpoint...but I did quite easily set up an SSIS project and add a connection manager for Excel...right click in connection manager>new connection > select excel
why are you using microsoft jet 4.0 ole provider?
of course I maybe totally wrong, at which point I will get my coat.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 6, 2012 at 11:44 am
i changed the provider and followed the example that was specified in link that u posted me
everything works fine but the excel files are not being iterated only the first file that i specify or link is being extracted. how should i make the loop to iterate through all the excel file in that folder and extract that inf onto sql server destination
September 6, 2012 at 11:58 am
Without having something to look at (I had to do this at a previous employer) I can't give you specifics, but you are going to need a file system task to get the name of each file.
September 6, 2012 at 12:02 pm
SS53217 (9/6/2012)
i changed the provider and followed the example that was specified in link that u posted meeverything works fine but the excel files are not being iterated only the first file that i specify or link is being extracted. how should i make the loop to iterate through all the excel file in that folder and extract that inf onto sql server destination
Great...so changing the provider at least moved you forward a step I assume.
Unfortunately, I cannot help you further... I have no direct experience with the process you wish to use.
I would suggest that you reread the articles you have been looking at and be certain that you have all the necessary bits in place....iirc it is quite a detailed article but it also requires reading the narrative not just looking at the the screenshots.
I wish you luck and would further suggest that you explain quite clearly what you have done so far in the set ups and the details of the error(s) if you need to post back.
there are plenty of people on here with vast experience of SSIS...please help them to help you.
Kind regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 6, 2012 at 12:12 pm
there are total 9 excel file in my folder and trying to extract them using for each loop container and data flow task and loading onto sql server destination.
once i execute the package,
the table on sql server destination is showing only one Excel sheet data only...
with following errors
[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 "SALES$" 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.
September 7, 2012 at 2:20 am
I think that error is happening because the worksheets in the Excel files have different names. Presumably it's called 'SALES' in the one which is getting loaded and something else in the next one which is being processed.
Assuming you can't make all the worksheet names the same, you'll have to add some additional logic to grab the sheet names. Been a long time since I've done that, so hopefully someone else can direct you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply