loop thru multiple excel tabs

I have multiple excel workbooks that contain multiple worksheets.
Is there a way to loop through the workbooks (and the worksheets they contain) for an import to an Access database table?
ktjamms2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jason YousefSr. BI  DeveloperCommented:
Hi,
Is your works sheets are identical? column names and location and names?

If not, You'll need to use a script task...
http://msdn.microsoft.com/en-us/library/ms403358.aspx
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

ktjamms2Author Commented:
I imported the [SSIS_EX_03_FELC.dtsx] into a project. I downloaded the sample test excel file to my local machine and set the connection manager to this test excel file. I get an error "Cannot Create User Component cl....   And I cannot see the rest of the error. What do you think is the problem?
ktjamms2Author Commented:
The error I previously posted is on the script component "SCR Get worksheet names"
SThayaTechnical MAnagerCommented:
Hi,

  are you getting the Sheet names from the Script Component..

For getting the Sheet names , do the below steps

1. Create a variable with a string datatype
2. In the data flow Set access mode as "Table Name or Varibale name"
3. select the Sheet name varibale from the variable name Drop down
4. use a drived column , add a new colum and take the value from the Variable "sheetname"
5. add the drived column in the destination table ..
Now when ever you processing the sheets it will update in ur table
ktjamms2Author Commented:
Hi SThaya:

I'm not sure I follow....
>>In the data flow Set access mode as "Table Name or Varibale name">> what component in the data flow are you referring to?

Are you considering the example posted by vdr1620?
SThayaTechnical MAnagerCommented:
Hi,

  I am using For each loop container and inside i am enumerationg the dataflow task .. see my screen shot DFT
ktjamms2Author Commented:
That's why I was asking if  you were considering the example posted by vdr1620?

Because in this example it instructs the Enumerator be set to Foreach ADO Enumerator and the configuration is:

ForEach-Loop-Editor.docx ForEach-Loop-Editor.docx
SThayaTechnical MAnagerCommented:
Hi,

  select Enumerator type as "Foreach ADO.NET Schema Rowset Enumerator" .. then it will work
ktjamms2Author Commented:
How should I configure the connection for an excel file?
SThayaTechnical MAnagerCommented:
1.select the excel source ---> Right Click select "EDIT"
2. In the OLE DB Connection Manager ---cleck new
3.ADD the excel File path with the file name .
ktjamms2Author Commented:
I already had the excel file configured in the connection manager of the project, but it doesn't show up in the connection of the Foreach Loop Editor.

>>1. select the excel source ---> Right Click select "EDIT"<< do you mean in the Project connection manager?
ktjamms2Author Commented:
Sorry, I'm a newbie here....The Excel Source was configured to the excel file
SThayaTechnical MAnagerCommented:
Hi,

 the above i explained about the Excel Data source only in the data flow.

if you want to configure the For each Loop Editer
1.Open the For each loop editer
2.In the Enumarator Configuration  , click the drop down , it will show the new connection tab--> select the connection ..the refer the scrren shot(1.Jpeg)
 1

3. the select ALL tab set the Extended propeties as "Excel 8.0"(Its for Excel 2003 Format)
ktjamms2Author Commented:
When I try the .Net Providers for OLEDb\Microsoft Jet 4.0 OLE DB Provider  and browse to my excel file and test the connection,  I get an error that it's an unrecognized database format
ktjamms2Author Commented:
Please disregard that last comment...didn't see step 3 in your instruction
ktjamms2Author Commented:
OK, I did everything with the Foreach container...now for the derived column? how do I attach the variable to the new derived colunn?
ktjamms2Author Commented:
ktjamms2Author Commented:
ktjamms2Author Commented:
Still getting the same error "cannot create user component". When I move the mouse over the "X"... I see partial message. How can I view the complete error message?
SThayaTechnical MAnagerCommented:
Hi,

  the Drived Column Seetings is correct .. can you please send me your excel file and Package ,it help me to solve issue quickly
ktjamms2Author Commented:
SThaya:  Here is the file modified as you suggested.
test.xls
ktjamms2Author Commented:
I believe this package was originally created with Visual Studio 2005 and I'm using 2008. I'm getting the impression that this is creating the problem as  a warning message displayed tells me "Return type of function '<cmExcel>' is not CLS-compliant" on the componentwrapper.vb. Does anyone know what the remedy for this could be?  

This is in my ScriptMain:
<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
 Inherits UserComponent

ktjamms2Author Commented:
Thank-you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.