SSIS Foreach Loop Editor Variable Mapping Index: Help with assigning a number to the index

I am having some issues with creating a Foreach loop container that will loop through the worksheets of a particular Spreadsheet. One of the problems is knowing what Index number to assign to my Worksheet variable. (See Attached picture) I am a little unclear what the "Index" actually is and what number I should put there. I think the default is 0 but I am getting this error message:

Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::WorkSheetName" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

I assume I am pulling something other than the name of the worksheet but I am unclear what that would be. A little more explanation would be very helpful!


ForeachLoop.bmp
CSTX_AnalystsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HoggZillaCommented:
What variable type is WorkSheetName?
0
CSTX_AnalystsAuthor Commented:
Sorry, that was important. String.
0
HoggZillaCommented:
What type of Enumerator are you using in the General tab?
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.

CSTX_AnalystsAuthor Commented:
Foreach ADO.NET Schema Rowset Enumerator
0
HoggZillaCommented:
Try index of 2, not 0. I am looking for an example in my files.
0
CSTX_AnalystsAuthor Commented:
I have used 1-5 all of them error. 4 of them give me the error in the OP. #2 gives me this error:

Error: 0xC0202009 at Package, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data.".

The file is NOT open and I do have rights to the file.
0
HoggZillaCommented:
To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator

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. For more information, see How to: Add a Connection Manager to a Package.
Create a string variable that will receive the name of the current table on each iteration of the loop.
Add a Foreach Loop container to the Control Flow tab. For information about how to configure the Foreach Loop container, see How to: Configure a Foreach Loop Container.
On the Collection page of the Foreach Loop Editor, select the Foreach ADO.NET Schema Rowset enumerator.
As the value of Connection, select the ADO.NET connection manager that you created previously.
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 Task.


On the Variable Mappings page, map Index 2 to the string variable created earlier to hold the name of the current table.
Close the Foreach Loop Editor.
Create tasks in the Foreach Loop container that use the Excel connection manager to perform the same operations on each Excel table in the specified workbook. If you use a Script Task to examine the enumerated table name or to work with each table, remember to add the string variable to the ReadOnlyVariables property of the Script task.
0
CSTX_AnalystsAuthor Commented:
Yep I have done all of this. ( I have actually been working on this issue on and off for several weeks) That is why I am trying to find out what exactly the "Index" refers to. I am just putting numbers in there with no real idea of what they actually reference.
0
HoggZillaCommented:
I select Index 2, and a Schema of Tables. It works, i tested it. WOW, that was a lot for 125 points. LOL. Good luck!!!

ado-net-foreach-schema.bmp
0
CSTX_AnalystsAuthor Commented:
Those are the exact settings I have. Does not work. However I am much more concerned with what the Index Field refers to and what number 2 refrences as opposed to 1,3,4 or 5? The points are yours if you can tell me!
0
HoggZillaCommented:
OK, I am obsessed now. :-) So far I have this for you. Here is the full link: http://support.microsoft.com/kb/257819/EN-US/
Retrieve Data Source Structure (Metadata) from Excel You can retrieve data about the structure of your Excel data source (tables and fields) with ADO. Results differ slightly between the two OLE DB Providers, although both return at least the same small number of useful fields of information. This metadata can be retrieved with the OpenSchema method of the ADO Connection object, which returns an ADO Recordset object. You can also use the more powerful Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) library for this purpose. In the case of an Excel data source however, where a "table" is either a worksheet or a named range, and a "field" is one of a limited number of generic datatypes, this additional power is not useful. Query Table Information Of the various objects available in a relational database (tables, views, stored procedures, and so forth), an Excel data source exposes only table equivalents, consisting of the worksheets and the named ranges defined in the specified workbook. Named ranges are treated as "Tables" and worksheets are treated as "System Tables," and there is not much useful table information you can retrieve beyond this "table_type" property. You request a list of the available tables in the workbook with the following code: Set rs = cn.OpenSchema(adSchemaTables) The Jet Provider returns a recordset with nine (9) fields, of which it populates only four (4):

" table_name " table_type ("Table" or "System Table") " date_created " date_modifiedThe two date fields for a given table always show the same value, which appears to be the "date last modified." In other words, "date_created" is not reliable.

The ODBC Provider also returns a recordset with nine (9) fields, of which it populates only three (3):

" table_catalog, the folder in which the workbook is located. " table_name. " table_type, as noted earlier.According to the ADO documentation, it is possible to retrieve a list of worksheets only, for example, by specifying the following additional criteria to the OpenSchema method: Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "System Table"))  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HoggZillaCommented:
The OLE DB Jet connection for Excel returns schema object rowsets. Here is a list of the OleDbSchemaGuid objects to choose from. Only Tables is usefull for Excel, at least based on what I read.
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbschemaguid_members(VS.80).aspx
 Here are the 4 Columns in the Tables Rowset. Zero Based ordinals. According to MS the order is always like the table unless otherwise stated.

ado-table-rowset-columns.bmp
0
PedroCGDCommented:
Dear Friend,
Follow HoggZilla solution and also check the package I made in other question. If you need I can customize it for your case and improve it more. Just let me know.
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23680131.html

Regards!
Pedro
www.pedrocgd.blogspot.com
0
CSTX_AnalystsAuthor Commented:
Answered the question I had!!! I will redo the package and see what result I get!! Thanks very much for the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.