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?
 
Jason Yousef, MSSr. 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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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?
0
 
ktjamms2Author Commented:
The error I previously posted is on the script component "SCR Get worksheet names"
0
 
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
0
 
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?
0
 
SThayaTechnical MAnagerCommented:
Hi,

  I am using For each loop container and inside i am enumerationg the dataflow task .. see my screen shot DFT
0
 
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
0
 
SThayaTechnical MAnagerCommented:
Hi,

  select Enumerator type as "Foreach ADO.NET Schema Rowset Enumerator" .. then it will work
0
 
ktjamms2Author Commented:
How should I configure the connection for an excel file?
0
 
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 .
0
 
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?
0
 
ktjamms2Author Commented:
Sorry, I'm a newbie here....The Excel Source was configured to the excel file
0
 
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)
0
 
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
0
 
ktjamms2Author Commented:
Please disregard that last comment...didn't see step 3 in your instruction
0
 
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?
0
 
ktjamms2Author Commented:
0
 
ktjamms2Author Commented:
0
 
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?
0
 
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
0
 
ktjamms2Author Commented:
SThaya:  Here is the file modified as you suggested.
test.xls
0
 
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

0
 
ktjamms2Author Commented:
Thank-you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.