?
Solved

SSIS If Sql Statement is true send email?

Posted on 2011-10-14
17
Medium Priority
?
365 Views
Last Modified: 2012-05-12
Hi,
I have a package that imports a flat file into a Sql server table, with any row errors inserted into an error table.
In the calling package I would like to perform a select on this error table where count(*) having > than 0. If the Sql returns true then have an email sent indicating that there were some errors in the import.
I am not sure what the best way is to perform this type of action. Any help would be appreciated.

importerrorpackage.jpg
importpackage.jpg
0
Comment
Question by:gcrickman
  • 9
  • 7
17 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 36971136
--You could use the sample code below to send query output as attachemnt from a SSIS T-sql task:

declare @report_file_name varchar(30),
            @sql varchar(max),
            @emailbody varchar(4000)
      
      set @sql = N'SET NOCOUNT ON
                        SELECT whatever FROM whatever_table WHERE whatever LIKE ''%error%'''

      SET @report_file_name = 'file_list_at_' + convert(varchar(10),getdate(), 112) + '.txt'
      SET @emailbody = 'Here is the list you wanted as today(yyyy.mm.dd):  '+ convert(varchar(10),getdate(), 102)
      EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Mail Profile', -- mail profile here
            @recipients='user@mail.com',
            @subject = 'This is a TEST email',
            @body = @emailbody,
            @body_format = 'TEXT',
            @query = @sql,
            @attach_query_result_as_file = 1,
            @execute_query_database = 'YourDB', -- your database name here
            @query_attachment_filename = @report_file_name,
            @query_result_header = 1,
            @query_result_no_padding = 1,
            @query_result_separator = '      '
0
 
LVL 11

Accepted Solution

by:
Simone B earned 2000 total points
ID: 36971330
Create a package variable to hold the count of records in the error table. In the SQL task where you are doing the select count(*), populate the variable. In the SQL task on the General tab, set the result set to single row. Change your query to select count(*) as NewResultName. On the Result Set tab, set NewResultName equal to your variable.

Right click on the data flow which goes to the send mail task and select edit. In the Precedence Constraint Editor, change the Evaluation Operation to Expression. Enter the expression as
@[User::YourVariableName] > 1. The send mail will only happen when the precedence constraint evaluates to true.

You will have a second flow going to the task where there are no errors. You can change that precedence constraint also if you like, depending on when you want it to execute.

If you want to also send the error details as an attachment to the email, then you will need another task prior to send mail. This will be a data flow task to copy the errors in the error table to a text file. You can then specify the text file as an attachment in the send mail task.
0
 

Author Comment

by:gcrickman
ID: 36979683
@Buttercup1

As far as the flow goes would this work?



 Email Errors
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:gcrickman
ID: 36979903
@Buttercup1

I am having issues setting up the variable and populating it. Here is what I have done.
 Creation of Variable General Tab of SQL Task Result Set Tab of SQL Task
0
 
LVL 11

Expert Comment

by:Simone B
ID: 36980565
At first glance, it looks fine. Are you getting errors?

I notice the scope of the variable is Start. Is that the name of your package?
0
 

Author Comment

by:gcrickman
ID: 36980590
[Execute SQL Task] Error: Executing the query "SELECT COUNT(*) as CustImporErrors  FROM DELTEK.GT..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
0
 
LVL 11

Expert Comment

by:Simone B
ID: 36980855
That error is generally pretty vague. Some options:

From your screen shot, the query looks fine, but your last post showing the error has a typo in the field alias. It's missing a t in the name.
OR
Are you using parameters as well in your query?
OR
Check that the scope of your variable is at the package level. Also check the variable's data type. I would use Int32.
0
 
LVL 11

Expert Comment

by:Simone B
ID: 36981190
You have 3 Exec SQL tasks, which are essentially doing the same thing. Because most of the data flow is of type Success, it is entirely possible that 2 or more of your exec sql's are executing at the same time. It would be a good idea to use a different variable for each task.
0
 
LVL 11

Expert Comment

by:Simone B
ID: 36981387
One other observation. I've never seen a user variable without the prefix

User::

In the result set, did you type in your variable name, or select it from the drop down list? It should be selected from the list.

 User::varName
0
 

Author Comment

by:gcrickman
ID: 36981418
I had just copied from the first SQL task. But during debugging I was placing a breakpoint before the others would be run. So to clean things up I have created three different variables. But I still get the same error. I noticed on the Parameter Mapping tab. I removed that and it seems that it has gotten past that. Working on an object error now , even though the table exists.
0
 

Author Comment

by:gcrickman
ID: 36981430
Can the Result Name be the same as the Variable Name
0
 
LVL 11

Expert Comment

by:Simone B
ID: 36981537
Yes, the Result Name can be the same as the Variable Name. But you should have the User:: prefix in your variable name. I.e. Result Name = CustImportErrors, Variable Name = User::CustImportErrors



"I removed that and it seems that it has gotten past that." Does that mean your SQL tasks are working now?
0
 

Author Comment

by:gcrickman
ID: 36982145
Ok I have gotten further. Everything is working. The variable is 2 but the email is not being sent.
Do I have the data flow expression correct.
 expression
0
 
LVL 11

Expert Comment

by:Simone B
ID: 36982209
Looks good to me. What happens when you click Test?

Also, perhaps you have no errors? The mail task will only run if your error count is > 0.
0
 

Author Comment

by:gcrickman
ID: 36982284
Test says that is it ok. The value of CustImportErrors is 2 but it doesn't get to Cust Import Error Email.
Nothing is being sent through the SMTP server so for some reason it is not getting past this expression.
0
 

Author Comment

by:gcrickman
ID: 36982612
It is the expression in the data flow between the Execute SQL Task and the Email Task that is causing the problem.
For some reason the expression is not working so that the Email Task is performed.
I can run the Email Task and get an email so I know the Email Task is setup correctly.
0
 

Author Closing Comment

by:gcrickman
ID: 36983270
Thank you so much. It turns out that the breakpoint I had set was not allowing the Send Email Task to fire until I stepped past it.
So all is well and working now.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question