April 25, 2016 at 9:28 am
Hi,
Having a problem getting a package to work.
This package is supposed to load multiple sheets, from a single Excel workbook, into SQL Server 2014.
I am using Visual Studio 2012 Integrated (Shell) and this works very well with every other package I have deployed.
The problem is that it gets stuck on the first tab, loops round that multiple times, and does not move to next tab.
So the user variable is set to the sheet name for the first tab, and then iterates exactly 9 times before hanging.
I have set up the package as follows:
* using ForEach loop container
* collection enumerator = "ForEach ADO .NET Schema Rowset Enumerator"
* Excel file connection = ".NET Provider for OLE DB"
* Provider = "Microsoft Office 12.0 Access Database Engine OLE DB Provider"
* Extended Properties = "Excel 12.0;HDR=YES"
* Schema = "Tables"
* added new User variable "User::SheetName"
* set Index = 2
* within Data Flow Task, set Excel connection as ADO.NET connection
* data access mode = "Table or View"
* within DFT, mapped "[ADO NET Source].[TableOrViewName] property expression to "User::SheetName"
* because the package doesn't have a sheet name prior to execution, I have set DelayValidation = True
* set executable as 32-bit runtime
What am I missing?
April 25, 2016 at 10:03 am
DuncEduardo (4/25/2016)
Hi,Having a problem getting a package to work.
This package is supposed to load multiple sheets, from a single Excel workbook, into SQL Server 2014.
I am using Visual Studio 2012 Integrated (Shell) and this works very well with every other package I have deployed.
The problem is that it gets stuck on the first tab, loops round that multiple times, and does not move to next tab.
So the user variable is set to the sheet name for the first tab, and then iterates exactly 9 times before hanging.
I have set up the package as follows:
* using ForEach loop container
* collection enumerator = "ForEach ADO .NET Schema Rowset Enumerator"
* Excel file connection = ".NET Provider for OLE DB"
* Provider = "Microsoft Office 12.0 Access Database Engine OLE DB Provider"
* Extended Properties = "Excel 12.0;HDR=YES"
* Schema = "Tables"
* added new User variable "User::SheetName"
* set Index = 2
* within Data Flow Task, set Excel connection as ADO.NET connection
* data access mode = "Table or View"
* within DFT, mapped "[ADO NET Source].[TableOrViewName] property expression to "User::SheetName"
* because the package doesn't have a sheet name prior to execution, I have set DelayValidation = True
* set executable as 32-bit runtime
What am I missing?
Have you tried debugging inside your FEL (by setting breakpoints), to check that the SheetName variable is being populated correctly?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 25, 2016 at 10:13 am
yeah I've used breakpoints to debug and the user variable gets assigned to {_xlnm#_FilterDatabase} value. Then errors out at data flow task with:
"the type of the value (Empty) being assigned to variable differs from the current variable type (string)"
April 25, 2016 at 10:23 am
actually previous post was slightly inaccurate.
basically, there is no error as such - the package just doesn't move on to next tab.
It iterates 9 times around the "_xlnm#_FilterDatabase" and then gets stuck without moving further.
The actual data being loaded corresponds to the first tab in the spreadsheet (463 rows) and so 463 x 9 rows are loaded (4167 total).
April 25, 2016 at 11:04 am
So _xlnm#_FilterDatabase is the name of the first tab?
What is your connection string expression?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 25, 2016 at 12:04 pm
No, that object is a hidden one apparently, having read some feedback from another forum.
None of my tabs are called this and there are no hidden tabs (that you can reach manually).
All tabs names are the format "yyyyMM" and I've basically got one tab for each month going back to 201401.
Connection to Excel, in package, is ADO.NET whereby the UNC path is specified.
If I look at the source Excel objects (sheets) in the drop-down within ADO NET Source, I can see all the individual tabs as well as that "hidden" object at the top of the selection.
April 25, 2016 at 12:12 pm
So every iteration of the loop shows the variable containing this hidden tab value? You said your for each loop was configured to hit an ADO recordset. How are you populating the object variable?
April 25, 2016 at 12:20 pm
Yes, every iteration of the loop shows this hidden value assigned to the user variable SheetName.
I can clearly trace this via the Watch window as the package executes.
The connection between Data Flow Task (inside container) and variable is set within the Data Flow Task properties.
The "[ADO NET Source].[TableOrViewName] property is assigned the expression @[User::SheetName] which is the user varible.
So, like I said, the logic appears to be correct but can't help thinking there's something obvious that is amiss.
April 25, 2016 at 12:25 pm
* collection enumerator = "ForEach ADO .NET Schema Rowset Enumerator"
How are you populating the object variable that you are using for your collection enumerator?
April 25, 2016 at 12:37 pm
ForEach Loop Container
-> Collection
-> Enumerator Configuration
-> Schema
-> "Tables"
-> Variable Mappings
-> Variable
-> "User::SheetName"
-> Index
-> "2"
So "2" ties in with "Tables" above as this is 3rd in list of "restrictions"
April 25, 2016 at 12:48 pm
What is the scope of User::SheetName?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 25, 2016 at 12:49 pm
Well, this is more of a work around than a fix, but since you know the exact naming convention of the tabs, you could write a query to return the tab names, push those to an object variable, then use the ADO Enumerator to loop through your query results. I'll keep looking into the issue, but this is certainly an option.
April 25, 2016 at 12:58 pm
Phil Parkin (4/25/2016)
What is the scope of User::SheetName?
Package
April 25, 2016 at 1:04 pm
John Rowan (4/25/2016)
Well, this is more of a work around than a fix, but since you know the exact naming convention of the tabs, you could write a query to return the tab names, push those to an object variable, then use the ADO Enumerator to loop through your query results. I'll keep looking into the issue, but this is certainly an option.
Yes, I was trying to avoid this John.
I think there's a technique whereby this container is nested within another which is using the "ForEach ADO enumerator" and then you can apply some kind of filter but not sure; maybe a bespoe script task to avoid the hidden sheet.
Having said that, I don't need "ForEach ADO" as I only have a single workbook in scope.
Do appreciate you all looking into solutions though.
April 25, 2016 at 1:22 pm
another option is code entire solution inside a script task which references variables like SourcePath, DestinationTable.
again, hoping for more of a standard SSIS solution and minimise the scripting side.
May also try creating a new Excel file in case this one was sourced from a macro-enabled workbook and just try a couple of test tabs.
Could also try previous version of Excel.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply