Warning: is_writable() [function.is-writable]: open_basedir restriction in effect. File(/f5/sqllion/public/wp-content/uploads/shareaholic/) is not within the allowed path(s): (/fs3d/sqllion/:/nfsn/apps/php53/lib/php/:/nfsn/apps/php5/lib/php/:/nfsn/apps/php/lib/php/) in /fs3d/sqllion/public/wp-content/plugins/sexybookmarks/sexy-bookmarks.php on line 335
Programming Foreach Loop Container – Enumerating Excel Sheets | SQL Lion
Home > SSIS, SSIS programming > Programming Foreach Loop Container – Enumerating Excel Sheets

Programming Foreach Loop Container – Enumerating Excel Sheets

In my previous article named “Programming Foreach Loop Container – Enumerating Excel Files”, we discussed about enumerating Excel files in a folder and load data from each excel file to the destination by using Foreach File enumerator.

But there may be cases where we may have data in the form of sheets in a single excel file. For example, the total sales of study materials over different countries.

To load all the sheets from a single excel file in Foreach Loop Container, we have to use “Foreach ADO.NET Schema Rowset Enumerator”. This special enumerator is used to enumerate through schema information about a data source i.e. the schema information supported by the OLEDB provider. For example, to get list of tables, schemas, column information, constraints, etc from a database.

Excel Sheets example

Below you will see step by step operations to configure the package for enumerating sheets from a excel file.

Step 1: Create a package with the basic components and tasks. Refer my earlier article for details. Programming Foreach Loop Container – Enumerating Excel Files

Step 2: Create a variable named “CurrentExcelSheet” of String type with default value as “India$” (one of the sheet name) to avoid any validation error.

Step 3: Create Connection manager for source (Excel Connection manager) and for the destination (OLEDB connection manager)

Connection Managers

Step 4: Create ADO .Net connection manager to access the excel file in the Foreach ADO.NET Schema Rowset Enumerator.

Right click on the connection manager area and select “New ADO .NET Connection

ADO .net connection


In the connection manager dialog box, select “Microsoft Jet4.0 OLE DB Provider” so that we can access Excel file by this OLE DB driver.

Microsoft Jet4.0 OLE DB Provider

Then in the “Database file name” section select the Excel file that contains information about all the sales. (If necessary select “All Files *.*” in the file open dialog box.)

Database file name

Leave the User name and password fields as it is. Click on “All” pane at left side and set the value for “Extended Properties” to “Excel 8.0”.

Extended Properties

Then test the connection and press OK.

Step 5: Create a “Foreach Loop Container” and set the “Enumerator” in Collection pane to “Foreach ADO.NET Schema Rowset Enumerator”. Here two more properties you have to set. They are:

a) Connection: Select an ADO.NET connection manager in the list. Here in our case, the connection manager will be “Ado .net for Excel” that we created above.

b) Schema: Select the schema to enumerate. For our case we set “Tables” as we need to enumerate all the sheets of the given excel file.

Keep the “Set Restrictions” settings as it is. For Each Loop Editor

Step 6: Then we have to set the Variable Mappings pane. Here we will set the “User::CurrentExcelSheet” variable to index 2. Here index 2 is specifically used because the table information of the excel sheet is at the 3rd column of the resultset generated by the “Foreach ADO.NET Schema Rowset Enumerator” and the columns of the resultset have 0 based indexing.

Step 7: Create the data flow task name “Sales” where we have one source named “Excel Source (Sales)”, but the table information for the source will be fetched from the variable “User::CurrentExcelSheet” so that at each iteration, we will get the new sheet information from the excel file.

Data Flow Task

For this we have to set the “Data access mode:” to “Table name or view name variable” and the variable name to “User::CurrentExcelSheet”.

Excel Source Editor

Step 8: Create the destination component and all other components as per the requirement and run the package. For more information about the other components, refer my article named “SSIS Programming Basic”.

Control Flow

So we successfully executed the package for extracting data from different sheets of a excel file.

