Solved

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

Posted on 2008-10-30
14
2,949 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
ID: 22844646
What variable type is WorkSheetName?
0
 

Author Comment

by:CSTX_Analysts
ID: 22844654
Sorry, that was important. String.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22844664
What type of Enumerator are you using in the General tab?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:CSTX_Analysts
ID: 22844694
Foreach ADO.NET Schema Rowset Enumerator
0
 
LVL 17

Expert Comment

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

Author Comment

by:CSTX_Analysts
ID: 22844810
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
ID: 22844812
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
 

Author Comment

by:CSTX_Analysts
ID: 22844891
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
ID: 22845073
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
ID: 22846148
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
ID: 22846311
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
ID: 22846950
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
ID: 22849136
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
ID: 31511830
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

777 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