Foreach ADO.NET Schema Rowset Enumerator won't loop through Excel Workbook
-
Hello all,
I've searched the forums and internet for this problem all to no avail. Hopefully one of you can shed some light on the issue...
I have a SSIS package importing a variety of Excel Workbook data sources. Due to the nature of the workbooks themselves, I've decided to connect to these sources using a ADO.NET connection, which I loop through using a [Foreach ADO.NET Schema Rowset Enumerator] to load all the tables/worksheets. All but one file type has been configured successfully -- the anomaly has been configured just like all the other imports but for some reason, won't enter the loop to run the DataFlow. It's as if the loop isn't recognizing the tables/worksheets, so it runs the loop "successfully" without ever entering inside it to execute the internal tasks. I've set no Restrictions on the loop itself.
Does anyone know what may be happening to cause this? Please let me know should you need any more detail or clarification.
Many thanks in advance.
-
All but one file type has been configured successfully
Please describe what you mean by "file type".
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I assumed that may be questioned 🙂
What I mean is different data sets. So for example, let's say I have 5 "file types"...
1) Product Mix file
2) Sales file
3) Items file
4) Cost file
5) Stores file
Using these examples, I've successfully configured the first four "types" to load the datasets via an ADO.NET connection and a [Foreach ADO.NET Schema Rowset Enumerator] Loop. For some reason, however, the loop that should cycle through all worksheets in my Stores file [file type] won't actually enter the loop to execute the DataFlow -- yes, the Stores file is not an empty workbook.
Hope this helps clarify.
Thanks again!
-
reelnoncents (11/23/2016)
I assumed that may be questioned 🙂What I mean is different data sets. So for example, let's say I have 5 "file types"...
1) Product Mix file
2) Sales file
3) Items file
4) Cost file
5) Stores file
Using these examples, I've successfully configured the first four "types" to load the datasets via an ADO.NET connection and a [Foreach ADO.NET Schema Rowset Enumerator] Loop. For some reason, however, the loop that should cycle through all worksheets in my Stores file [file type] won't actually enter the loop to execute the DataFlow -- yes, the Stores file is not an empty workbook.
Hope this helps clarify.
Thanks again!
Are they all .xlsx files, created in the same version of Excel?
Does someone have the file open for editing somewhere? (We've all done it!)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
4 of the six are "Microsoft Excel Worksheets" (.xlsx) and the other two are "Microsoft Excel 97-2003 Worksheets" (.xls). The files are definitely not open for editing -- a common check I find myself having to do too often 🙂
I used the ACE provider for 5 of the files, even one of the .xls files [which executes successfully]. The remaining file which is causing my frustration is the other .xls file. I've tried both JET & ACE providers -- both with the same result: still cannot enter the Foreach ADO.NET Schema Rowset Enumerator Loop. The loop executes "successfully", but the process never enters the loop for this one file. Again, this loop should traverse through the worksheets within the remaining .xls file and certain tasks should be executed within the loop per worksheet. I'm using ADO.NET connections with dynamic connection strings built using a @CurrentFile variable. Each of the Foreach ADO.NET loops is housed inside a Foreach File Enumerator that loops through a directory and populates the @CurrentFile variable.
-
FYI, the expressions for the connection strings look something like this (with minor tweaks based on file):
"Data Source=" + @[User::strCurrentFile] + ";Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\";"
"Data Source=" + @[User::strCurrentFile] + ";Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\";"
Below is an image of the problem section. I have a [Foreach File Enumerator] loop (04 - Load Raw Data) that loops through a directory and populates the @[User::strCurrentFile] variable. Within this loop, I have several [Foreach ADO.NET Schema Rowset Enumerator] loops that connect to an Excel source, loops through the worksheets/tables, and runs a Data Flow task on each iteration. These [Foreach ADO.NET Schema Rowset Enumerator] loops are configured identically from one another except for which connection manager they utilize. Each connection manager is, again, configured nearly identically from one another except for minor differences in the connection string: (HDR = YES/NO, JET vs ACE provider). The disabled Store List loop is my trouble. As discussed, for some reason it will not enter inside the ADO.NET loop to execute the child Data Flow.
-
The casing is taken into accounts with names of the worksheets? Though I believe this would produce an error still good to be sure. And the list(s) in that fifth work are in similar formats, no groupings? The casing of column headers is consistent?
For some reason I was unable to see the picture that you posted. Maybe something here on the network is blocking it.
----------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply