Excel export missing table header

I am working with RS2000 SP2 and VS2003.

I have a simple report with just one table, two levels of grouping with subtotals and a table header. There is also a separate page header. The report renders correctly on screen and when printed.

When I export the report in Excel format the detail lines, group sub-totals and page head appear. However, the problem I have is that the table header is not shown. So the user can't make out what data is shown each column. How can I get the table header to appear when exporting to Excel?

TIA, Magendo_man

To complicate matters further, I have added another table to this report, which is a copy of the original one. This table uses the same dataset but is filtered and sorted differently, with no grouping and a footer total. I set the table to have a page break before it is printed.

Now when I export to Excel I get the same problem with missing table header for the first table, but I do get the table header for this second table.

TIA, Magendo_man

Excel Export error in RS200SP2 when nesting tables within matirx

I was hoping someone could help out with this issue....I've got a report I created in Reporting Services 2000 SP2 where I am nesting table elements within the row and detail sections of the matrix. This allows me to have multiple items within my "row" data with column headings, and works perfectly fine within the designer, and viewer, and whenever I export it to any format other than Excel. Exporting to excel results in a "Specified cast is not valid" error.

To me this seems to be a bug in the Excel rendering extension, since it seems to be supported in RDL. Has anyone else run into this? Is there a fix to the renderer or a workaround that anyone is aware of?

I'd be happy to post the RDL if anyone is interested.

Thanks,

Casey

The Excel rendering extension does not support exporting nested tables within tables/matrices, so this won't work for you. The behavior is documented in books online somewhere.

However, normally the "nested" region should simply be exported as a blank space without throwing an error. A bug was reported on this problem, and it is fixed in 2005 (by "fixed", you still won't be able to export nested data regions, but you won't get an error).

|||

That's cool....I figured it wouldn't work, but thought it worth a try. I using nested tables mostly to give me the ability to add headers to rowgroup items, which I was able to reproduce visually by dropping a rectangle object on the matrix and arranging textboxes to simulate the table headers.

Thanks for the response.

|||

Were you able to find a workaround for exporting the nested tables to Excel?|||Not really, it looks like the limitation is in the Excel renderer. What I was able to do in my case which helped was to embed rectangles within the matrix column headers and add multiple text boxes within them. For the report I was working on that gave me the ability to approximate the format I was wanting. Not perfect, but good enough.

Excel Export error in RS200SP2 when nesting tables within matirx

I was hoping someone could help out with this issue....I've got a report I created in Reporting Services 2000 SP2 where I am nesting table elements within the row and detail sections of the matrix. This allows me to have multiple items within my "row" data with column headings, and works perfectly fine within the designer, and viewer, and whenever I export it to any format other than Excel. Exporting to excel results in a "Specified cast is not valid" error.

To me this seems to be a bug in the Excel rendering extension, since it seems to be supported in RDL. Has anyone else run into this? Is there a fix to the renderer or a workaround that anyone is aware of?

I'd be happy to post the RDL if anyone is interested.

Thanks,

Casey

The Excel rendering extension does not support exporting nested tables within tables/matrices, so this won't work for you. The behavior is documented in books online somewhere.

However, normally the "nested" region should simply be exported as a blank space without throwing an error. A bug was reported on this problem, and it is fixed in 2005 (by "fixed", you still won't be able to export nested data regions, but you won't get an error).

|||

That's cool....I figured it wouldn't work, but thought it worth a try. I using nested tables mostly to give me the ability to add headers to rowgroup items, which I was able to reproduce visually by dropping a rectangle object on the matrix and arranging textboxes to simulate the table headers.

Thanks for the response.

|||

Were you able to find a workaround for exporting the nested tables to Excel?|||Not really, it looks like the limitation is in the Excel renderer. What I was able to do in my case which helped was to embed rectangles within the matrix column headers and add multiple text boxes within them. For the report I was working on that gave me the ability to approximate the format I was wanting. Not perfect, but good enough.

sql

Excel Export error in RS200SP2 when nesting tables within matirx

I was hoping someone could help out with this issue....I've got a report I created in Reporting Services 2000 SP2 where I am nesting table elements within the row and detail sections of the matrix. This allows me to have multiple items within my "row" data with column headings, and works perfectly fine within the designer, and viewer, and whenever I export it to any format other than Excel. Exporting to excel results in a "Specified cast is not valid" error.

To me this seems to be a bug in the Excel rendering extension, since it seems to be supported in RDL. Has anyone else run into this? Is there a fix to the renderer or a workaround that anyone is aware of?

I'd be happy to post the RDL if anyone is interested.

Thanks,

Casey

The Excel rendering extension does not support exporting nested tables within tables/matrices, so this won't work for you. The behavior is documented in books online somewhere.

However, normally the "nested" region should simply be exported as a blank space without throwing an error. A bug was reported on this problem, and it is fixed in 2005 (by "fixed", you still won't be able to export nested data regions, but you won't get an error).

|||

That's cool....I figured it wouldn't work, but thought it worth a try. I using nested tables mostly to give me the ability to add headers to rowgroup items, which I was able to reproduce visually by dropping a rectangle object on the matrix and arranging textboxes to simulate the table headers.

Thanks for the response.

|||

Were you able to find a workaround for exporting the nested tables to Excel?|||Not really, it looks like the limitation is in the Excel renderer. What I was able to do in my case which helped was to embed rectangles within the matrix column headers and add multiple text boxes within them. For the report I was working on that gave me the ability to approximate the format I was wanting. Not perfect, but good enough.

Excel export drill-down problem (sp2)

Hi all,
I have a relatively straight forward report with a Table and a grouping on
the details group. I have the the ToggleItem property of the details group
set to the first cell of the detail row, and it renders as expected in HTML.
(An expand/collapse graphic appears to the left of the cell and dynamically
shows/hides the child rows when clicked.) However, when I export to Excel
the groupings are lost and I see only the root rows and none of the children.
From my understanding the export should render the child rows using the
Outline feature of Excel, but for the life of me I can't get this to work.
Any suggestions on what I'm doing wrong here?
Thanks
BillHello Bill,
If you have drill down reports then you could use show-and-hide toggle
<different from hidden items> and if rendering is done in HTML then it will
show collapsed items so that users can click on the toggle to view hidden
groups.
Using other rendering extension like Excel then the drill down effect is
not supported.
Please see the following topic in Books On-Line for Reporting Services:
Drill Down Reports and Hidden Items.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/h
tm/rcr_creating_interactive_v1_8bg4.asp>
Based on my test, Toggleitem feature seems to work for Matrix after
exporting to Excel. However, it does not work properly for table after
exporting to Excel. I think it needs to contain all of the data and then
provide the ability to toggle but apprarently these data is not included in
this situation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Excel export drill-down problem (sp2)
| thread-index: AcWMm3svM+OGxhhzSJqE5HRfXsUjew==| X-WBNR-Posting-Host: 156.153.255.243
| From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
| Subject: Excel export drill-down problem (sp2)
| Date: Tue, 19 Jul 2005 12:53:06 -0700
| Lines: 15
| Message-ID: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:48405
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi all,
|
| I have a relatively straight forward report with a Table and a grouping
on
| the details group. I have the the ToggleItem property of the details
group
| set to the first cell of the detail row, and it renders as expected in
HTML.
| (An expand/collapse graphic appears to the left of the cell and
dynamically
| shows/hides the child rows when clicked.) However, when I export to
Excel
| the groupings are lost and I see only the root rows and none of the
children.
| From my understanding the export should render the child rows using the
| Outline feature of Excel, but for the life of me I can't get this to work.
|
| Any suggestions on what I'm doing wrong here?
|
| Thanks
| Bill
||||Hi Peter,
I've seen quite a few posts on this newsgroup from other people who have
gotten the "Outlining" feature of Excel to work when rendering from Reporting
Services. How is this done?
If it's only supported through a Matrix report that's ok, however I was
unable to get that working either. I saw the same behavior as before, where
the Matrix renders with expand/collapse columns in HTML and they disappear in
Excel. If I have to write an Excel-specific version of the report that's ok,
but I can't get the Outlining feature to work at all.
I have a hard (non-negotiable) requirement to get this feature working, so
any help would be greatly appreciated!
Thanks
"Peter Yang [MSFT]" wrote:
> Hello Bill,
> If you have drill down reports then you could use show-and-hide toggle
> <different from hidden items> and if rendering is done in HTML then it will
> show collapsed items so that users can click on the toggle to view hidden
> groups.
> Using other rendering extension like Excel then the drill down effect is
> not supported.
> Please see the following topic in Books On-Line for Reporting Services:
> Drill Down Reports and Hidden Items.
> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/h
> tm/rcr_creating_interactive_v1_8bg4.asp>
> Based on my test, Toggleitem feature seems to work for Matrix after
> exporting to Excel. However, it does not work properly for table after
> exporting to Excel. I think it needs to contain all of the data and then
> provide the ability to toggle but apprarently these data is not included in
> this situation.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> --
> | Thread-Topic: Excel export drill-down problem (sp2)
> | thread-index: AcWMm3svM+OGxhhzSJqE5HRfXsUjew==> | X-WBNR-Posting-Host: 156.153.255.243
> | From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
> | Subject: Excel export drill-down problem (sp2)
> | Date: Tue, 19 Jul 2005 12:53:06 -0700
> | Lines: 15
> | Message-ID: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:48405
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi all,
> |
> | I have a relatively straight forward report with a Table and a grouping
> on
> | the details group. I have the the ToggleItem property of the details
> group
> | set to the first cell of the detail row, and it renders as expected in
> HTML.
> | (An expand/collapse graphic appears to the left of the cell and
> dynamically
> | shows/hides the child rows when clicked.) However, when I export to
> Excel
> | the groupings are lost and I see only the root rows and none of the
> children.
> | From my understanding the export should render the child rows using the
> | Outline feature of Excel, but for the life of me I can't get this to work.
> |
> | Any suggestions on what I'm doing wrong here?
> |
> | Thanks
> | Bill
> |
>|||I have table (not Matrix) where it does use the outlining feature. I created
a drill down report and everything worked as advertised in Excel. I'm a loss
at why it isn't for you but it did for me. I only have a single level of
drill down and haven't tried multiple levels (just trying to think what
might be different for you). Create a simple report with a single drilldown,
does it work or not for you?
Anyway, just thought I would let you know that it does work with tables.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bill Mers" <billmers@.newsgroup.nospam> wrote in message
news:E2AA4A4B-09D6-400C-AF2A-657366E71870@.microsoft.com...
> Hi Peter,
> I've seen quite a few posts on this newsgroup from other people who have
> gotten the "Outlining" feature of Excel to work when rendering from
> Reporting
> Services. How is this done?
> If it's only supported through a Matrix report that's ok, however I was
> unable to get that working either. I saw the same behavior as before,
> where
> the Matrix renders with expand/collapse columns in HTML and they disappear
> in
> Excel. If I have to write an Excel-specific version of the report that's
> ok,
> but I can't get the Outlining feature to work at all.
> I have a hard (non-negotiable) requirement to get this feature working, so
> any help would be greatly appreciated!
> Thanks
> "Peter Yang [MSFT]" wrote:
>> Hello Bill,
>> If you have drill down reports then you could use show-and-hide toggle
>> <different from hidden items> and if rendering is done in HTML then it
>> will
>> show collapsed items so that users can click on the toggle to view hidden
>> groups.
>> Using other rendering extension like Excel then the drill down effect is
>> not supported.
>> Please see the following topic in Books On-Line for Reporting Services:
>> Drill Down Reports and Hidden Items.
>> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/h
>> tm/rcr_creating_interactive_v1_8bg4.asp>
>> Based on my test, Toggleitem feature seems to work for Matrix after
>> exporting to Excel. However, it does not work properly for table after
>> exporting to Excel. I think it needs to contain all of the data and then
>> provide the ability to toggle but apprarently these data is not included
>> in
>> this situation.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>
>> --
>> | Thread-Topic: Excel export drill-down problem (sp2)
>> | thread-index: AcWMm3svM+OGxhhzSJqE5HRfXsUjew==>> | X-WBNR-Posting-Host: 156.153.255.243
>> | From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
>> | Subject: Excel export drill-down problem (sp2)
>> | Date: Tue, 19 Jul 2005 12:53:06 -0700
>> | Lines: 15
>> | Message-ID: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
>> | MIME-Version: 1.0
>> | Content-Type: text/plain;
>> | charset="Utf-8"
>> | Content-Transfer-Encoding: 7bit
>> | X-Newsreader: Microsoft CDO for Windows 2000
>> | Content-Class: urn:content-classes:message
>> | Importance: normal
>> | Priority: normal
>> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
>> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> | Xref: TK2MSFTNGXA01.phx.gbl
>> microsoft.public.sqlserver.reportingsvcs:48405
>> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>> |
>> | Hi all,
>> |
>> | I have a relatively straight forward report with a Table and a grouping
>> on
>> | the details group. I have the the ToggleItem property of the details
>> group
>> | set to the first cell of the detail row, and it renders as expected in
>> HTML.
>> | (An expand/collapse graphic appears to the left of the cell and
>> dynamically
>> | shows/hides the child rows when clicked.) However, when I export to
>> Excel
>> | the groupings are lost and I see only the root rows and none of the
>> children.
>> | From my understanding the export should render the child rows using
>> the
>> | Outline feature of Excel, but for the life of me I can't get this to
>> work.
>> |
>> | Any suggestions on what I'm doing wrong here?
>> |
>> | Thanks
>> | Bill
>> |
>>|||Hi Bruce,
Thanks for the confirmation that the Excel outling feature does work with
just a straight table. Somebody on my team was able to get it to work as
well, however we still can't get it working with my particular report. What
I suspect might be making the difference is that my report has both the
grouping and toggleItem set on the same row. The table has only two rows in
it: the header row and the details row, and the first column in the detail
row is the toggle for the whole row. This renders fine in HTML but doesn't
export to Excel correctly.
Anybody have any ideas on how to get this to work? I've opened a trouble
ticket with Microsoft but so far that hasn't solved the issue. Any
alternatives/workarounds would be much appreciated.
Thanks
Bill
"Bruce L-C [MVP]" wrote:
> I have table (not Matrix) where it does use the outlining feature. I created
> a drill down report and everything worked as advertised in Excel. I'm a loss
> at why it isn't for you but it did for me. I only have a single level of
> drill down and haven't tried multiple levels (just trying to think what
> might be different for you). Create a simple report with a single drilldown,
> does it work or not for you?
> Anyway, just thought I would let you know that it does work with tables.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Bill Mers" <billmers@.newsgroup.nospam> wrote in message
> news:E2AA4A4B-09D6-400C-AF2A-657366E71870@.microsoft.com...
> > Hi Peter,
> >
> > I've seen quite a few posts on this newsgroup from other people who have
> > gotten the "Outlining" feature of Excel to work when rendering from
> > Reporting
> > Services. How is this done?
> >
> > If it's only supported through a Matrix report that's ok, however I was
> > unable to get that working either. I saw the same behavior as before,
> > where
> > the Matrix renders with expand/collapse columns in HTML and they disappear
> > in
> > Excel. If I have to write an Excel-specific version of the report that's
> > ok,
> > but I can't get the Outlining feature to work at all.
> >
> > I have a hard (non-negotiable) requirement to get this feature working, so
> > any help would be greatly appreciated!
> >
> > Thanks
> >
> > "Peter Yang [MSFT]" wrote:
> >
> >> Hello Bill,
> >>
> >> If you have drill down reports then you could use show-and-hide toggle
> >> <different from hidden items> and if rendering is done in HTML then it
> >> will
> >> show collapsed items so that users can click on the toggle to view hidden
> >> groups.
> >>
> >> Using other rendering extension like Excel then the drill down effect is
> >> not supported.
> >>
> >> Please see the following topic in Books On-Line for Reporting Services:
> >> Drill Down Reports and Hidden Items.
> >> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/h
> >> tm/rcr_creating_interactive_v1_8bg4.asp>
> >>
> >> Based on my test, Toggleitem feature seems to work for Matrix after
> >> exporting to Excel. However, it does not work properly for table after
> >> exporting to Excel. I think it needs to contain all of the data and then
> >> provide the ability to toggle but apprarently these data is not included
> >> in
> >> this situation.
> >>
> >> Best Regards,
> >>
> >> Peter Yang
> >> MCSE2000/2003, MCSA, MCDBA
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >>
> >> =====================================================> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
> >>
> >> --
> >> | Thread-Topic: Excel export drill-down problem (sp2)
> >> | thread-index: AcWMm3svM+OGxhhzSJqE5HRfXsUjew==> >> | X-WBNR-Posting-Host: 156.153.255.243
> >> | From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
> >> | Subject: Excel export drill-down problem (sp2)
> >> | Date: Tue, 19 Jul 2005 12:53:06 -0700
> >> | Lines: 15
> >> | Message-ID: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
> >> | MIME-Version: 1.0
> >> | Content-Type: text/plain;
> >> | charset="Utf-8"
> >> | Content-Transfer-Encoding: 7bit
> >> | X-Newsreader: Microsoft CDO for Windows 2000
> >> | Content-Class: urn:content-classes:message
> >> | Importance: normal
> >> | Priority: normal
> >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> >> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >> | Xref: TK2MSFTNGXA01.phx.gbl
> >> microsoft.public.sqlserver.reportingsvcs:48405
> >> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> >> |
> >> | Hi all,
> >> |
> >> | I have a relatively straight forward report with a Table and a grouping
> >> on
> >> | the details group. I have the the ToggleItem property of the details
> >> group
> >> | set to the first cell of the detail row, and it renders as expected in
> >> HTML.
> >> | (An expand/collapse graphic appears to the left of the cell and
> >> dynamically
> >> | shows/hides the child rows when clicked.) However, when I export to
> >> Excel
> >> | the groupings are lost and I see only the root rows and none of the
> >> children.
> >> | From my understanding the export should render the child rows using
> >> the
> >> | Outline feature of Excel, but for the life of me I can't get this to
> >> work.
> >> |
> >> | Any suggestions on what I'm doing wrong here?
> >> |
> >> | Thanks
> >> | Bill
> >> |
> >>
> >>
>
>|||Hello Bill,
Based on my further test, I found if I use the following method, I could
export the Excel as expected:
1. In Layout view, click the table so that column and row handles appear
above and next to the table or matrix.
2. Right-click the corner handle of the table or matrix, and then click
Properties.
3. On the Groups tab, select the group to edit, and then click Edit.
4. On the Visibility tab, do the following:
5. For Initial visibility, select Hidden.
6. Select Visibility can be toggled by another report item.
7. In Report item, type or select the name of the text box that users click
to show the selected item. I selected Textbox1.
Note: The value for Report item must be the name of a text box that is
either in the same group as the item that is being hidden or in another
group or item in the same container hierarchy (up to and including the
report body).
By using the following method, I reproduced the issue you encountered:
1. In Layout view, click the table so that column and row handles appear
above and next to the table or matrix.
2. Right-click the detail handle of the table or matrix, and then click
Edit Group
3. On the Visibility tab, do the following:
4. For Initial visibility, select Hidden.
5. Select Visibility can be toggled by another report item.
6. In Report item, type or select the name of the text box that users click
to show the selected item. I selected Textbox1.
Though in HTML rendering, above methods have the same behavior, they are
different in Excel rendering.
By checking the RDL code, I found the differenences between them.
1. For the 1st method, the <visibility> entry is included in <Tablegroup>
item. The data of the table is included in this report even the report is
collapsed.
2. For the 2nd method, the <visibility> entry is in <details> item, the
data that is necessary to toggle is not actaully included in this
situation.
Hope this information is helpful.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Excel export drill-down problem (sp2)
| thread-index: AcWRO/qqj6RePYO2SkSf0MlULM92LQ==| X-WBNR-Posting-Host: 161.114.64.75
| From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
| References: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
<V8UcOnQjFHA.3120@.TK2MSFTNGXA01.phx.gbl>
<E2AA4A4B-09D6-400C-AF2A-657366E71870@.microsoft.com>
<OK###2TjFHA.3568@.tk2msftngp13.phx.gbl>
| Subject: Re: Excel export drill-down problem (sp2)
| Date: Mon, 25 Jul 2005 10:12:04 -0700
| Lines: 154
| Message-ID: <3C668BC0-EF6A-4453-AC4E-FB10A1EE5F68@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:48828
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi Bruce,
|
| Thanks for the confirmation that the Excel outling feature does work with
| just a straight table. Somebody on my team was able to get it to work as
| well, however we still can't get it working with my particular report.
What
| I suspect might be making the difference is that my report has both the
| grouping and toggleItem set on the same row. The table has only two rows
in
| it: the header row and the details row, and the first column in the
detail
| row is the toggle for the whole row. This renders fine in HTML but
doesn't
| export to Excel correctly.
|
| Anybody have any ideas on how to get this to work? I've opened a trouble
| ticket with Microsoft but so far that hasn't solved the issue. Any
| alternatives/workarounds would be much appreciated.
|
| Thanks
| Bill
|
| "Bruce L-C [MVP]" wrote:
|
| > I have table (not Matrix) where it does use the outlining feature. I
created
| > a drill down report and everything worked as advertised in Excel. I'm a
loss
| > at why it isn't for you but it did for me. I only have a single level
of
| > drill down and haven't tried multiple levels (just trying to think what
| > might be different for you). Create a simple report with a single
drilldown,
| > does it work or not for you?
| >
| > Anyway, just thought I would let you know that it does work with tables.
| >
| >
| > --
| > Bruce Loehle-Conger
| > MVP SQL Server Reporting Services
| >
| >
| > "Bill Mers" <billmers@.newsgroup.nospam> wrote in message
| > news:E2AA4A4B-09D6-400C-AF2A-657366E71870@.microsoft.com...
| > > Hi Peter,
| > >
| > > I've seen quite a few posts on this newsgroup from other people who
have
| > > gotten the "Outlining" feature of Excel to work when rendering from
| > > Reporting
| > > Services. How is this done?
| > >
| > > If it's only supported through a Matrix report that's ok, however I
was
| > > unable to get that working either. I saw the same behavior as
before,
| > > where
| > > the Matrix renders with expand/collapse columns in HTML and they
disappear
| > > in
| > > Excel. If I have to write an Excel-specific version of the report
that's
| > > ok,
| > > but I can't get the Outlining feature to work at all.
| > >
| > > I have a hard (non-negotiable) requirement to get this feature
working, so
| > > any help would be greatly appreciated!
| > >
| > > Thanks
| > >
| > > "Peter Yang [MSFT]" wrote:
| > >
| > >> Hello Bill,
| > >>
| > >> If you have drill down reports then you could use show-and-hide
toggle
| > >> <different from hidden items> and if rendering is done in HTML then
it
| > >> will
| > >> show collapsed items so that users can click on the toggle to view
hidden
| > >> groups.
| > >>
| > >> Using other rendering extension like Excel then the drill down
effect is
| > >> not supported.
| > >>
| > >> Please see the following topic in Books On-Line for Reporting
Services:
| > >> Drill Down Reports and Hidden Items.
| > >>
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/h
| > >> tm/rcr_creating_interactive_v1_8bg4.asp>
| > >>
| > >> Based on my test, Toggleitem feature seems to work for Matrix after
| > >> exporting to Excel. However, it does not work properly for table
after
| > >> exporting to Excel. I think it needs to contain all of the data and
then
| > >> provide the ability to toggle but apprarently these data is not
included
| > >> in
| > >> this situation.
| > >>
| > >> Best Regards,
| > >>
| > >> Peter Yang
| > >> MCSE2000/2003, MCSA, MCDBA
| > >> Microsoft Online Partner Support
| > >>
| > >> When responding to posts, please "Reply to Group" via your
newsreader so
| > >> that others may learn and benefit from your issue.
| > >>
| > >> =====================================================| > >>
| > >> This posting is provided "AS IS" with no warranties, and confers no
| > >> rights.
| > >>
| > >>
| > >>
| > >>
| > >> --
| > >> | Thread-Topic: Excel export drill-down problem (sp2)
| > >> | thread-index: AcWMm3svM+OGxhhzSJqE5HRfXsUjew==| > >> | X-WBNR-Posting-Host: 156.153.255.243
| > >> | From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
| > >> | Subject: Excel export drill-down problem (sp2)
| > >> | Date: Tue, 19 Jul 2005 12:53:06 -0700
| > >> | Lines: 15
| > >> | Message-ID: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
| > >> | MIME-Version: 1.0
| > >> | Content-Type: text/plain;
| > >> | charset="Utf-8"
| > >> | Content-Transfer-Encoding: 7bit
| > >> | X-Newsreader: Microsoft CDO for Windows 2000
| > >> | Content-Class: urn:content-classes:message
| > >> | Importance: normal
| > >> | Priority: normal
| > >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > >> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > >> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > >> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > >> | Xref: TK2MSFTNGXA01.phx.gbl
| > >> microsoft.public.sqlserver.reportingsvcs:48405
| > >> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > >> |
| > >> | Hi all,
| > >> |
| > >> | I have a relatively straight forward report with a Table and a
grouping
| > >> on
| > >> | the details group. I have the the ToggleItem property of the
details
| > >> group
| > >> | set to the first cell of the detail row, and it renders as
expected in
| > >> HTML.
| > >> | (An expand/collapse graphic appears to the left of the cell and
| > >> dynamically
| > >> | shows/hides the child rows when clicked.) However, when I export
to
| > >> Excel
| > >> | the groupings are lost and I see only the root rows and none of the
| > >> children.
| > >> | From my understanding the export should render the child rows
using
| > >> the
| > >> | Outline feature of Excel, but for the life of me I can't get this
to
| > >> work.
| > >> |
| > >> | Any suggestions on what I'm doing wrong here?
| > >> |
| > >> | Thanks
| > >> | Bill
| > >> |
| > >>
| > >>
| >
| >
| >
||||Did anyone resolve this? I have rendered a report into Excel but only one
user out of 3 (includes me) gets the Excel doc with expand/collapse auto
format. I can't figure out why only she gets the correct format. Any ideas as
to what I need specifiically to check for in Excel?
There are 8 groups with details below each. When she gets report in Excel,
she sees outline symbols (plus , minus, etc.) and it looks great. When I get
the report in Excel, it shows all info but not in outline format. HELP!
thanks
"Peter Yang [MSFT]" wrote:
> Hello Bill,
> Based on my further test, I found if I use the following method, I could
> export the Excel as expected:
> 1. In Layout view, click the table so that column and row handles appear
> above and next to the table or matrix.
> 2. Right-click the corner handle of the table or matrix, and then click
> Properties.
> 3. On the Groups tab, select the group to edit, and then click Edit.
> 4. On the Visibility tab, do the following:
> 5. For Initial visibility, select Hidden.
> 6. Select Visibility can be toggled by another report item.
> 7. In Report item, type or select the name of the text box that users click
> to show the selected item. I selected Textbox1.
> Note: The value for Report item must be the name of a text box that is
> either in the same group as the item that is being hidden or in another
> group or item in the same container hierarchy (up to and including the
> report body).
> By using the following method, I reproduced the issue you encountered:
> 1. In Layout view, click the table so that column and row handles appear
> above and next to the table or matrix.
> 2. Right-click the detail handle of the table or matrix, and then click
> Edit Group
> 3. On the Visibility tab, do the following:
> 4. For Initial visibility, select Hidden.
> 5. Select Visibility can be toggled by another report item.
> 6. In Report item, type or select the name of the text box that users click
> to show the selected item. I selected Textbox1.
> Though in HTML rendering, above methods have the same behavior, they are
> different in Excel rendering.
> By checking the RDL code, I found the differenences between them.
> 1. For the 1st method, the <visibility> entry is included in <Tablegroup>
> item. The data of the table is included in this report even the report is
> collapsed.
> 2. For the 2nd method, the <visibility> entry is in <details> item, the
> data that is necessary to toggle is not actaully included in this
> situation.
> Hope this information is helpful.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | Thread-Topic: Excel export drill-down problem (sp2)
> | thread-index: AcWRO/qqj6RePYO2SkSf0MlULM92LQ==> | X-WBNR-Posting-Host: 161.114.64.75
> | From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
> | References: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
> <V8UcOnQjFHA.3120@.TK2MSFTNGXA01.phx.gbl>
> <E2AA4A4B-09D6-400C-AF2A-657366E71870@.microsoft.com>
> <OK###2TjFHA.3568@.tk2msftngp13.phx.gbl>
> | Subject: Re: Excel export drill-down problem (sp2)
> | Date: Mon, 25 Jul 2005 10:12:04 -0700
> | Lines: 154
> | Message-ID: <3C668BC0-EF6A-4453-AC4E-FB10A1EE5F68@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:48828
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi Bruce,
> |
> | Thanks for the confirmation that the Excel outling feature does work with
> | just a straight table. Somebody on my team was able to get it to work as
> | well, however we still can't get it working with my particular report.
> What
> | I suspect might be making the difference is that my report has both the
> | grouping and toggleItem set on the same row. The table has only two rows
> in
> | it: the header row and the details row, and the first column in the
> detail
> | row is the toggle for the whole row. This renders fine in HTML but
> doesn't
> | export to Excel correctly.
> |
> | Anybody have any ideas on how to get this to work? I've opened a trouble
> | ticket with Microsoft but so far that hasn't solved the issue. Any
> | alternatives/workarounds would be much appreciated.
> |
> | Thanks
> | Bill
> |
> | "Bruce L-C [MVP]" wrote:
> |
> | > I have table (not Matrix) where it does use the outlining feature. I
> created
> | > a drill down report and everything worked as advertised in Excel. I'm a
> loss
> | > at why it isn't for you but it did for me. I only have a single level
> of
> | > drill down and haven't tried multiple levels (just trying to think what
> | > might be different for you). Create a simple report with a single
> drilldown,
> | > does it work or not for you?
> | >
> | > Anyway, just thought I would let you know that it does work with tables.
> | >
> | >
> | > --
> | > Bruce Loehle-Conger
> | > MVP SQL Server Reporting Services
> | >
> | >
> | > "Bill Mers" <billmers@.newsgroup.nospam> wrote in message
> | > news:E2AA4A4B-09D6-400C-AF2A-657366E71870@.microsoft.com...
> | > > Hi Peter,
> | > >
> | > > I've seen quite a few posts on this newsgroup from other people who
> have
> | > > gotten the "Outlining" feature of Excel to work when rendering from
> | > > Reporting
> | > > Services. How is this done?
> | > >
> | > > If it's only supported through a Matrix report that's ok, however I
> was
> | > > unable to get that working either. I saw the same behavior as
> before,
> | > > where
> | > > the Matrix renders with expand/collapse columns in HTML and they
> disappear
> | > > in
> | > > Excel. If I have to write an Excel-specific version of the report
> that's
> | > > ok,
> | > > but I can't get the Outlining feature to work at all.
> | > >
> | > > I have a hard (non-negotiable) requirement to get this feature
> working, so
> | > > any help would be greatly appreciated!
> | > >
> | > > Thanks
> | > >
> | > > "Peter Yang [MSFT]" wrote:
> | > >
> | > >> Hello Bill,
> | > >>
> | > >> If you have drill down reports then you could use show-and-hide
> toggle
> | > >> <different from hidden items> and if rendering is done in HTML then
> it
> | > >> will
> | > >> show collapsed items so that users can click on the toggle to view
> hidden
> | > >> groups.
> | > >>
> | > >> Using other rendering extension like Excel then the drill down
> effect is
> | > >> not supported.
> | > >>
> | > >> Please see the following topic in Books On-Line for Reporting
> Services:
> | > >> Drill Down Reports and Hidden Items.
> | > >>
> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/h
> | > >> tm/rcr_creating_interactive_v1_8bg4.asp>
> | > >>
> | > >> Based on my test, Toggleitem feature seems to work for Matrix after
> | > >> exporting to Excel. However, it does not work properly for table
> after
> | > >> exporting to Excel. I think it needs to contain all of the data and
> then
> | > >> provide the ability to toggle but apprarently these data is not
> included
> | > >> in
> | > >> this situation.
> | > >>
> | > >> Best Regards,
> | > >>
> | > >> Peter Yang
> | > >> MCSE2000/2003, MCSA, MCDBA
> | > >> Microsoft Online Partner Support
> | > >>
> | > >> When responding to posts, please "Reply to Group" via your
> newsreader so
> | > >> that others may learn and benefit from your issue.
> | > >>
> | > >> =====================================================> | > >>
> | > >> This posting is provided "AS IS" with no warranties, and confers no
> | > >> rights.
> | > >>
> | > >>
> | > >>
> | > >>
> | > >> --
> | > >> | Thread-Topic: Excel export drill-down problem (sp2)
> | > >> | thread-index: AcWMm3svM+OGxhhzSJqE5HRfXsUjew==> | > >> | X-WBNR-Posting-Host: 156.153.255.243
> | > >> | From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
> | > >> | Subject: Excel export drill-down problem (sp2)
> | > >> | Date: Tue, 19 Jul 2005 12:53:06 -0700
> | > >> | Lines: 15
> | > >> | Message-ID: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
> | > >> | MIME-Version: 1.0
> | > >> | Content-Type: text/plain;
> | > >> | charset="Utf-8"
> | > >> | Content-Transfer-Encoding: 7bit
> | > >> | X-Newsreader: Microsoft CDO for Windows 2000
> | > >> | Content-Class: urn:content-classes:message
> | > >> | Importance: normal
> | > >> | Priority: normal
> | > >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > >> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > >> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > >> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > >> | Xref: TK2MSFTNGXA01.phx.gbl
> | > >> microsoft.public.sqlserver.reportingsvcs:48405
> | > >> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > >> |
> | > >> | Hi all,
> | > >> |
> | > >> | I have a relatively straight forward report with a Table and a
> grouping
> | > >> on
> | > >> | the details group. I have the the ToggleItem property of the
> details
> | > >> group
> | > >> | set to the first cell of the detail row, and it renders as
> expected in
> | > >> HTML.
> | > >> | (An expand/collapse graphic appears to the left of the cell and
> | > >> dynamically
> | > >> | shows/hides the child rows when clicked.) However, when I export
> to
> | > >> Excel
> | > >> | the groupings are lost and I see only the root rows and none of the
> | > >> children.
> | > >> | From my understanding the export should render the child rows
> using
> | > >> the
> | > >> | Outline feature of Excel, but for the life of me I can't get this
> to
> | > >> work.
> | > >> |
> | > >> | Any suggestions on what I'm doing wrong here?
> | > >> |
> | > >> | Thanks
> | > >> | Bill
> | > >> |
> | > >>
> | > >>
> | >
> | >
> | >
> |
>|||I have a table with one group ('table1_state') and a detail part.
What i made now is a dummy group ('table1_dummy') only for handling the
visibility. This dummy group is a child of the table1_state group and
has no footer and no header. Its visibility is set to hidden and the
toggle item is set to the first textbox of table1_state group (in my
case 'txtState').
Check that no other visibility configurations are set.
<TableGroup>
<Grouping Name="table1_dummy">
<GroupExpressions>
<GroupExpression>=Fields!PrimaryKey.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>txtState</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableGroup>
</TableGroups>|||Bill,
I have the same problem. What is even stranger is that there is an example
sql report that comes with Northwind called "Northwind Simple report", which
also has a single collapible row. This report will export to excel with the
children data while my report with similar settings looses this data.
I have found no reason for this.
Have you had any luck with working out a solution ?

Excel consuming 100% CPU

Hi,
We are using RS Sp2 andExcel XP on the clients.
One report outputs 5000+ rows and are grouped like this:
account group (25 groups)
account (250 groups)
values (4725 rows)
When exported to Excel the XL-file is 11Mb+ !
All groups are collapsed in XL by default, and when trying to work in the
sheet XL consumes all CPU! If all groups are expanded it is possible to work
with the data and there is no unusual CPU consuming.
If collapsing a group again then the CPU consumtion becomes heavy again.
My question, is this a limitation in XL? If it is, does this work better in
XL 2003?
Is there anything we can do to boost performance, as it is now we cant work
in XL...
Any ideas?
/FredrikAt this point this is just an Excel file. It looks like Excel is not
handling that amount of data and that amount of groups very well. You could
ask in Excel newsgroup if XL 2003 might work better for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:64659432-7804-4443-83D6-454F665C650A@.microsoft.com...
> Hi,
> We are using RS Sp2 andExcel XP on the clients.
> One report outputs 5000+ rows and are grouped like this:
> account group (25 groups)
> account (250 groups)
> values (4725 rows)
> When exported to Excel the XL-file is 11Mb+ !
> All groups are collapsed in XL by default, and when trying to work in the
> sheet XL consumes all CPU! If all groups are expanded it is possible to
> work
> with the data and there is no unusual CPU consuming.
> If collapsing a group again then the CPU consumtion becomes heavy again.
> My question, is this a limitation in XL? If it is, does this work better
> in
> XL 2003?
> Is there anything we can do to boost performance, as it is now we cant
> work
> in XL...
> Any ideas?
> /Fredrik
>|||Tested 2003 but the same problem occured. I would be very happy to now what
the heck is going on in Excel here...
"Bruce L-C [MVP]" wrote:
> At this point this is just an Excel file. It looks like Excel is not
> handling that amount of data and that amount of groups very well. You could
> ask in Excel newsgroup if XL 2003 might work better for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
> news:64659432-7804-4443-83D6-454F665C650A@.microsoft.com...
> > Hi,
> > We are using RS Sp2 andExcel XP on the clients.
> > One report outputs 5000+ rows and are grouped like this:
> >
> > account group (25 groups)
> > account (250 groups)
> > values (4725 rows)
> >
> > When exported to Excel the XL-file is 11Mb+ !
> > All groups are collapsed in XL by default, and when trying to work in the
> > sheet XL consumes all CPU! If all groups are expanded it is possible to
> > work
> > with the data and there is no unusual CPU consuming.
> > If collapsing a group again then the CPU consumtion becomes heavy again.
> >
> > My question, is this a limitation in XL? If it is, does this work better
> > in
> > XL 2003?
> > Is there anything we can do to boost performance, as it is now we cant
> > work
> > in XL...
> >
> > Any ideas?
> >
> > /Fredrik
> >
>
>|||The problem may be merged cells or show/hide regions. Excel reports with
thousands of these elements are slow to load because for each new cell they
need to check to see if it intersects with all of the other merged cells.
If you can, removing some of the show/hide elements or other obvious merged
cells and you should see a speed increase in Excel.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:3F5CB660-BFB5-4712-A30C-A0081A888A29@.microsoft.com...
> Tested 2003 but the same problem occured. I would be very happy to now
> what
> the heck is going on in Excel here...
> "Bruce L-C [MVP]" wrote:
>> At this point this is just an Excel file. It looks like Excel is not
>> handling that amount of data and that amount of groups very well. You
>> could
>> ask in Excel newsgroup if XL 2003 might work better for you.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
>> news:64659432-7804-4443-83D6-454F665C650A@.microsoft.com...
>> > Hi,
>> > We are using RS Sp2 andExcel XP on the clients.
>> > One report outputs 5000+ rows and are grouped like this:
>> >
>> > account group (25 groups)
>> > account (250 groups)
>> > values (4725 rows)
>> >
>> > When exported to Excel the XL-file is 11Mb+ !
>> > All groups are collapsed in XL by default, and when trying to work in
>> > the
>> > sheet XL consumes all CPU! If all groups are expanded it is possible to
>> > work
>> > with the data and there is no unusual CPU consuming.
>> > If collapsing a group again then the CPU consumtion becomes heavy
>> > again.
>> >
>> > My question, is this a limitation in XL? If it is, does this work
>> > better
>> > in
>> > XL 2003?
>> > Is there anything we can do to boost performance, as it is now we cant
>> > work
>> > in XL...
>> >
>> > Any ideas?
>> >
>> > /Fredrik
>> >
>>

Excel 2007 Documents Not Displayed in Windows XP Professional sp2 Start Menu Recent Documents Li

---

Excel 2007 Documents Not Displayed in Windows XP Professional sp2

Start Menu's Recent Documents List:

---

Dear Microsoft Support:

I can't figure out how to get recently used Excel 2007 (new file formats) documents to show up in the Windows XP Professional (sp2) Start Menu's Recent Documents List.I checked the Internet, the knowledgebase, many parts of the MS web site, etc. for an answer but can't find one.

Are Excel 2007 documents supposed to show up in the XP Start Menu's Recent Document List?Is this a bug or do I have to do some sort of configuration to make it do so? If it's a bug, when will a fix be available?

Are these new Excel 2007 files filtered out like EXE files are which will not appear in the Recent Document List?

The Excel 2007 file types are listed in the Registry.

This question is also posted on Experts-Exchange...No solutions yet.

Thanks.

Jim.

This forum is nothing to do with Excel. Nothing to do with Windows XP. And it isn't a route to Microsoft Support.

Perhaps you should try elsewhere.

Excel 2007 Documents Not Displayed in Windows XP Professional sp2 Start Menu Recent Document

---

Excel 2007 Documents Not Displayed in Windows XP Professional sp2

Start Menu's Recent Documents List:

