Propery expressions: Values not updated

I see some strange behavior when running a package using the SQL Server Agent. The package I run calls a number of child packages, in which I use property expressions to set - among other things - the path to text files I read into a database.

The property expressions are in the form: @.[User::ThePath] + "\\TheFile.txt". The variable ThePath is read from an XML configuration file at runtime from the "master" package and passed on to the child packages using a parent package variable configuration.

My problem is this: When the package is executed by the SQL Server Agent, the properties for the path of the text files are not updated, and the package fails, as it cannot locate the files. When using DTExecUI to execute the same package, everything runs fine.

Could it be a permissions issue? I don't think so, as the SQL Server Agent runs under the same account as I am logged in when executing with DTExecUI.

Anyone?

It does sound a bit like permissions.
So are you referring to the Account the Agent service runs under OR the account the specific Agent Job Step is executing AS, which are 2 different things.

>>>Could it be a permissions issue? I don't think so, as the SQL Server Agent runs under the same account as I am logged in when executing with DTExecUI.

http://support.microsoft.com/?kbid=918760

|||The SQL Agent is running under a local administrator account, which is the same I use when I log in and run the package using DTExecUI. In the scheduled job, the package is "Run as" the "SQL Agent Service Account", which I think should mean that it is executing in the same security context as when I run it manually?!|||

This one has been bugging me big time!

Now, it seems that I might have found a workaround. If I store the main/parent package (the one executed in the Agent job) in MSDB and let the job execute it from here, everything seems to work fine and the property expressions are properly updated at runtime - even though those property expressions are used in the child packages. Previously I had all the packages stored in SSIS Package Store (File System), and that is also where I still keep the child packages.

Can anyone explain this behavior?

|||Can anyone give any explanation for this behavior?|||Now, it gets even more strange. When the job runs on a schedule, it fails, but when I start it manually by clicking "Start job at step..." it runs fine.

Properties Expressions

