Hi,
I have multiple Excel files to import into single SQL table. All the files(about 35-40) have same metadata like they have same column names, similar data and structure. But the worksheet name is different in all the files. There is only one worksheet in all the Excel files.
Previously, I used UNION ALL component to achieve this but then I had like 3 or 4 Excel files to import. I tried using FOR Each Loop Container but that fails as the Excel table names are different.
Is there any way this could be done?
Any help would be truly appreciated !
Thanks,
S.D
Welcome to teh community!
Take a look at this paper and return again with updated feedback:
http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspx
Regards,
Pedro
Thanks Pedro,
I checked the package and understood that its for looping through different worksheets/tables in a single Excel workbook using For Each Loop Container.
But in my case I need to grab multiple workbooks from within a folder. All workbooks have only one worksheet/table but they all have different names.
so, do I need to modify this package in some way? Please advice.
I'll be thankful to you.
You have several excel files with the same schema, but the worksheet name is different, is it true?!
Hey Pedro,
I apologize, I couldn't reply earlier due to holidays here.
Yes, this is true. I have several excel files with same schema (same kind of data and datatypes) but worksheet name is different.
For Ex,
File Name -- WorkSheet Name
ExcelFileNevadaXXXXXXXX.xls -- NevadaXXXXXXXX
ExcelFileArizonaXXXXXXXX.xls -- ArizonaXXXXXXXX
-
So on and so forth.
Thanks.
The example here satisfy your Excel need, correct?
But you need an aditional ForEach Loop to read several Excel files, confirm!?
Yes, that's correct.
Thanks for your time.
Here you have the package.
Update the SSIS variable uvFolderPath that store the filesystem folder that has the excel files and execute it.
I attached the SSIS package and 2 sample excel files
Helped?
thanks very much !
I'll run this package and will post the findings here.
Ok!
let me know as soon as you can!
regards.
Improvements!?
regards,
Pedro,
1374.ForForum.zip
I tried using the package but ran into errors. Here's the package. Please take a look.
I have added few of the Excel files and also made them small for easy loading.
Thanks for your valuable help !!
:)
Hi sql.techo,
Which version is your SSIS package? 2005? 2008?
I've tried with 2005 with no sucess
It's SQL 2008 version. Let me know if you don't have SQL 2008 installed.
The 2008 SSIS packages are not backward compatible. So, I'll have to create it in 2005 and then re-post it.
OK!
I have the SQL 2008 also. No problem, I'll take a look today.
Hi Pedro,
I have reconfigured the package. Removed the use of expression that looks for current datestamp files only as I learned it's not natively possible within FELC
Package should run now but the error I am getting is related to the VB script.
ERROR: Conversion from string "TA-12-2009" to type 'Date' is not valid.
Snippet from the script:
Me.Output0Buffer.colDate = CDate(Mid(tableInFile.Item("TABLE_NAME").ToString, 2, 2) & "-" & CStr(IIf(Len(Month(Now).ToString) = 2, Month(Now).ToString, ("0" & Month(Now).ToString))) & "-" & Year(Now).ToString)
Thanks very much !
UPDATE: Got that script working. But, this time I have another error.
Error: Variable "User::uvWorksheet" does not contain a valid data object
I have set the name of the worksheet with in first Excel file(ex 'SHEETNAME$').
Also, do I need to change the condition inside Conditional Split Task ?
I appreciate your time and efforts.
Dear Friend,
In the Excel source, go to properties and make sure that AcessMode property is set to "OpenRowset From Variable"
Give feedback!
Looks like you are busy !
But, please reply ASAP if possible or whenever you get a chance otherwise.
Update:
1) I was not using Openrowset as a variable for Accsess Mode under Excel Source Properties.Changed it now.
2) I was using uvWorkshhet instead of uvObjectworksheet under ADO Object Source Variable in collection for inner FELC(for eacch worksheet)
Also, when I execute the Dataflow task separately which loads data from Excel into SQL table using 'sheetname$' as a value it fails but after removing the single quotes i.e. sheetname$ it runs successfully.
When I execute the whole package I get the following error:
[ExcelSourceForImport [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "cmExcel" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Waiting for your reply, :)
Very good!
You are almost therre! :-)
Select the cmExcel connection and in the properties tab, select expressions, and open this property to check the connection to excel... make sense the expression generated from expression?
Do you still need the expression on the connection?
If the error persist attach here... is SSIS 2008, correct?
yes , its SQL 2008.
Take a look. I have used varibales as I need this to run daily on new files.
Here it is take a look.
The error persists.
This is getting interesting !
I re-configured the whole package.
Now it's running and only loading a single Excel files(contains just one worksheet with name like CTAZ20090930$) and fails after that with Error like this
[ExcelSourceForImport [1]] Error: Opening a rowset for "CTAZ20090930$" failed. Check that the object exists in the database.
Almost...almost there! :-)
Attach the new version of the package and 2 excel files examples... (or send to my email)
I have forwarded the new version to your e-mail address.
Did you get a chance to test the package?
Happy New Year !!