Precedence Constraint Editor - 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)

Precedence Constraint Editor

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2009-04-27 : 09:30:24

Hi Guys, I am trying create tasks in a pkg that will check if records exist on a file. I am using the Precedence Constraint Editor and I’ve created a variable (@RecCount). In the Exec SQL Task (on the General tab) I have set my ResultSet to ‘Single Row’ and (on the Result Set tab) under Result Set I have a zero and under variable name I have User::RecCount.

I have two arrows coming from my Exec SQL Task (the Precedence Constraint Editor test is).
1. @RecCount <=0 (sends email, stop process)
2. @RecCount > 0 (continues processing)

Here is my SQL statement that is in my Exec SQL Task
Declare @RecCount as int
Select @RecCount =(count(*))
FROM Master_Fulfillment
WHERE Client_Id = '1000020000' AND SalesType = 'DWN'
Print @RecCount

If @RecCount >0
Select @RecCount as ReCount
Else
select 0 as RecCount

The problem is the pkg always takes the @RecCount <=0 path. I have run the query in SQL Server and the value of ReCount and @RecCount is 2

What in the world am I missing?

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-04-27 : 09:50:25

What's the value of @RecCount OnPostExecute on SQL Task?

Trudye
Posting Yak Master

169 Posts

Posted - 2009-04-27 : 10:04:05

HI rgombina, thanks for responding. I don't know how to determine the value OnPostExecute on SQL Task. I would love to know how to test OnPostExecute on SQL Task's value.

I just tested it in SQL Server and it returned a value of 2 which is correct.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-04-27 : 10:14:32

Oh - right-click SQL Task and do Edit BreakPoint then check OnPostExecute (run debug as usual). Scroll over Auto tab at the bottom (or one of those taks) and you'll see bunch of useful values assigned to User or System variables. This is just test what @RecCount really is at that point.

Trudye
Posting Yak Master

169 Posts

Posted - 2009-04-27 : 10:26:12

That was very, very cool, thank so much rgombina. The answer to your question is {2} is the value of User::RecCount.

And that is what is should be.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-04-27 : 10:54:55

Are using "Expression, AND" (solid blue) or other combo? Try @RecCount < 1 and @RecCount > 0 and see what happens.

Trudye
Posting Yak Master

169 Posts

Posted - 2009-04-27 : 11:16:54

I am just using Expression (solid blue line). I changed the expression from @RecCount <=0 to @RecCount <1 but I received the same results.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-04-27 : 14:50:44

Create another package with same logic but value from variable, hardcode. Doesn't make sense why it is not evaluating correctly.

Trudye
Posting Yak Master

169 Posts

Posted - 2009-04-28 : 11:19:35

HI rgombina, thanks so much for hanging in there with me. You are 100% correct I created a new variable and a new task and it worked like a charm. Think I'll have to drop a line or two to Uncle Bill about this one (smile).

Thanks again,
Trudye

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources