asked on

SSIS If Sql Statement is true send email?

You have seen this one before, but I need some hand holding. I have a SSIS package and in there is a part where I need to query a table to see if any files records are there. If so execute the unknown file package which sends an email. In the attached screen shot you will see that I have created a Variable called RecCnt and the Execute SQL Task Editor show that I have selected Single row. the second sheet of the attached file show the Result Set tab where Result Name is 0 (zero) and Variable Name is User::RecCnt. Now this is where I get lost in the weeds. The query is :

DECLARE
@RecCnt INT;

SET @RecCnt = 0

SELECT
@RecCnt = COUNT(*)
FROM BobWhite.dbo.fileloadhistory as flh

I set the output of the count to the variable @RecCnt. How do I set up the Precedence Constraint Editor when the count is > 0? What else do I need in the Execute SQL Task Editor?
screen1.docx

ASKER CERTIFIED SOLUTION

edtechdba

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

wdbates

ASKER

Not using the SendMail task. Looking at the attached file I will test to see if the count > 0 and then execute a DTSX package that formats the information in Excel and uses an off the shelf mail package.

edtechdba

That's OK, I think you can still set the Disable property on most tasks based on the value of your variable (@RecCnt), the same expression code should apply.

wdbates

ASKER

Error      1      Error loading Package.dtsx: The variable "User::RecCnt" was not found in the Variables collection. The variable might not exist in the correct scope.      C:\Users\dbates\Documents\Visual Studio 2008\Projects\Integration Services Project1\Integration Services Project1\Package.dtsx      1      1

Error      2      Error loading Package.dtsx: Attempt to parse the expression "@[User::RecCnt]<1" failed and returned error code 0xC00470A6. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.      C:\Users\dbates\Documents\Visual Studio 2008\Projects\Integration Services Project1\Integration Services Project1\Package.dtsx      1      1

Error      3      Error loading Package.dtsx: The expression "@[User::RecCnt]<1" on property "Disable" cannot be evaluated. Modify the expression to be valid.      C:\Users\dbates\Documents\Visual Studio 2008\Projects\Integration Services Project1\Integration Services Project1\Package.dtsx      1      1

edtechdba

Did you set up your RecCnt variable when you were in the Control Flow window? Or within a Data Flow window? If you need to have a task in the Control Flow check the RecCnt variable value, then I would recommend making sure your variable is set up within the Control Flow so that it can capture the value.

If you click on your Control Flow window and look in the Variables menu, do you see your variable, RecCnt, in the list? If not, you'll need to add it.

wdbates

ASKER

Was able to remove the errors and set the Expression as you suggested. I can execute the program in debug and even with a count of 0 it passes on to the next task. See the attached file.
screen2.docx

edtechdba

I think I see what is happening ...

It looks like you are setting your Execute SQL Task value to store within the variable @RecCnt, and then you are also trying to use that same variable within the Disable property of that same Execute SQL Task. I'm not sure that's do-able, although I haven't tried personally.

How about setting the Disable property expression on the "Email List of Unknown" task instead of the "Check for files" task?

wdbates

ASKER

Error 1 Error loading Email List of Unknown.dtsx: The variable "User::RecCnt" was not found in the Variables collection. The variable might not exist in the correct scope.

edtechdba

How about deleting RecCnt variable and then re-creating it while are you in the Control Flow window?

Hmm, this should work, I use this expression logic in almost all of my packages.

edtechdba

And maybe trying this code in your Execute SQL Task?

SELECT COUNT(*) AS RecCnt
FROM BobWhite.dbo.fileloadhistory

Open in new window

And set your Result Name is the Result Set setting in your Execute SQL Task to "RecCnt". Not sure this will make a difference, but maybe worth a try.

wdbates

ASKER

That did not get it. I have to go an pick up the kids. I will attach a screen shot of all the tasks when I retrun.

wdbates

ASKER

Here are all the screens. I did change the query and it is on page 3 or 4.
allscreen.docx

edtechdba

Ah, OK.

How about trying this method?]
1. Remove the Precedence Constraint Logic (remove expression code)
-- Set it back to function as normal, passing the process over to the DTSX task.
2. Click on "Email List of Unknown Files" task and go to the property box.
3. Click on "Expressions" ("..."), select "Disable" from the drop down list in the Expressions box, and then enter your expression code for your variable "@[User::RecCnt]<1"

This will disable the task if the count < 1, but if the count is > 0 then it should allow the execution of the DTSX package task.

edtechdba

Good luck! I'm running on EST, so I'll check back in the morning.

wdbates

ASKER

OK, I performed 1 through 3. I also removed the Expression from Check for files task. When I execute the package it run through validation and stops.

edtechdba

Are you saying that the task disabling process worked with no errors?
Where your RecCnt was > 0 and the task was disabled as intended?

What is your current record count if you were to run the select query by itself?

wdbates

ASKER

It is working now. See the attachment for the solution. In the Check for file task I assign count(*) to NewResultName which is the default in the Result Set tab. I insert the value 0 (zero) into the Result Name and User::RecCnt in the Variable Name section. There are no Expressions. In the Precedence Constraint Editors I test the value of RecCnt to determine which process to follow.

Thank you for your help.
Solution.docx