Fail package with expression

How do I use the expression to force a failure using an If statement to evalute a rowcount variable?

I'd evaluate that variable in a script task instead.|||

Do you have an example of how to do that?

If my rowcount < 1 then I want to fail the package

|||

Ken Augustine wrote:

Do you have an example of how to do that?

If my rowcount < 1 then I want to fail the package

Actually, use an expression. Add the script task and hook up a precedence constraint to it. Set it to evaluate the expression.

For the script code, it's basically the default code that is generated when you add a new script task. The only difference is that you call Dts.Results.Failure instead of success.

Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()
Dts.TaskResult = Dts.Results.Failure
End Sub

End Class

|||

Can you help me with the if then expression to evalute the variable?

Thanks for your help.

|||

Ken Augustine wrote:

Can you help me with the if then expression to evalute the variable?

Thanks for your help.

Just use:

@.rowcount < 1

That's all you need. When you double click on the connection between the ?Data flow? and the script task, set it to use constraint and expression. Then use the above (copy and paste if your variable is named 'rowcount') in the expression box. Note that I believe the CaSE of the variable name is important.

F I L T E R HELP!!! PLEASE!!!! THIS CAN'T BE THAT HARD

In SSRS 2005, how can I add this check (what do I have to put in the Expression, operator, and value in filters tab of table properties) to my table filter to ensure only customers that match one of the ORs are viewed in my report?

HERE IS A PRINT SCREEN OF THE FILTER TAB WHERE i WANT THIS ALL TO HAPPEN...PROPERTIES OF MY TABLE:
http://www.photopizzaz.biz/filtertab_ssrs2005table.jpg

essentially I want this check to filter records on my table...match to this criteria from my dataset:

(Fields!Branch.Value = '00002' and
Fields!CustomerNumber.Value = '0000002' or
Fields!CustomerNumber.Value = '0000003' or
Fields!CustomerNumber.Value = '0000004' or
Fields!CustomerNumber.Value = '0000155' or
Fields!CustomerNumber.Value = '0000156' or
Fields!CustomerNumber.Value = '0000159' or
Fields!CustomerNumber.Value = '0000160' or
Fields!CustomerNumber.Value = '0000161' or
Fields!CustomerNumber.Value = '0000118' or
Fields!CustomerNumber.Value = '0000153' or
Fields!CustomerNumber.Value = '0000152' or
Fields!CustomerNumber.Value = '0000108' or
Fields!CustomerNumber.Value = '0000158' or
Fields!CustomerNumber.Value = '0000133')

OR

(Fields!Branch.Value <> '00002' and
Fields!CustomerNumber.Value = '0000053' or
Fields!CustomerNumber.Value = '0000058' or
Fields!CustomerNumber.Value = '0000072' or
Fields!CustomerNumber.Value = '0000073' or
Fields!CustomerNumber.Value = '0000079' or
Fields!CustomerNumber.Value = '0000080' or
Fields!CustomerNumber.Value = '0000143' or
Fields!CustomerNumber.Value = '0000146' or
Fields!CustomerNumber.Value = '0000157' or
Fields!CustomerNumber.Value = '0000135')

Fav, Why don't you put these values into a SQL DB table and add it as an additional dataset for your report. Then, just reference the new column/table in the Expression definition.

Just a thought .....

|||

because while that would work, it's more overhead. I want to be able to use the reporting interface, that's what it's for. I'm not one to like to code all this crap in the backend for a report as complicated as the one I have. I'd like to be able to use the tool, SSRS which is what it's for and manage the filters, at least some of the major ones through the UI..I don't like managing everything in my stored procs and tables...that is way too much work...and that's why a reporting interface along with .NET and VB can help manage. I already have around 7 stored procs and 5 tables for this report...I don't need any more overhead....I don't want reporting to be 100% of my life at work...easier to maintain this with SSRS reporting filters. The problem is, SSRS 2005 is too new and nobody seems to know how to tell me how to form expressions in table filters at this point it looks like.

Why use reporting services if you can't harness filters like I want? think of it the reverse. If I did all this in SQL, first it would take me a year, second, then I would have no need for a UI...well, I don't have time to code everything in SQL, that's what UI is there to help with. I come from Crystal...if I had to code all that in SQL, adios to all my other projects. The stored procs behind my 5 datasets in this report have enough code in it....it's a nightmare.

|||

u can try this one

(Fields!Branch.Value = '00002' and
Fields!CustomerNumber.Value in ( '0000002' ,'0000003' , '0000004', '0000155' , '0000133') )

something like this, u can put all these constant values in expression after selecting IN operator

|||

thanks, so just so I have this right....in the filter screen, I'd put something like:

Expression Operator Value

Fields!CustomerNumber IN (Fields!Branch.Value = '00002' and
Fields!CustomerNumber.Value in ( '0000002' ,'0000003' , '0000004', '0000155' , '0000133') )

I need to be sure I'm putting your expression in the right place and that my operator is correct. So would you expression go on the expression side or Value when you look at my URL above?

|||

So, I tried this, but it doesn't like the syntax and how I set it up in this whole tab. Am I not doing this right?

http://www.photopizzaz.biz/filtertab_ssrs2005table2.jpg

|||

i think u have to use like this

Expression Operator Value

Fields!Branch.Value = '00002'

Fields!CustomerNumber.Value IN ( '0000002' ,'0000003' , '0000004', '0000155' , '0000133') )

use double quotes instead of single quotes if this does not work

OR else u can try this

Expression Operator Value

Fields!CustomerNumber.Value IN ( iif(Fields!Branch.Value = '00002' ,('0000002' ,'0000003' , '0000004', '0000155' , '0000133'), ('0000001'))

use iif function check this out in MSDN and try out

|||thanks, but what about the other OR piece?|||

i think u should use IIF for this - so for that OR piece u have to include all the ids where i have included ('000001')

that is, if use IIF function it needs three parameters, first parameter will be an expression, in ur case to check branch value is '000002',

second parameter will if the expression is true, include all the ids when branch value is '000002'

third parameter will be if the expression is false,include all the ids when branch value is not equal to '000002'