Start Free Trial

asked on

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?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

Hi ,,

Refere the below links

http://www.sqllion.com/2009/06/programming-foreach-loop-container-%e2%80%93-enumerating-excel-files/

http://msdn.microsoft.com/en-us/library/ms345182(v=sql.90).aspx

ASKER

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?

ASKER

The error I previously posted is on the script component "SCR Get worksheet names"

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

ASKER

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?

Hi,

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

User generated image

ASKER

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

Hi,

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

ASKER

How should I configure the connection for an excel file?

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 .

ASKER

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?

ASKER

Sorry, I'm a newbie here....The Excel Source was configured to the excel file

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)

User generated image

3. the select ALL tab set the Extended propeties as "Excel 8.0"(Its for Excel 2003 Format)

ASKER

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

ASKER

Please disregard that last comment...didn't see step 3 in your instruction

ASKER

OK, I did everything with the Foreach container...now for the derived column? how do I attach the variable to the new derived colunn?

ASKER

Is this correct?

Derived-Column-Transformation-Ed.docx

ASKER

Script-Component-Error.docx

ASKER

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?

Hi,

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

ASKER

Got it form this link:

http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspx

ASKER

SThaya: Here is the file modified as you suggested.
test.xls

ASKER

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

ASKER

Thank-you!