Let’s have a look on the procedures to create the above logic by using Integration Service Programming in SSIS. The code will go in the same way as my previous article “Programming Foreach Loop Container – Enumerating Excel Files” except some modifications in the areas of Enumerator where we are using “Foreach ADO.NET Schema Rowset Enumerator


In the Excel source area the data is extracted from a variable instead of Open Rowset. The code is self descriptive and easy to understand. The code is given in both C# .net and VB .net language for both SQL Server 2005 and SQL Server 2008.

Add the below References before developing the code.

Microsoft.SqlServer.Dts.Design
Microsoft.SqlServer.DTSPipelineWrap
Microsoft.SQLServer.DTSRuntimeWrap
Microsoft.SQLServer.ManagedDTS

Programming Integration Service in SQL Server 2005

Expand image C# .Net Expand
Expand image VB .Net Expand

Programming Integration Service in SQL Server 2008

Expand image C# .Net Expand
Expand image VB .Net Expand

To make your work more easy and effortless, SQL Lion team comes up with a unique tool named “SSIS Component Explorer” that will help you in retrieving the Creation Name, Component Class ID, etc about any component like Control Flow Tasks, Data Flow Transformations, Connections Managers, Log Providers, etc in SSIS package. It also provides code snippets for each component.
Download
the free version from here.
Categories: SSIS, SSIS programming
  1. August 6th, 2009 at 19:38 | #1

    Do you have more information on your tool “SSIS Component Explorer”?

    http://www.perpetualdynamics.com

  2. August 7th, 2009 at 01:01 | #2

    Please visit the below link, download the software “SSIS Component Explorer” and explore it….

    I hope you will get enough information.
    Thanks

  3. ziegheart
    April 6th, 2010 at 18:22 | #3

    thank you for exam
    but i need add the country name from excel sheet(on you exam = india,australia,cananda,japan)continue the last column into the destination
    help me please

    ps. i’m not strong in english ,sorry

  4. Reddeppa
    July 20th, 2010 at 11:34 | #4

    Excellent Explaination

  5. Thulasi.Ram
    June 24th, 2011 at 12:20 | #5

    Hi,I was try to this navigation,But i was getting error,colud plz explain me clearly

  6. Art DeGaetano II
    February 1st, 2012 at 03:15 | #6

    Help, i am trying to use this and when I try to rename the file to an archive location, i am getting the infamous

    The process cannot access the file because it is being used by another process.

    How do I close the connection after the excel file has been loaded so I can continue to the next file and then rename to an archive location? I know its probably simple and I am missing something… please help!

  7. Jey
    April 25th, 2012 at 18:44 | #7

    Hi Arun,

    Thanks for the demo, I’ve cpl of questios.

    1)Step2 says one of the sheet name, but I guess the variable User::CurrentExcelSheet saves the work book path ratherthan sheet name, let me know if I were wrong.

    I’m getting error “Error opening rowset for “File path”"

    2)To create an excel connection manager , have to give a default file path, even though a variable used, are there any other way to create a excel manager without default file name/path?

    Thanks,
    2)

  8. JimFive
    August 22nd, 2012 at 23:15 | #8

    I have run into two problems with this. I am looping through a directory of Excel files and looping through the sheets in file.

    First, I had to set the RetainSameConnection property to False for the Excel Connection Manager. Without this, I was getting an error that the sheet name was not found in the collection. It appears that it was just keeping the original file open instead of moving to the next file.

    Second, It stops with an error after 64 files. I didn’t have time to find a solution so I just moved files around to process them all.

    Thanks
    JimFive

  9. umesh lade
    January 8th, 2013 at 11:35 | #9

    during Excel source editor it give error Opening a rowset for [sales opt$] faild .check that object exist in database

  10. Noah Meyer
    June 6th, 2013 at 03:48 | #10

    Very helpful article, Arun! I was super happy to be able to import my Excel without having to open the Excel via code.

    @umesh lade

  11. Noah Meyer
    June 6th, 2013 at 03:50 | #11

    Hi Umesh, you have to make sure to pre-specify the Excel worksheet’s name in the variable. When you run the package though, the worksheet name can be different than the initial sheet name ythat you specified.

  1. No trackbacks yet.
You must be logged in to post a comment.