Passing a parameter value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Passing a parameter value

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-04-09 : 11:30:34

Hi ,

I have this situation,I need to store results of a Select Count(*) from TableA to a parameter and only if the value of the count(*) is greater than 50,000,a notification mail would be sent out to the table owner.Currently this is what I have done:
1) Use SQL task to include "Select Count(*) from TableA"
2) In parameter mapping tab of SQL Task, define the following
Parameter name : Table::Count
Direction: Input
Data Type: Numeric
Parameter Name : @Count
3) I have connected the SQL Task to a Send Mail Task [ Only If @count>50 000, then a mail should be sent out]
4) The constraints has been set as
Evaluation Operation : Expression and Constraints
Value: Success
Expression : @Count >"50000"

In SSIS tab > Variable
I have set the following
Variable Name : Count
DataType : Int32

Can someone advise me where I am going wrong, as when I execute the SQL Task turns greens and ends.Even when the count of tableA is greater than 50 000, no mails I sent out[send mail task does not get executed]

Thank You in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 12:00:28

User variables are accessed as user::<variablenames> so use user::Count > .Also why are you comparing it to string "50,000"?

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-04-09 : 12:25:35

Visakh,

I changed the variable name to User::Count.

What would be the correct datatype that I should use for both parameter and variable ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 12:31:15

integer

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-04-09 : 12:33:49

Manage to twig some settings here and there and got it to work.

It works fine when count(*) > 50 000, a mail is sent out BUT

A mail is also sent out when the count is < 50 000.

Am I missing on something?

Can someone point me in the right direction.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 12:49:55

There are two radios button on bottom of tab where you set this properties:-
Evaluation Operation : Expression and Constraints
Value: Success
Expression : User::Count >50000

Please check if its set as AND option. If not change it to AND.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-04-09 : 13:06:11

It is set to 'AND'.
Anything else that I need to check?
Could you populate the same package on your site with the same settings and let me know where I am missing out.As I have read many sites and still not able to find out where is my mistake.Thank You.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 13:29:32

I dont have SQL box here. You can also put some break point in your tasks and see the value of count variables while package is running. Also you can count variable in watch scrren to check its intermediate values. You can even try using a Rowcount task which returns the count of records in work flow to a variable.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-04-09 : 13:47:44

Hi Visakh,
I tried as the following create a data flow task [ Oledb connection to row count],use a variable to store data count values and connect it to send mail task.
And it still behaves in the same way.
Send's mail even when count of records is lesser than 50 000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 14:13:59

Can you add a Breakpoint in sql task on post execute event & check the value of count variable?

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-04-09 : 14:31:52

I addded a breakpoint on sql task,while running at breakpoint,I checked variable count value at execution.It shows "0".

As this is my 1st time using breakpoint.Could you confirm if this is how you check the variable value.SSIS tab > variable> value .If this is correct, then value displayed is ='0'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 14:48:02

No. When you execute package, it stops at breakpoint. Go to immediate window and select count variable from the dropdown to view its value.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-04-09 : 15:28:22

Hi Visakh,
Thank You for your help and patience.
I rechecked my precedence constraints and followed this site http://support.microsoft.com/kb/906547 and now Im glad to say it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources