?
Solved

loop thru multiple excel tabs

Posted on 2011-10-03
26
Medium Priority
?
323 Views
Last Modified: 2013-11-10
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?
0
Comment
Question by:ktjamms2
26 Comments
 
LVL 16

Accepted Solution

by:
vdr1620 earned 2000 total points
ID: 36904685
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36906790
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ktjamms2
ID: 36909694
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?
0
 

Author Comment

by:ktjamms2
ID: 36909823
The error I previously posted is on the script component "SCR Get worksheet names"
0
 
LVL 11

Expert Comment

by:SThaya
ID: 36909950
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
0
 

Author Comment

by:ktjamms2
ID: 36910043
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?
0
 
LVL 11

Expert Comment

by:SThaya
ID: 36910127
Hi,

  I am using For each loop container and inside i am enumerationg the dataflow task .. see my screen shot DFT
0
 

Author Comment

by:ktjamms2
ID: 36910235
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
0
 
LVL 11

Expert Comment

by:SThaya
ID: 36910267
Hi,

  select Enumerator type as "Foreach ADO.NET Schema Rowset Enumerator" .. then it will work
0
 

Author Comment

by:ktjamms2
ID: 36910456
How should I configure the connection for an excel file?
0
 
LVL 11

Expert Comment

by:SThaya
ID: 36910565
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 .
0
 

Author Comment

by:ktjamms2
ID: 36910601
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?
0
 

Author Comment

by:ktjamms2
ID: 36910639
Sorry, I'm a newbie here....The Excel Source was configured to the excel file
0
 
LVL 11

Expert Comment

by:SThaya
ID: 36910672
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)
0
 

Author Comment

by:ktjamms2
ID: 36910898
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
0
 

Author Comment

by:ktjamms2
ID: 36910949
Please disregard that last comment...didn't see step 3 in your instruction
0
 

Author Comment

by:ktjamms2
ID: 36911077
OK, I did everything with the Foreach container...now for the derived column? how do I attach the variable to the new derived colunn?
0
 

Author Comment

by:ktjamms2
ID: 36911113
0
 

Author Comment

by:ktjamms2
ID: 36912419
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?
0
 
LVL 11

Expert Comment

by:SThaya
ID: 36915740
Hi,

  the Drived Column Seetings is correct .. can you please send me your excel file and Package ,it help me to solve issue quickly
0
 

Author Comment

by:ktjamms2
ID: 36923957
SThaya:  Here is the file modified as you suggested.
test.xls
0
 

Author Comment

by:ktjamms2
ID: 36927046
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

0
 

Author Closing Comment

by:ktjamms2
ID: 36938666
Thank-you!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question