Looping through an excel spreadsheet

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.

Looping through an excel spreadsheet

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.

Looping through an excel spreadsheet

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.

Looping through an excel spreadsheet

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.

sql

Looping through an excel spreadsheet

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.

Looping through an excel spreadsheet

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.

Lookup table insert, update, and delete...

All,

Just wondering if anyone is aware of a SQL server shareware utility that places a front end on a table to manage insert, update, and delete of rows on a lookup table.

We can certainly write this but before reinventing the wheel I figure I'd ask and see.

Many Thanks,

IsaacThe DB Explorer works extensively with look-ups. You will have to define the relationship by referential constraints in the database, or as navigation paths in the client front-end.

You find all resources at MayeticVillage (http://www.MayeticVillage.com/DB-Explorer)

I'll be glad to answer all your questions about it.|||You can edit table data directly in Enterprise Manager.|||You can even use the query analyzer to maintain your data by DML statements, but I invite you to experience the ease of working with the DB Explorer.|||Gosh, I had no idea that DB Explorer was "shareware".

And all this time I've been going to the trouble of right-clicking on any table in Enterprise Manager and selecting "Open Table/Return All Rows"...|||The idea behind the DB Explorer is to exploit the essence of the relational model: the relationship between entities. To work with single tables / views is simpel, any tool including the Enterprise Manager can do so. To put a table / view into a relational context is normally the exclusive domain of client application development, but the DB Explorer is able to generate a user interface on-the-fly, including lookups.

Moreover, you can instantly use the relationships to navigate through your data: from a customer record to the related order records, or vise versa from an order to the corresponding customer. Even within the Enterprise Manager with its knowledge of the existing relations, you would have to write SQL to do so; the DB Explorer works (almost) completely without SQL in the GUI.

Actually, the DB Explorer isn't shareware but freeware. The only restriction is that it can be used in a stand-alone / Client/Server configuration only. As soon as you want to use the tool in a three-tier or/and multi-user configuration, an evaluation period of 1 month will start.

Why don't you give it a try?|||The problem I have with using enterprise mgr or query analyzer is that the users who will be doing the updates are very untechnical.

Ideally, I'd like to provide just a nice front end to the 4 or 5 tables that matter to them.

I can't overwhelm them with database names, a zillion other tables in a large list of tables, and cryptic column names etc etc etc.

Thanks,

Isaac|||Then your cheapest solution is to create an Access Data Project linked to your SQL Server database, with an entry form for each table.|||...Even cheaper would be to create updateable views and grant all permissions to those rather than the tables themselves.|||I think he is looking for a simple user interface, but it would definitely be a good idea to create the views you suggested, along with a login/role that only has access to those views, before allowing people into the database through an Access Data Project. Once they get into the .ADP file, they can get into anything their login allows, or that is poorly secured.|||The problem I have with using enterprise mgr or query analyzer is that the users who will be doing the updates are very untechnical.

Ideally, I'd like to provide just a nice front end to the 4 or 5 tables that matter to them.

I can't overwhelm them with database names, a zillion other tables in a large list of tables, and cryptic column names etc etc etc.
Isaac

Please note that the DB Explorer allows you to present just the tables you need, with functional table and field names. There is also a quite complex access control mechanism integrated, which would allow you to grant read-write access to lookup tables, and read-only access or no access at all to other tables.|||Please note that the poster requested a shareware solution. He did not ask for you to push your companys product by touting all the things it does which can also be done through Enterprise Manager.

DBForums has a Marketplace forum if you wish to advertise.|||Please note that the poster requested a shareware solution. He did not ask for you to push your companys product by touting all the things it does which can also be done through Enterprise Manager.

Which part of the freeware property or extended functionality which is definitively NOT possible with Enterprise Manager didn't you understand?|||So your software is free? My apologies...|||Apologies granted. :cool:

Donatations, however, are always appreciated.|||Curious. Why does your website offer a trial-version of DB Explorer if it is freeware?

I don't want to bother with the demo version. Please post the link where I can just download the entire thing for free.

Thanks!|||Curious. Why does your website offer a trial-version of DB Explorer if it is freeware?

I don't want to bother with the demo version. Please post the link where I can just download the entire thing for free.

Thanks!

Thank you for pointing me for that, the label is misleading. I've changed it. Note, that for trying the software, you will need to have MDAC version 2.8 or higher.

Looking for sample code for doing store procedures

I'm looking for any good samples that anyone might be aware of on how to write a SQL Server 2005 stored procedure with error handling (using TRY-CATCH blocks) that I can use as a model.

I did find the article "Detecting and Reporting Errors in Stored Procedures - Part 2: SQL Server 2005 TRY-CATCH Blocks" by Rob Garrison. It's pretty good. The article was written based upon an early beta so the author (understandable) wasn't sure about some possible features. I also was disappointed that the examples didn't flow back to showing how the application handle the resulting errors.

If you know of any good article or samples, please let me know.

TIA,

Richard Rosenheim
Please refer to the INSTAWDB.sql script installed with the samples. It has several SP's which use the new TRY...CATCH syntax.|||

Please take a look at the TRY...CATCH topics in Books Online. They also contains lot of examples.

TRY...CATCH (Transact-SQL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/248df62a-7334-4bca-8262-235a28f4b07f.htm

Using TRY...CATCH in Transact-SQL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm