Solved

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

Posted on 2008-10-30
14
2,896 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:CSTX_Analysts
  • 7
  • 6
14 Comments
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
What variable type is WorkSheetName?
0
 

Author Comment

by:CSTX_Analysts
Comment Utility
Sorry, that was important. String.
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
What type of Enumerator are you using in the General tab?
0
 

Author Comment

by:CSTX_Analysts
Comment Utility
Foreach ADO.NET Schema Rowset Enumerator
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
Try index of 2, not 0. I am looking for an example in my files.
0
 

Author Comment

by:CSTX_Analysts
Comment Utility
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
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:CSTX_Analysts
Comment Utility
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
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
 

Author Comment

by:CSTX_Analysts
Comment Utility
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
 
LVL 17

Accepted Solution

by:
HoggZilla earned 100 total points
Comment Utility
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
 
LVL 17

Assisted Solution

by:HoggZilla
HoggZilla earned 100 total points
Comment Utility
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
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 25 total points
Comment Utility
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
 

Author Closing Comment

by:CSTX_Analysts
Comment Utility
Answered the question I had!!! I will redo the package and see what result I get!! Thanks very much for the help
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now