I can create an expression for the visibility property as follows:
=iif(countDistinct(Fields!RefNbr.Value)=1,True,False)
BUT when I try to do something similar for the fontweight property like
=iif( Fields!Carryover.Value = 2 ,Heavy,Normal) I get the following error message
Name Heavy is not declared
'Try: =iif( Fields!Carryover.Value = 2 ,"Heavy","Normal")
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:E4B78AB2-9D82-4B14-9A0A-2FFD4D41359D@.microsoft.com...
>I can create an expression for the visibility property as follows:
> =iif(countDistinct(Fields!RefNbr.Value)=1,True,False)
> BUT when I try to do something similar for the fontweight property like
> =iif( Fields!Carryover.Value = 2 ,Heavy,Normal) I get the following error
> message
> Name Heavy is not declared
> '|||Another gotcha is using font weight names in expression. When using a font
weight in an expression you have to use the serialized value.
In your case you will need to use =iif(Fields!Carryover.Value = 2, "900",
"Normal).
This chart shows how the named displyed in Report Designer's UI maps to the
serialized value.
UI Display Serialized
Lighter Lighter
Thin 100
Extra Light 200
Light Light
Normal Normal
Medium 500
Semi-Bold 600
Bold 700
Extra Bold 800
Heavy 900
Bolder Bolder
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:O86wRYyeEHA.384@.TK2MSFTNGP10.phx.gbl...
> Try: =iif( Fields!Carryover.Value = 2 ,"Heavy","Normal")
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:E4B78AB2-9D82-4B14-9A0A-2FFD4D41359D@.microsoft.com...
> >I can create an expression for the visibility property as follows:
> > =iif(countDistinct(Fields!RefNbr.Value)=1,True,False)
> >
> > BUT when I try to do something similar for the fontweight property like
> > =iif( Fields!Carryover.Value = 2 ,Heavy,Normal) I get the following
error
> > message
> >
> > Name Heavy is not declared
> >
> > '
>

Properties affected by Expressions

Hi,

is there any way to see which property is changed/affected by expressions? If not that would make things really complicated because you look at a property and it seams to be OK but the task doesn't do what it's supposed to because "behind the scenes" the setting is changed by an expression...

Thanks,

To see a list of properties with expression, go to the same dialog as you set the expression. The first screen shows what is set, the "Property Expressions Editor" dialog. You can also expand the "Expressions" property in the VS Properties pane of the standard MS task dialogs, and this will list the properties as well.
Is this not what you want?

|||Darren,

OK, you can see the expressions at the place where they are defined... My idea would be that properties which are overrided by expressions are shown in a different color or marked with a big blue bullet... OK, I don't insist on blue but anything you notice on the first sight would be great!|||

Thomas Pagel wrote:

Darren,

OK, you can see the expressions at the place where they are defined... My idea would be that properties which are overrided by expressions are shown in a different color or marked with a big blue bullet... OK, I don't insist on blue but anything you notice on the first sight would be great!

Very much a nice-to-have. I like it Thomas. Have you submitted it to MS?|||Jamie,

it's on the way to my blog and to MS...|||

Thomas Pagel wrote:

Jamie,

it's on the way to my blog and to MS...

I didn't know you had a blog Thomas...but I do now :)

You're on my reader!!|||Jamie,

just started some weeks ago... More content to come... At least I hope so...|||Having a way to see the expressions is a good idea - I have ran into this problem as well, only to realize I had set an expression.

I started adding an annotation with the word "Expression" by every task that has an expression, but an automated way would be ideal!

-Evan Black|||See what you are saying, and I'd go for blue too. It would be handy.

Probably quite nasty to code into the VS property-grid, and an MS only job, but certainly doable in the custom UI's. I'll bear it in mind when I get back to doing some UIs myself.|||Additionally, how about if you hover over the property it shows you any expression that isapplied to it?

-Jamie|||Yup, we had that on the plans... kind of similar to what the other VS projects have when you turn on 'Dynamic Properties'... each property in the grid has a small icon next to it.

Last time I checked that wasn't available to us for using.

But, yes, please log and vote on that.

regards|||This is along the lines of the problem with event handlers, methinks-- there is no way to see where they are, and they can be anywhere and change anything, so you can't distinguish what is real from what is not (ie, what is just irrelevant placeholder values) when you look at SSIS packages?
|||Take a look at the updated UI for the File Watcher Task. I expose the expression instead of the property when set. Hope MS implement something similar, as I know I have spent way too much time cursing trying to get stuff to work, and it was all down to an expression I forgot about. Screen shot here-

File Watcher Task
(http://www.sqlis.com/default.aspx?23)

Properties affected by Expressions

Hi,

is there any way to see which property is changed/affected by expressions? If not that would make things really complicated because you look at a property and it seams to be OK but the task doesn't do what it's supposed to because "behind the scenes" the setting is changed by an expression...

Thanks,

To see a list of properties with expression, go to the same dialog as you set the expression. The first screen shows what is set, the "Property Expressions Editor" dialog. You can also expand the "Expressions" property in the VS Properties pane of the standard MS task dialogs, and this will list the properties as well.
Is this not what you want?

|||Darren,

OK, you can see the expressions at the place where they are defined... My idea would be that properties which are overrided by expressions are shown in a different color or marked with a big blue bullet... OK, I don't insist on blue but anything you notice on the first sight would be great!|||

Thomas Pagel wrote:

Darren,

OK, you can see the expressions at the place where they are defined... My idea would be that properties which are overrided by expressions are shown in a different color or marked with a big blue bullet... OK, I don't insist on blue but anything you notice on the first sight would be great!

Very much a nice-to-have. I like it Thomas. Have you submitted it to MS?|||Jamie,

it's on the way to my blog and to MS...|||

Thomas Pagel wrote:

Jamie,

it's on the way to my blog and to MS...

I didn't know you had a blog Thomas...but I do now :)

You're on my reader!!|||Jamie,

just started some weeks ago... More content to come... At least I hope so...|||Having a way to see the expressions is a good idea - I have ran into this problem as well, only to realize I had set an expression.

I started adding an annotation with the word "Expression" by every task that has an expression, but an automated way would be ideal!

-Evan Black|||See what you are saying, and I'd go for blue too. It would be handy.

Probably quite nasty to code into the VS property-grid, and an MS only job, but certainly doable in the custom UI's. I'll bear it in mind when I get back to doing some UIs myself.|||Additionally, how about if you hover over the property it shows you any expression that isapplied to it?

-Jamie|||Yup, we had that on the plans... kind of similar to what the other VS projects have when you turn on 'Dynamic Properties'... each property in the grid has a small icon next to it.

Last time I checked that wasn't available to us for using.

But, yes, please log and vote on that.

regards|||This is along the lines of the problem with event handlers, methinks-- there is no way to see where they are, and they can be anywhere and change anything, so you can't distinguish what is real from what is not (ie, what is just irrelevant placeholder values) when you look at SSIS packages?
|||Take a look at the updated UI for the File Watcher Task. I expose the expression instead of the property when set. Hope MS implement something similar, as I know I have spent way too much time cursing trying to get stuff to work, and it was all down to an expression I forgot about. Screen shot here-

File Watcher Task
(http://www.sqlis.com/default.aspx?23)

Programmatically Evaluate Expression

I would like to evaluate expressions from within my execute function in a custom task. I saw this post from about 8 months ago detailing how it would be possible:

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

Is there an updated response (or more detailed example) to the question? Am I really risking a lot by using this undocumented feature?

Thanks!

Hi David,

To quote Kirk from the thread you linked:

"...this is not documented and will not be supported by Microsoft. It could change at anytime and break your component if you use it. User beware. "

This remains the case. I encourage you to head over to the MSDN Product Feedback site at http://msdn.microsoft.com/sql/bi/integration/ and submit a suggestion for exposing this functionality.

Thanks
Mark

|||

Thanks for the feedback. I have submitted the suggestion:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=133078

Programmability of parameter bar(layout/items)?

Hi all,

does anybody of you know how to code and customize the parameter bar of the
SSRS reports? I know about putting code behind expressions but how is it with
parameters?

How is it possible to...
1. define the layout of the parameter bar and the positions of the parameter
items?
2. make the parameter items user-specific?

Is it possible to define a report with the report designer and hand over
data (parameters) by code-behind or a referenced assembly?

Thanks a lot for any help

Best Regards
Marc

Unfortunately, you can't do either of these things. (#1, #2, or the "hand-over" of parameter selection via Report Designer).

If the parameter selection needs to be as dynamic as you describe, the best solution is to actually build your own custom UI in which you display the appropriate parameter UI in the appropriate places to the appropriate users...Then take the values that get selected and pass them to the report via URL Access, or plug them into the ReportViewer with code, andrender the report in the viewer. www.gotreportviewer.com has samples of the latter.

Hope this helps!

|||

Hey Russell,

that's not really good news...but thanks a lot for your help anyway..i'm going to try the solution with passing parameters by code into the ReportViewer control.

Cheers
Marc

|||

Hi Marc,

I have a similar problem. Could you please explain how you were able to solve this problem.

Thanks a lot for your help.

Regards,

Simranjeev

|||You need to query the web service to find out which parameters a report has, their data types and values etc. then programatically populate dropdowns etc. and arrange them how you like in your custom UI. You can use either a web or a windows UI for this.|||

Thanks ... Can i find some article / sample code somewhere on how to build custom UI and use it as the Parameter Area in Reporting Services

Programmability of parameter bar(layout/items)?

Hi all,

does anybody of you know how to code and customize the parameter bar of the
SSRS reports? I know about putting code behind expressions but how is it with
parameters?

How is it possible to...
1. define the layout of the parameter bar and the positions of the parameter
items?
2. make the parameter items user-specific?

Is it possible to define a report with the report designer and hand over
data (parameters) by code-behind or a referenced assembly?

Thanks a lot for any help

Best Regards
Marc

Unfortunately, you can't do either of these things. (#1, #2, or the "hand-over" of parameter selection via Report Designer).

If the parameter selection needs to be as dynamic as you describe, the best solution is to actually build your own custom UI in which you display the appropriate parameter UI in the appropriate places to the appropriate users...Then take the values that get selected and pass them to the report via URL Access, or plug them into the ReportViewer with code, andrender the report in the viewer. www.gotreportviewer.com has samples of the latter.

Hope this helps!

|||

Hey Russell,

that's not really good news...but thanks a lot for your help anyway..i'm going to try the solution with passing parameters by code into the ReportViewer control.

Cheers
Marc

|||

Hi Marc,

I have a similar problem. Could you please explain how you were able to solve this problem.

Thanks a lot for your help.

Regards,

Simranjeev

|||You need to query the web service to find out which parameters a report has, their data types and values etc. then programatically populate dropdowns etc. and arrange them how you like in your custom UI. You can use either a web or a windows UI for this.|||

Thanks ... Can i find some article / sample code somewhere on how to build custom UI and use it as the Parameter Area in Reporting Services

Programmability of parameter bar(layout/items)?

Hi all,
does anybody of you know how to code and customize the parameter bar of the
SSRS reports? I know about putting code behind expressions but how is it with
parameters?
How is it possible to...
1. define the layout of the parameter bar and the positions of the parameter
items?
2. make the parameter items user-specific?
Is it possible to define a report with the report designer and hand over
data (parameters) by code-behind or a referenced assembly?
Thanks a lot for any help
Best Regards
MarcI don't know if this is the right way but I turned off the parameter bar and
added the following code
Dim parm As New
Microsoft.Reporting.WebForms.ReportParameter("week",
DrpWeeks.SelectedValue.ToString)
Dim p() As Microsoft.Reporting.WebForms.ReportParameter = {parm}
ReportViewer1.ServerReport.SetParameters(p)
DrpWeeks is a drop down box populated just as I would have if I had
populated it in the report designer.
User specific is by grabbing user!userid
"MarcBey" wrote:
> Hi all,
> does anybody of you know how to code and customize the parameter bar of the
> SSRS reports? I know about putting code behind expressions but how is it with
> parameters?
> How is it possible to...
> 1. define the layout of the parameter bar and the positions of the parameter
> items?
> 2. make the parameter items user-specific?
> Is it possible to define a report with the report designer and hand over
> data (parameters) by code-behind or a referenced assembly?
> Thanks a lot for any help
> Best Regards
> Marc
>
>