October 10, 2011 at 1:56 pm
Hi,
IF ( SELECT COUNT(t.name) AS CheckQuery FROM (
SELECT f.name FROM fleets f
WHERE f.name NOT IN ( SELECT C.CompanyName FROM Company C
LEFT JOIN Accounts A ON A.CompanyName = C.CompanyName
WHERE C.CCode = ABCD') AND f.[status] = 2) t ) = 0
Print 'Exists'
ELSE
Print 'AddCompany'
I have to execute the package whatever the value of the code above is , but if it is > 0 then send mail to some one.
For the send mail part
I am using
Expression or constarin,Failure, and @CheckQuery > "0",Logical AND.
But its sending email whether or not The above exp = 0 or > 0
Thanks,
October 10, 2011 at 2:01 pm
IF ( SELECT COUNT(t.name) AS CheckQuery FROM (
SELECT f.name FROM fleets f
WHERE f.name NOT IN ( SELECT C.CompanyName FROM Company C
LEFT JOIN Accounts A ON A.CompanyName = C.CompanyName
WHERE C.CCode = ABCD') AND f.[status] = 2) t ) = 0
Print 'Exists'
ELSE
Print 'AddCompany'
This code does not return a number.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 10, 2011 at 2:04 pm
it looks like you are testing the number of records that match...so i'd cosnider changing ti to simply IF EXISTS:
IF EXISTS(SELECT f.name
FROM fleets f
WHERE f.name NOT IN (SELECT C.CompanyName
FROM Company C
LEFT JOIN Accounts A
ON A.CompanyName = C.CompanyName
WHERE C.CCode = 'ABCD')
AND f.[status] = 2)
PRINT 'Exists'
ELSE
PRINT 'AddCompany'
Lowell
October 10, 2011 at 2:13 pm
If this code is driving an Execute SQL task and a number is required for a subsequent precedence constraint, the query should return a number!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 10, 2011 at 2:16 pm
ok. Then should I use @CheckQuery > "0" or @CheckQuery > "AddCompany",
October 10, 2011 at 2:24 pm
Yes it is used in execute sql task . Not getting what I wanted.
October 10, 2011 at 3:12 pm
By default, an Execute SQL Task only returns success/failure information to the package. If you want to use the output of a result set, you need to specify the result set type on the General tab and then assign the information to SSIS variables. It sounds like you have not done this.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 10, 2011 at 5:49 pm
I am using an SSIS variable to get the results from the IF statement
But I get the following error :
[Execute SQL Task] Error: Executing the query "IF ( SELECT count(f.name) AS VerifyCompanyExists ..." failed with the following error: "An expression of non-boolean type specified in a context where a condition is expected, near 't'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
October 10, 2011 at 5:51 pm
Using the query below
IF ( SELECT count(f.name) AS VerifyCompanyExists FROM fleets f
WHERE f.name NOT IN ( SELECT DISTINCT C.CompanyName
FROM Company C
LEFT JOIN Accounts A ON A.CompanyName = C.CompanyName
WHERE C.CorporateCode = '" +@[User::CorporateCode] +"'" + ") AND f.[status] = 2)
t ) = 0
PRINT 'AddCompany'
ELSE
PRINT 'Exists'
October 10, 2011 at 9:08 pm
Got this working . Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply