Excel Connection Manager expression editor - variable problem

Hi,

I have a data flow task within a foreach file loop. My problem occurs when I tried to make up an expression for the connection string of the Excel connection manager. Somehow I can see only system variables and none of the variables which I set up within the scope of the foreach file loop.

Can anyone provide me any insight into this mystery? As far as I can see, all the variables I created in the foreach file loop are still showing on the variable windows (without the Show All Variable button clicked).

Thank you very much and hope to hear from someone soon!

Regards,
Hsiao

The problem is the scope of your variables...move the variable at the package level so they are available to the connection manager.

Actually somewhere on this thread, there is an detailed explanation:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=770652&SiteID=1

Rafael Salas

|||

Thank you Rafael. You're right about the variable scope. Somehow when clicking on connection managers, the variable list does not update itself. This seems to indicate to me that all connection-related variables need to be set up at the package level, including those that are used at a subordinate levels, such as containers and tasks.

Hsiao

Excel and pdf not searcheable

I cannot find a simple string or number within ANY and EVERY Excel file and
the only pdf I have EVER exported. My colleague System Administrator has
tried DOS and Search... also to no avail...
I have tried DOS, Windows Explorer Search..., Copernic Desktop Search and
MSN Desktop Search (Beta - circa yesterday). NONE of these apps is able to
find a result.
Is this a known bug (couldn't find another topic with 'search' and Excel in
the subject)? A configuration problem? What?
(Many) sample files can be supplied...
RS SP1 on XP/Pro SP1 with all cumulative updates and Office 2003 updates.Hi,
From your descriptions, I understood that you would like to search the
context in export file Excel and PDF. Have I understood you? Correct me if
I was wrong.
Based on my test, I deploied sample report Product Line Sales.rdl (which
locates C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Reports) and I was able to use Search Funcation in Excel
(Edit -> Find) and in Acrobat Reader (Edit -> Search).
Would you please deploy these sample reports to your server and let me know
whether you could use search function (If you still cannot use search to
find the result, you are encourage to let me know the detail steps you do
with sample reports)
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
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.|||I can search the sample report that is exported to the same folder as the
"failed searches" xls files.
I can send you the rdl and Excel file for the file wherein I cannot
search...
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:KQ9$Zw$DFHA.3900@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> From your descriptions, I understood that you would like to search the
> context in export file Excel and PDF. Have I understood you? Correct me if
> I was wrong.
> Based on my test, I deploied sample report Product Line Sales.rdl (which
> locates C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Reports) and I was able to use Search Funcation in Excel
> (Edit -> Find) and in Acrobat Reader (Edit -> Search).
> Would you please deploy these sample reports to your server and let me
know
> whether you could use search function (If you still cannot use search to
> find the result, you are encourage to let me know the detail steps you do
> with sample reports)
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> 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.
>|||Hi,
Thanks for your prompt updates!
First of all,please understand that newsgroups are staffed weekdays by
Microsoft Support professionals to answer your non-urgent, break/fix
systems and applications questions. Our goal is to provide 24 hour response
to all questions.
Without datasource, I am afraid it will be hard to reproduce it on my side,
anyway, you are welcome to send rdl and excel files to
v-mingqc@.microsoft.com. I will have a look into it.
However, To find out the root cause of this issue we may need to analyze
what Reporting Services do internal, this work has to be done by contacting
Microsoft Product Support Services. Therefore, we probably will not be able
to resolve the issue through the newsgroups. If the issue is urgent, I
recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
Thank you for your patience and corporation.
Sincerely yours,
Michael Cheng
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.|||If you could point me to the URL that will actually allow me to open an
incident using my MSDN Universal Subscription, I'd be MOST grateful... (The
MSDN Site hasn't exactly been helpful in this regard...!)
Thanks.
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:3dRcZxmEFHA.4072@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> Thanks for your prompt updates!
> First of all,please understand that newsgroups are staffed weekdays by
> Microsoft Support professionals to answer your non-urgent, break/fix
> systems and applications questions. Our goal is to provide 24 hour
response
> to all questions.
> Without datasource, I am afraid it will be hard to reproduce it on my
side,
> anyway, you are welcome to send rdl and excel files to
> v-mingqc@.microsoft.com. I will have a look into it.
> However, To find out the root cause of this issue we may need to analyze
> what Reporting Services do internal, this work has to be done by
contacting
> Microsoft Product Support Services. Therefore, we probably will not be
able
> to resolve the issue through the newsgroups. If the issue is urgent, I
> recommend that you open a Support incident with Microsoft Product Support
> Services so that a dedicated Support Professional can assist with this
> case. If you need any help in this regard, please let me know.
> Thank you for your patience and corporation.
>
> Sincerely yours,
> Michael Cheng
> 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.
>|||Hi,
Worldwide technical support contact information can be found in the
following table. You may also contact your local MSDN Subscriptions
Services Center to inquire about technical support options available in
your country and/or region. Or, if you already have a Microsoft support
contract, you can submit an incident online.
Technical Support
http://msdn.microsoft.com/subscriptions/support/technical/
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
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.sql

Excel 2007, MDX Compatibility and connection string (for ragged dimensions)

I'm trying to open a simple SSAS 2005 cube with a ragged hierarchy (it has a level that contains member that should be hidden if the member name is the same as the parent).

I read about the necessity to use MDX Compatibility=2 into the connection string to see the HideMemberIf property in action. The dimension browser in BIDS does not display a member into a level only if it has no visible childs. May be this is a browser issue, as I read somewhere...

With Excel 2007, it should be possible to change the connection string. Unfortunately, even if I change it, Excel 2007 force the "MDX Compatibility=1" into my connection string. Moreover, it adds a "Safety Options=2" and "MDX Missing Member Mode=Error" and even if I try to change them, Excel still recreate the same connection string.

Is there someone that:

- has been successful using ragged hierarchies with Analysis Services 2005?

- has been able to change the connection string into Excel 2007?

Thank you

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Not tried them with Excel 2007, I'm afraid - it's always seemed pretty dumb to me that the default was that ragged hierarchies weren't displayed properly anyway. And what's worse is that in AS2005 you can't force the MDX compatibility property on the server either:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=653115&SiteID=1

Can we get this fixed please?

Chris

|||

Hi Chris,

I just posted in Excel 2007 forum another annoying issue.

By default, Excel 2007 display calculated measures but not calculated members on other dimensions.
You have to change this pivottable option settings each time. For each Pivot Table you create.

I asked it there is a way (eventually with a registry hack) to change this default...

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

|||

Hi there,

Has anyone seen any updated information on the ragged hierarchy issue? I am having problems with setting MDX Compatibility = 2. Excel resets it back to 1.

Cheers

Excel 2007, MDX Compatibility and connection string (for ragged dimensions)

I'm trying to open a simple SSAS 2005 cube with a ragged hierarchy (it has a level that contains member that should be hidden if the member name is the same as the parent).

I read about the necessity to use MDX Compatibility=2 into the connection string to see the HideMemberIf property in action. The dimension browser in BIDS does not display a member into a level only if it has no visible childs. May be this is a browser issue, as I read somewhere...

With Excel 2007, it should be possible to change the connection string. Unfortunately, even if I change it, Excel 2007 force the "MDX Compatibility=1" into my connection string. Moreover, it adds a "Safety Options=2" and "MDX Missing Member Mode=Error" and even if I try to change them, Excel still recreate the same connection string.

Is there someone that:

- has been successful using ragged hierarchies with Analysis Services 2005?

- has been able to change the connection string into Excel 2007?

Thank you

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Not tried them with Excel 2007, I'm afraid - it's always seemed pretty dumb to me that the default was that ragged hierarchies weren't displayed properly anyway. And what's worse is that in AS2005 you can't force the MDX compatibility property on the server either:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=653115&SiteID=1

Can we get this fixed please?

Chris

|||

Hi Chris,

I just posted in Excel 2007 forum another annoying issue.

By default, Excel 2007 display calculated measures but not calculated members on other dimensions.
You have to change this pivottable option settings each time. For each Pivot Table you create.

I asked it there is a way (eventually with a registry hack) to change this default...

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Event id: 416 - String message: Session idle timeout over, tearing

Does anyone know what this info message means'Is the event ID actually 4156 with a source of MSDTC?
If so, check the following for more information:
[url]http://www.eventid.net/display.asp?eventid=4156&eventno=695&source=MSDTC&phase=1[/
url]
-Sue
On Mon, 8 Aug 2005 07:15:05 -0700, Nick Pennisi
<NickPennisi@.discussions.microsoft.com> wrote:

>Does anyone know what this info message means'

Event id: 416 - String message: Session idle timeout over, tearing

Does anyone know what this info message means?
Is the event ID actually 4156 with a source of MSDTC?
If so, check the following for more information:
http://www.eventid.net/display.asp?e...SDTC& phase=1
-Sue
On Mon, 8 Aug 2005 07:15:05 -0700, Nick Pennisi
<NickPennisi@.discussions.microsoft.com> wrote:

>Does anyone know what this info message means?

Event id: 416 - String message: Session idle timeout over, tearing

Does anyone know what this info message means'Is the event ID actually 4156 with a source of MSDTC?
If so, check the following for more information:
http://www.eventid.net/display.asp?eventid=4156&eventno=695&source=MSDTC&phase=1
-Sue
On Mon, 8 Aug 2005 07:15:05 -0700, Nick Pennisi
<NickPennisi@.discussions.microsoft.com> wrote:
>Does anyone know what this info message means'