---

Dear Microsoft Support:

I can't figure out how to get recently used Excel 2007 (new file formats) documents to show up in the Windows XP Professional (sp2) Start Menu's Recent Documents List.I checked the Internet, the knowledgebase, many parts of the MS web site, etc. for an answer but can't find one.

Are Excel 2007 documents supposed to show up in the XP Start Menu's Recent Document List?Is this a bug or do I have to do some sort of configuration to make it do so? If it's a bug, when will a fix be available?

Are these new Excel 2007 files filtered out like EXE files are which will not appear in the Recent Document List?

The Excel 2007 file types are listed in the Registry.

This question is also posted on Experts-Exchange...No solutions yet.

Thanks.

Jim.

This forum is nothing to do with Excel. Nothing to do with Windows XP. And it isn't a route to Microsoft Support.

Perhaps you should try elsewhere.

Excel 2007 and SqlServer sp2

hellow all,

i installed SqlServer 2005 sp2 on my server.

now, how do i import excel 2007 files in SSIS? i don't have office 2007 installed on my computer, but i've got some .xslx that i would like to try ro import. i tried to create an OLEDB connection, but i don't know which provider to choose.

anyway, can i do it without the sp2 or office 2007 installed on my server?

thanks for tour reply!

Data Sources/Microsoft Office (2007) - ssis
(http://ssis.wik.is/Data_Sources/Microsoft_Office_(2007))|||

thanks, the link is very helpfull.

i downloaded the example package but when i try to see the connection manager's configuration- i'm getting this massage:

"The specified provider is not supported. Please choose different provider in connection manager."

it is probably because i don't have office 2007 installed on my computer.

is there a way to get only the ACE provider without installing office 2007?

|||BTW, why is my thread icon grey? (while everybody else's is green or red?)|||That is a way to tell you which threads you have already visited (open)

Excel 2007 AcquireConnection error

I'm creating a small test package that copies a value from an Excel 2007 worksheet into a SQL 2005 database (SP2). When I do an Execute Task, I get the following error:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager \\lorip\MandEScorecards\SQLQueryExample.xlsx failed with error code 0xC0202009.

However, if I do a "Preview" in the Editor for this same Excel Source task that fails, the data comes up as I would expect. What am I missing?

Thanks in advance!

This error code is sometimes related to security issues. How are you running the package?|||

Is this a 64-Bit Server?

If is, there is an Article that show that SQL Server 2005 in 64-Bits don't give support to any OLE DB Transactions especially Excel.

sql

Excel 2007 AcquireConnection error

I'm creating a small test package that copies a value from an Excel 2007 worksheet into a SQL 2005 database (SP2). When I do an Execute Task, I get the following error:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager \\lorip\MandEScorecards\SQLQueryExample.xlsx failed with error code 0xC0202009.

However, if I do a "Preview" in the Editor for this same Excel Source task that fails, the data comes up as I would expect. What am I missing?

Thanks in advance!

This error code is sometimes related to security issues. How are you running the package?|||

Is this a 64-Bit Server?

If is, there is an Article that show that SQL Server 2005 in 64-Bits don't give support to any OLE DB Transactions especially Excel.

Excel 2002/2003 Add-in for SQL Server Analysis

Is "Excel 2002/2003 Add-in for SQL Server Analysis
Services" supported under Win XP SP2 ?
When I try to install it I get "The advertised application
will not be installed because it is unsafe. Contact your
administator to change the installation user interface
option of the package to basec".
TIA
Stephen
Try this server
privatenews.microsoft.com
and this newsgroup
microsoft.private.offsolaccelerators
...but I have XP and no problems - but perhaps there are different XP
versions - think I have professional.
Other than that it could sound like the security settings on your machine
that blocks it.
--Michael
"Stephen" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:199801c4abf8$f580baf0$a601280a@.phx.gbl...
> Is "Excel 2002/2003 Add-in for SQL Server Analysis
> Services" supported under Win XP SP2 ?
> When I try to install it I get "The advertised application
> will not be installed because it is unsafe. Contact your
> administator to change the installation user interface
> option of the package to basec".
> TIA
> Stephen
>

Everything slower with SP2?

I installed SP2 two days ago and it seems like my SSIS-packes now take longer time than before - the very opposite of what I was hoping for.

Anyway, here are some data from runs on our performance environment. No new data is added to the source database between the runs, but I do a full process of the cubes every time (time is in seconds):

Package...............................SP1...............................SP2

Load dimensions..................200.................................270

Load fact data.......................800...............................1600

Process cubes....................2100...............................2600

So, as you can see, everything is going slower with SP2. I have yet to look into if there are any specific steps in the packages that take longer time than before, but it's odd that all packages take a longer time to execute. Especially that cube processing is slower suprises me.

Has anyone experienced something similar? Thanks!

Are you using SSIS Logging? SP2 added some diagnostics messages for logging database connection and command information. If this causes performance issues, you may want to unselect logging information messages.

|||It's interesting that processing the cubes slows down. That would indicate to me that it might be the relational engine is running slower. Tough to imagine how any potential change in SSIS could impact that process. Are you able to separate processing time from package run time, to rule out a long validation phase or something?
|||

I did discover that there is a lot more logging going on in SP2 - at least for the User: Diagnostic, as described in "What's New in SQL Server 2005 SP2":

http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/WhatsNewSQL2005SP2.htm

"You can now troubleshoot a package's interaction with external data sources by enabling logging and selecting the package's Diagnostic event for logging. Many package failures occur during interaction with external data providers. However, those providers often do not return messages to Integration Services that provide enough information to begin troubleshooting. The Integration Services components listed below have now been instrumented to write a message to the log before and after every call to an external data provider. This message includes the name of the method being called; for example, the Open method of an OLE DB Connection object, or the ExecuteNonQuery method of a Command object. You can view these messages by enabling logging and selecting the package's Diagnostic event."

Since I am currently trying to improve the performance of our ETL I'm currenlty logging OnError, OnWarning, OnPreExecute, OnPostExecute, OnPipelineRowsSent and Diagnostic to the sysdtslog90 table. Apart from Diagnostic, the amount of events logged are roughly the same (# events logged):

Package...............................SP1...............................SP2...............................SP2 (without User: Diagnostic)

Load dimensions..................455............................26161...................................455

Load fact data.....................1581............................18527.................................1607

Process cubes........................41.................................137.....................................41

I have run the packages without any logging at all for SP2, and that only shortened the total execution time with a couple of seconds (less than one minute for all three).

I'll look into if there are any certain steps that now runs a lot slower than with SP1 and get back during the day. Thanks all!

|||

Ok, here are some more detailed info about the steps that differ between SP1 and SP2 (duration in seconds if not specified):

Load dimensions: http://happy.lanmust.org/SP1vsSP2/dimensions.Png

Load facts: http://happy.lanmust.org/SP1vsSP2/facts.Png

Cube processing: http://happy.lanmust.org/SP1vsSP2/cubes.Png

At least there are a couple of steps that are faster with SP2

tsk_c_s means data is loaded from an oracle db to sql server,

tsk_s_d means data is loaded from sql server to sql server (from staging to datamart).

Connection to Oracle uses the Oracle provider for Oracle (OraOLEDB.Oracle.1). The rest is SQL Native Client (SQLNCLI.1).

OT: Is it possible to add images in the forum post instead of linking? I tried using html mode and <img..> images but ends up blank.

|||

1) I was just thinking, are there any settings that defaults when you install SP2?

2) Is there anything you have to think about before installing SP2 (I reckon there was this blog post about it, but now when I need it I can't find it).

Also, installed build is 3042. Now there are some fixes: http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx I'll apply these and see if things work out better.

|||Nopes, going to build 3054 didn't help. Going back to SP1 on Monday for now.|||Try asking in Analysis Services forum - most likely the performance regression is in OLAP server; it is unlikely the SSIS is the bottleneck here. E.g. in case of Cube processing, SSIS just send the XMLA command to the OLAP server, which does the actual work.|||

Ok, I'll do that. Thanks!

By the way, version of SQL Server is Standard.

|||

Good news. Now SP2 seem to be on par with SP1. I uninstalled SQL Server, re-installed it and added SP1. Did run the packages again, and was fine (normal performance). Couldn't help to try installing SP2 again to see if there still was this performance difference - and what do you know - this time it performed as well as SP1.

I dunno why it worked better this time, but this time I did not install the hotfixes for SP1 before installing SP2 (cannot evaluate variables http://support.microsoft.com/kb/918091, http://support.microsoft.com/kb/918222/). In addition, this time I installed the SP2 built in March and not the very first release (Feb). Might also have to do with tempdb and/or transaction log, cause before I had run the ETL packages 20+ times instead of 5.

Anyway, here's the new comparison: http://happy.lanmust.org/SP1vsSP2/all.Png

Events keep bubbling up, even with Propagate=False

I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going.
I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also.
Any idea what I'm doing wrong? Or is this a bug? Is there a work around?
Any help would be greatly appreciated.

On which eventhandler are you setting System::Propogate=FALSE? The one scoped to the FTP task?

Which container is the other eventhandler scoped to?

If you want your package to continue in the evnt of failure then this is configured within the propreties of the package. it is nothing to do with the configuration of your eventhandlers. I think you will find SSIS to be very robust in this regard when you get it working as you require.

-Jamie

|||

Jamie,

Thanks for the reply. The package has a Main task with a For Each Loop that goes through all files in a directory. It then calls an execute package called ETL-Archive. Within ETL-Archive, it calls execute package ETL. ETL is where the real meat and potatoes is at. This is where the FTP task is. The event handler OnError is scoped to the failing FTP task. This is where I set Propagate=False. However, the event keeps bubbling up to ETL, then to ETL-Archive and then to Main, resulting in complete package failure. Even when I have an OnError event handler at the ETL scope where I again set Propagate = False. I'm not sure I understood your comment about the fact that this is configured within the properties of the package? In this case do you mean ETL or Main? And what should be configured?

Thanks again!

|||

Also, the FTP task throws two errors because I deliberately configured it wrongly:

Error: 0xC0029190 at FTP Handshake back, FTP Task: Variable "FTPRemotePathGC" doesn't start with "/".

Error: 0xC0024107 at FTP Handshake back: There were errors during task validation.

Warning: 0x80019002 at ETL: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure.

This in turn will fail ETL, which will fail ETL-Archive, which will fail Main...

|||

Hi Jamie,

I just spent 2.5 hours on the phone with Microsoft support, burning up one of my 4 free support tickets that come with MSDN. They confirmed that it is actually a bug. (So I didn't loose one of my 4 support tickets). Apparently some task containers keep propagating Error events up, even though the Propagate system var is set to False. So... the good thing is I know I'm not loosing my mind. The bad thing is that there is no workaround and no fix planned until Katmay. Apparently this bug was fixed in SQL 2005 Beta 3 and reintroduced later. I just installed Build 2153 (http://support.microsoft.com/?id=918222), and the bug is still there.

I'm not sure where to proceed from here. I have an FTP task that I cannot protect with an event handler, so every time my package has to send a little handshake back and the FTP task fails, my whole package bombs. Again... no clue where to proceed from here. How can I ever release such an non-robust package into production?

|||

Do we know if this has actually been submitted as a bug, registered with MS.

I ask because I am seeing the same issue with OnPostExecution EH not just the OnError EH you have described.

I checked on the MS Connect website and searched under the feedback but found no evidence of it ?

P R W.

|||

Well, after a lot of back-and-forth, Microsoft decided to update their documentation on BOL to make this a 'documented feature'. It was written off as 'works as designed'.

They did NOT register it as a bug (In the case I described in the thread, not sure about your case).

|||

"Works as designed"? Did they give a rationale for that?

-Jamie

|||

Have you found in BOL where it states this ?

To me it's a bug because Propagate False should mean no Event Bubbling for ALL Event Handlers not just a select few.

P R W.

|||

This is what I got back from Microsoft on the issue:

Bugs

We have filled 2 documentation bugs on this issue:

Doc: Error in Validate phase of Package Task Bubbles up to Next level event handler even if Propagate variable Set to False.

Doc: Propagate Property Set to False in Child Package Task Does Not Prevent Errors from bubbling up to Parent Package OnError event handler

|||

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

|||

Dirk DC wrote:

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

yeah I'm not surprised about that one. I can kind of understand why that is "as designed". It one of those where its as easy to argue for as it is against.

Dirk DC wrote:

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

So Propogate=FALSE doesn't stop events propogating to a parent package? Hmmm...not quite sure about that one. I don't know why it would be designed that way.

I'll have to check it out.

-Jamie

|||Have you tried setting DelayValidation=true on both the package(s), and DelayValiation=true on the FTP task as well?

I know this approach seems counter intuitive (setting DelayValidation more than once). However, DelayValidation at the container level appears to

have cascading run-time effects, and not cascading design time effects.

DelayValidation at the task level appears to have both design-time and

run-time effects.|||Not sure I've tried that. What are the run-time effects of DelayValidation?|||

The run-time effects of DelayValidation="true" are two fold, that I've observed.

1. When set, task validation is invoked 1 time, that's it. The validation occurs after the task's OnPreExecuteEvent has fired, no less.

Of course, if the task is in a loop container, validation is called one time per loop iteration. Conversely, when DelayValidation is false, validation will be invoked upfront (I've seen it called three times for a given task).

2. Cascading effect. DelayValidation cascades from container to contained tasks.

The "exception" to the delay cascade is that when Delay Validation is set to true on an Execute Package Task, that setting has no effect on the number of times Validation is called on the task in the child package. I say "exception", because actually, Execute Package Task is not a container (does not implement IDTSSequence, more precisely).

Events keep bubbling up, even with Propagate=False

I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going.
I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also.
Any idea what I'm doing wrong? Or is this a bug? Is there a work around?
Any help would be greatly appreciated.

On which eventhandler are you setting System::Propogate=FALSE? The one scoped to the FTP task?

Which container is the other eventhandler scoped to?

If you want your package to continue in the evnt of failure then this is configured within the propreties of the package. it is nothing to do with the configuration of your eventhandlers. I think you will find SSIS to be very robust in this regard when you get it working as you require.

-Jamie

|||

Jamie,

Thanks for the reply. The package has a Main task with a For Each Loop that goes through all files in a directory. It then calls an execute package called ETL-Archive. Within ETL-Archive, it calls execute package ETL. ETL is where the real meat and potatoes is at. This is where the FTP task is. The event handler OnError is scoped to the failing FTP task. This is where I set Propagate=False. However, the event keeps bubbling up to ETL, then to ETL-Archive and then to Main, resulting in complete package failure. Even when I have an OnError event handler at the ETL scope where I again set Propagate = False. I'm not sure I understood your comment about the fact that this is configured within the properties of the package? In this case do you mean ETL or Main? And what should be configured?

Thanks again!

|||

Also, the FTP task throws two errors because I deliberately configured it wrongly:

Error: 0xC0029190 at FTP Handshake back, FTP Task: Variable "FTPRemotePathGC" doesn't start with "/".

Error: 0xC0024107 at FTP Handshake back: There were errors during task validation.

Warning: 0x80019002 at ETL: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure.

This in turn will fail ETL, which will fail ETL-Archive, which will fail Main...

|||

Hi Jamie,

I just spent 2.5 hours on the phone with Microsoft support, burning up one of my 4 free support tickets that come with MSDN. They confirmed that it is actually a bug. (So I didn't loose one of my 4 support tickets). Apparently some task containers keep propagating Error events up, even though the Propagate system var is set to False. So... the good thing is I know I'm not loosing my mind. The bad thing is that there is no workaround and no fix planned until Katmay. Apparently this bug was fixed in SQL 2005 Beta 3 and reintroduced later. I just installed Build 2153 (http://support.microsoft.com/?id=918222), and the bug is still there.

I'm not sure where to proceed from here. I have an FTP task that I cannot protect with an event handler, so every time my package has to send a little handshake back and the FTP task fails, my whole package bombs. Again... no clue where to proceed from here. How can I ever release such an non-robust package into production?

|||

Do we know if this has actually been submitted as a bug, registered with MS.

I ask because I am seeing the same issue with OnPostExecution EH not just the OnError EH you have described.

I checked on the MS Connect website and searched under the feedback but found no evidence of it ?

P R W.

|||

Well, after a lot of back-and-forth, Microsoft decided to update their documentation on BOL to make this a 'documented feature'. It was written off as 'works as designed'.

They did NOT register it as a bug (In the case I described in the thread, not sure about your case).

|||

"Works as designed"? Did they give a rationale for that?

-Jamie

|||

Have you found in BOL where it states this ?

To me it's a bug because Propagate False should mean no Event Bubbling for ALL Event Handlers not just a select few.

P R W.

|||

This is what I got back from Microsoft on the issue:

Bugs

We have filled 2 documentation bugs on this issue:

Doc: Error in Validate phase of Package Task Bubbles up to Next level event handler even if Propagate variable Set to False.

Doc: Propagate Property Set to False in Child Package Task Does Not Prevent Errors from bubbling up to Parent Package OnError event handler

|||

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

|||

Dirk DC wrote:

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

yeah I'm not surprised about that one. I can kind of understand why that is "as designed". It one of those where its as easy to argue for as it is against.

Dirk DC wrote:

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

So Propogate=FALSE doesn't stop events propogating to a parent package? Hmmm...not quite sure about that one. I don't know why it would be designed that way.

I'll have to check it out.

-Jamie

|||Have you tried setting DelayValidation=true on both the package(s), and DelayValiation=true on the FTP task as well?

I know this approach seems counter intuitive (setting DelayValidation more than once). However, DelayValidation at the container level appears to

have cascading run-time effects, and not cascading design time effects.

DelayValidation at the task level appears to have both design-time and

run-time effects.|||Not sure I've tried that. What are the run-time effects of DelayValidation?|||

The run-time effects of DelayValidation="true" are two fold, that I've observed.

1. When set, task validation is invoked 1 time, that's it. The validation occurs after the task's OnPreExecuteEvent has fired, no less.

Of course, if the task is in a loop container, validation is called one time per loop iteration. Conversely, when DelayValidation is false, validation will be invoked upfront (I've seen it called three times for a given task).

2. Cascading effect. DelayValidation cascades from container to contained tasks.

The "exception" to the delay cascade is that when Delay Validation is set to true on an Execute Package Task, that setting has no effect on the number of times Validation is called on the task in the child package. I say "exception", because actually, Execute Package Task is not a container (does not implement IDTSSequence, more precisely).

Events keep bubbling up, even with Propagate=False

I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going.
I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also.
Any idea what I'm doing wrong? Or is this a bug? Is there a work around?
Any help would be greatly appreciated.

On which eventhandler are you setting System::Propogate=FALSE? The one scoped to the FTP task?

Which container is the other eventhandler scoped to?

If you want your package to continue in the evnt of failure then this is configured within the propreties of the package. it is nothing to do with the configuration of your eventhandlers. I think you will find SSIS to be very robust in this regard when you get it working as you require.

-Jamie

|||

Jamie,

Thanks for the reply. The package has a Main task with a For Each Loop that goes through all files in a directory. It then calls an execute package called ETL-Archive. Within ETL-Archive, it calls execute package ETL. ETL is where the real meat and potatoes is at. This is where the FTP task is. The event handler OnError is scoped to the failing FTP task. This is where I set Propagate=False. However, the event keeps bubbling up to ETL, then to ETL-Archive and then to Main, resulting in complete package failure. Even when I have an OnError event handler at the ETL scope where I again set Propagate = False. I'm not sure I understood your comment about the fact that this is configured within the properties of the package? In this case do you mean ETL or Main? And what should be configured?

Thanks again!

|||

Also, the FTP task throws two errors because I deliberately configured it wrongly:

Error: 0xC0029190 at FTP Handshake back, FTP Task: Variable "FTPRemotePathGC" doesn't start with "/".

Error: 0xC0024107 at FTP Handshake back: There were errors during task validation.

Warning: 0x80019002 at ETL: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure.

This in turn will fail ETL, which will fail ETL-Archive, which will fail Main...

|||

Hi Jamie,

I just spent 2.5 hours on the phone with Microsoft support, burning up one of my 4 free support tickets that come with MSDN. They confirmed that it is actually a bug. (So I didn't loose one of my 4 support tickets). Apparently some task containers keep propagating Error events up, even though the Propagate system var is set to False. So... the good thing is I know I'm not loosing my mind. The bad thing is that there is no workaround and no fix planned until Katmay. Apparently this bug was fixed in SQL 2005 Beta 3 and reintroduced later. I just installed Build 2153 (http://support.microsoft.com/?id=918222), and the bug is still there.

I'm not sure where to proceed from here. I have an FTP task that I cannot protect with an event handler, so every time my package has to send a little handshake back and the FTP task fails, my whole package bombs. Again... no clue where to proceed from here. How can I ever release such an non-robust package into production?

|||

Do we know if this has actually been submitted as a bug, registered with MS.

I ask because I am seeing the same issue with OnPostExecution EH not just the OnError EH you have described.

I checked on the MS Connect website and searched under the feedback but found no evidence of it ?

P R W.

|||

Well, after a lot of back-and-forth, Microsoft decided to update their documentation on BOL to make this a 'documented feature'. It was written off as 'works as designed'.

They did NOT register it as a bug (In the case I described in the thread, not sure about your case).

|||

"Works as designed"? Did they give a rationale for that?

-Jamie

|||

Have you found in BOL where it states this ?

To me it's a bug because Propagate False should mean no Event Bubbling for ALL Event Handlers not just a select few.

P R W.

|||

This is what I got back from Microsoft on the issue:

Bugs

We have filled 2 documentation bugs on this issue:

Doc: Error in Validate phase of Package Task Bubbles up to Next level event handler even if Propagate variable Set to False.

Doc: Propagate Property Set to False in Child Package Task Does Not Prevent Errors from bubbling up to Parent Package OnError event handler

|||

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

|||

Dirk DC wrote:

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

yeah I'm not surprised about that one. I can kind of understand why that is "as designed". It one of those where its as easy to argue for as it is against.

Dirk DC wrote:

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

So Propogate=FALSE doesn't stop events propogating to a parent package? Hmmm...not quite sure about that one. I don't know why it would be designed that way.

I'll have to check it out.

-Jamie

|||Have you tried setting DelayValidation=true on both the package(s), and DelayValiation=true on the FTP task as well?

I know this approach seems counter intuitive (setting DelayValidation more than once). However, DelayValidation at the container level appears to

have cascading run-time effects, and not cascading design time effects.

DelayValidation at the task level appears to have both design-time and

run-time effects.|||Not sure I've tried that. What are the run-time effects of DelayValidation?|||

The run-time effects of DelayValidation="true" are two fold, that I've observed.

1. When set, task validation is invoked 1 time, that's it. The validation occurs after the task's OnPreExecuteEvent has fired, no less.

Of course, if the task is in a loop container, validation is called one time per loop iteration. Conversely, when DelayValidation is false, validation will be invoked upfront (I've seen it called three times for a given task).

2. Cascading effect. DelayValidation cascades from container to contained tasks.

The "exception" to the delay cascade is that when Delay Validation is set to true on an Execute Package Task, that setting has no effect on the number of times Validation is called on the task in the child package. I say "exception", because actually, Execute Package Task is not a container (does not implement IDTSSequence, more precisely).