Being new to SSIS I wish to loop through a series of excel spreadsheets and within each workbook loop through each sheet. I am aware of the For Each container but how can the each sheet in the workbook be referenced?
Steve
Use the ForEach file enumerator.
-Jamie
|||Thanks Jamie, my problem is for each spreadsheet I loop through how do I reference each sheet / tab within the spreadsheets returned by the For EachLoop.
Thanks
Steve
|||Hi Steve,
Both of our problem is the same. I couldn't loop through Each Sheet in a Excel File. I tried with ForEach Loop File Enumerator, but I couldn't acheive it.
Jamie,
Need your help.
Thanks & Regards,
Prakash Srinivasan
|||Hi Guys,
Any updates or any ideas?
Urgent Please.
Thanks & Regards,
Prakash Srinivasan
|||You can use a Foreach Loop and the Foreach ADO.NET Schema Rowset enumerator, return a TABLES rowset, and loop through each table. Note that both worksheets (with the $ suffix) and named ranges are TABLES in Excel. The SP1 refresh of BOL will include a new topic that discusses this and another aspect or two of working with Excel files.
-Doug
|||
Hi Doug,
As you mentioned, I tried with ForEach Loop ADO.NET Schema Rowset Enumerator, but I am not able to provide the Connection for Excel Files. I tried with both Microsoft Jet 4.0 OLEDB Provider as well as ODBC for Excel, but it is giving me an error.
So if you explain this in detail it will be very much helpful to me.
Expecting your reply ASAP.
Thanks & Regards,
Prakash Srinivasan.
|||try this:
set the delay validation to TRUE in your package properties, this may fix the error you are getting from the foreach loop going thru your sheets
|||Hi,
I tried this setup (delay validation as true) very long back. Now my concern is like how do we create the connection for Excel when you are trying with Foreach ADO.NET Schema Rowset Enumerator.
It is not supporting for Excel Files. Please advice.
Thanks for your help.
Prakash Srinivasan
|||Hi All,
Setting delayvalidation to true does not seem to help, I have a foreach loop for all the excel files, then a forech loop for the sheet names, how do I assign the variables to the for the filename and the sheet name, i am current generating an SQL qury varaible for the sheet ie select * from [sheetname$] , however the excel data source refuses to work, has anybody got a worked example or simple explanation.
Many thanks
Steve
|||You need to use an ADO.NET Connection Manager, the Jet Provider, and on the All page of the editor, enter "Excel 8.0" as the value of the Extended Properties argument.
-Doug
|||Steve,
When looping through tables, I assume that you will want to use "Table name from variable" in the Excel Source,
When looping through Excel files, you will need to concatenate the filename into the connection string by using an expression. There is a sample in the following thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=103273&SiteID=1
As for the validation issue, you can either put a valid file path in the ConnectionString property of the connection manager to avoid a validation error (if you've set an expression, this value will never be used), or set DelayValidation as you've done.
-Doug
|||Hi,
I tried with "Table Name from Variable" option in Excel Source also. But I am not at all able to close that dialog box as it is giving an error message.
Also I tried giving DelayValidation as True only for DataFlow Task. Still it doesn't work. So if you can send me the process in detail, that will be really helpful to me to get this resolved.
Thanks in advance.
Regards,
Prakash Srinivasan.
|||Yes,
I would appreciate it spelt out as I am finding this thoroughly confusing. Will keep persevering though.
Steve
|||Here is the draft of a revised BOL topic, copied into plain text because the HTML can't be copied neatly.
How to: Loop through Excel Files and Tables
Introduction
The procedures in this topic describe how to loop through the Excel workbooks in a folder, or through the tables in an Excel workbook, by using the Foreach Loop container with the appropriate enumerator.
Procedures
To loop through Excel files by using the Foreach File enumerator
1. Create a string variable that will receive the current Excel path and filename on each iteration of the loop. (The sample expression shown later in this procedure uses the variable name ExcelFile, with no initial value.)
2. Optionally, create another string variable that will hold the value for the Extended Properties argument of the Excel connection string. This argument contains a series of values that specify the Excel version and determine whether the first row contains column names, and whether import mode is used. (The sample expression shown later in this procedure uses the variable name ExtProperties, with an initial value of Excel 8.0;HDR=Yes.)
3. Add a Foreach Loop container to the Control Flow tab and configure it as described in How to: Configure a Foreach Loop Container.
4. On the Collection page of the Foreach Loop Editor, select the Foreach File enumerator, specify the folder in which the Excel workbooks are located, and specify the file filter (normally *.xls).
5. On the Variable Mapping page, map Index 0 to a user-defined string variable that will receive the current Excel path and filename on each iteration of the loop. (The sample expression shown later in this procedure uses the variable name ExcelFile.)
6. Close the Foreach Loop Editor.
7. Add an Excel connection manager to the package.
Note To avoid validation errors later as you configure tasks and data flow components to use this connection manager, assign a default Excel workbook in the Excel Connection Manager Editor. After creating and configuring the package, you can delete this value in the Properties window. However, after you delete this value, a validation error may occur because the connection string property of the Excel connection manager is no longer valid until the Foreach Loop runs. In this case, set the DelayValidation property to True on the connection manager, on the tasks in which it is used, or on the package.
8. Select the new Excel connection manager, click the Expressions property in the Properties window, and then click the ellipsis.
9. In the Property Expressions Editor, select the ConnectionString property, and click the ellipsis.
10. In the Expression Builder, enter the following expression:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""
Note the use of the escape character "\" to escape the inner quotes required around the value of the Extended Properties argument.
11. Create tasks within the Foreach Loop container that use the Excel connection manager to perform the same operations on each Excel workbook that matches the specified file location and pattern.
To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator
1. Create an ADO.NET connection manager that uses the Microsoft Jet OLE DB Provider to connect to an Excel workbook. On the All page of the Connection Manager dialog box, make sure that you enter Excel 8.0 as the value of the Extended Properties property.
2. Create a string variable that will receive the name of the current table on each iteration of the loop.
3. Add a Foreach Loop container to the Control Flow tab. For information on configuring the ForeachLoop, see How to: Configure a Foreach Loop Container.
4. On the Collection page of the Foreach Loop Editor, select the Foreach ADO.NET Schema Rowset enumerator.
5. As the value of Connection, select the ADO.NET connection manager that you created previously.
6. As the value of Schema, select Tables.
Note The list of tables in an Excel workbook includes both worksheets (which have the $ suffix) and named ranges. If you have to filter the list for only worksheets or only named ranges, you may have to write custom code in a Script task for this purpose. For more information, see Working with Excel Files with the Script Taskb8fa110a-2c9c-4f5a-8fe1-305555640e44.
7. On the Variable Mappings page, map Index 2 to the string variable created earlier to hold the name of the current table.
8. Close the Foreach Loop Editor.
9. Create tasks within the Foreach Loop container that use the Excel connection manager to perform the same operations on each Excel table in the specified workbook.
