question

Swordfish0102 avatar image
Swordfish0102 asked

How to send out user-unique emails

Hi, I need to send out an email to end users informing them that their reports are delayed. I want the email of each unique user to list the specific reports that they are subscribed to as well as the last run date/time. I have already got the list of emails, separated by report name and sorted by email address. Some people are subscribed to multiple reports. EG: Column 1: Email Address Column 2: Report name Column 1: x@ emailaddress.com Column 2: Report1 Column 1: x@ emailaddress.com Column 2: Report2 Column 1: x@ emailaddress.com Column 2: Report3 So, to x@ emailaddress.com, I want to send an email apologising for the delay and that the following reports will be sent out as soon as Reporting Services is switched on again. Report 1 Report 2 Report 3 Please let me know if anyone has any ideas??
ssrsreporting_servicessubscriptionsemailfailure
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This web site runs off of voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer leads to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Can you tell us a bit more about "... as soon as Reporting Services is switched on again..." We run reporting services and it's a rare thing indeed for it not to be running. Is it a routine thing to have it "switched off"?
0 Likes 0 ·
Sometimes the cubes we run are delayed. Because we do not want to send out old data in the reports, we disable the Reporting Services and enable it again when the cubes have caught up.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
I'd start by reading up on sp_send_dbmail http://msdn.microsoft.com/en-gb/library/ms190307.aspx and how it can embed or attach results to the emails that it sends. How you trigger these emails is another matter that you will need to consider - are you relying on the reporting services service status? If so then this is not really a job for SQL Server but for something closer to the OS - PowerShell perhaps can fill the gap but also start off SQL Server procedures / jobs etc ...
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

how do you intend to send the emails then? What is the reason for not using sp_send_dbmail? Do you have access (ie the relevant security levels) to any SMTP server? PS can create and send emails via a suitable SMTP server.
1 Like 1 ·
well, the details at that link show you how to embed data into the body of an email. This, to me, is the way that this might be handled most easily. If you cant use email then another solution might be required. I'd argue though that this business case is a reason to question why the decision has been taken have your server configured as it is - with Database Mail procedures disabled.
1 Like 1 ·
In my organization, sp_send_dbmail is not an option. Is there a way to link the tables I run in SSMS to SSIS and run emails from there? The emails will be triggered at a time of day when all reports should have been sent out already. In the Job Activity Monitor - sends data to msdb - it queues the job at the time it was meant to send out. The trigger will occur when the time logged in Job Activity Monitor is not on the same day as the time logged in the Subscriptions table (From ReportServer).
0 Likes 0 ·
I receive this error: "SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online." I am able to send emails via SSIS. But I am very new to SSIS and don't know my way around very well.
0 Likes 0 ·
My main question though is will I be able to send the data off to recipients - and the data that they receive is only relevant to them, i.e. lists the reports they would've received had the system been working properly.
0 Likes 0 ·
iainrobertson avatar image
iainrobertson answered
SSRS manages subscriptions through tables within its database. So you should be able to query these to return a set of reports that haven't been run. Have a look at this post, it has some example queries that you can run to get the information that you need: http://blogs.msdn.com/b/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx I would be tempted to create a view that shows the data that you need. Once you have a list of users and reports, you then need to do a bit of work in SSIS to get the emails together. I'd do the following: Declare a variable of type Object. Add an Execute SQL Task that executes a query to return the list of users that have missed reports, plus the other info (e.g. email address etc) that you might need, i.e. select distinct UserName, UserEmail from MyMissedReportsView. The ResultSet option needs to be set to "Full Result Set". Map the Result Set to the object variable created above. Create a for loop container that iterates through the recordset containing the list of users.On the "Collection" pane of the properties window, set "Enumerator" to "Foreach ADO Enumerator". Select your object variable created above as the "ADO source variable". Map the Result Set columns to variables. Join the Execute SQL Task to the container. Within this container, create a second Execute SQL Task and for loop container (using the method above) that returns the reports that the user currently being processed by loop 1 has missed. This recordset should be defined in terms of the user currently being processed by the outer loop, i.e. select ReportNames from MyMissedReportsView where User = TheCurrentlyProcessingUser. Map the report name to a variable. Add a script task to this container. Pass in the report name variable and concatenate the value to a separate variable to construct your report list. E.g. declare a variable named MyReportList and one named MyReport. In the script task do something like this: dts.Variables("User::MyReportList").Value = dts.Variables("User::MyReportList").Value & vbcrlf dts.Variables("User::MyReport").Value Once you've run out of report names, you're ready to send your email using a Send Mail Task item and the various variables that you've populated above. Hopefully this makes sense and gives you enough of the dots to be able to join them up. I'm happy to explain further if needed.
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

No worries, I'd skimped on detail for the sake of brevity. And no need to apologise - we all start somewhere :) I'll add an enhanced answer in a little while.
1 Like 1 ·
I already have the list of emails and report names related to the failures. Thanks everyone for your help - I am going to play around with these answers tomorrow and see if I can get anywhere! I will report back with my results as soon as possible.
0 Likes 0 ·
You mentioned, 'Once you've run out of report names' - I have over 400 reports that get sent out daily. Do I need to create a loop for each one? The loops are confusing me - would you mind elaborating a bit more? I am completely new to SSIS - sorry for my lack of knowledge :(
0 Likes 0 ·
Thanks - I appreciate that!
0 Likes 0 ·
iainrobertson avatar image
iainrobertson answered
I'm assuming that you have a collection of data that looks a little something like this: select * from dbo.MyMissedReportsView /* UserId UserName UserEmail ReportName 1 Bob bob@address.com Sales Report 1 Bob bob@address.com Absence Report 2 Sue sue@address.com Sales Report 2 Sue sue@address.com Absence Report 2 Sue sue@address.com Payments Report */ What we're going to do is: 1) Gather a list of distinct users that have missed reports. 2) Loop through this list row by row. 3) For each row in the users list, gather a list of reports that they have missed. 4) Concatenate these report names to a single string, with carriage returns between each report name so that they come out as a list. 5) Generate a message body. 6) Send the email. You should end up with a package that looks something like this (without any red error flags): ![alt text][1] You'll need the following variables: ![alt text][2] Step by step: **Execute SQL Task - Get User List** Returns the distinct list of users that have missed reports, i.e. select distinct UserId, UserName UserEmail from dbo.MyMissedReportsView /* UserId UserName UserEmail 1 Bob bob@address.com 2 Sue sue@address.com */ On the general tab of the task editor for the task, set ResultSet to "Full result set", set SQLSourceType to "Direct Input" (which is the default anyway) and set SQLStatement to "select distinct UserId, UserEmail from dbo.MyMissedReportsView" (or the equivalent for your environment). On the Result Set tab, you'll need to output to an object variable in the package. Use the "Add" button to add a new mapping. Then set the following: ResultName VariableName 0 User::UserList This makes the result dataset available to tasks further down the line. **Foreach Loop Container - Process Users** Runs through the user list row by row. All items within this container will execute once for each user in the list. On the collections tab of the task editor, set Enumerator to "Foreach ADO Enumerator". Set the ADO object source variable to be the object variable populated above. On the Variable Mappings tab, you'll need to map the columns in the input to variables in the package. You should end up with something like this: Variable Index User::CurrentUserId 0 User::CurrentUserName 1 User::CurrentUserEmail 2 For each row in the input set that we process, these variables will be populated with the values contained within that row. **Script Task - Clear Report List Variable and Set Query** This task clears the contents of User::MissedReportsList and sets the value of User::MissedReportsQuery. On the script tab of the task editor, add the User::MissedReportsList and User::MissedReportsQuery variables to the ReadWriteVariables list, separate with commas. Click the "Design Script" button. Add the following script lines: Dts.Variables("User::MissedReportsQuery").Value = "select ReportName from dbo.MyMissedReportsView where UserId = " & Dts.Variables("User::CurrentUserId").ToString Dts.Variables("User::MissedReportsList").Value = "" Close the script and its task editor. On running the package, for each row a specific query is generated. For Bob, your query will be this: select ReportName from dbo.MyMissedReportsView where UserId = 1 **Execute SQL Task - Get Missed Reports for User** This gets a list of reports that the currently processing user has missed. Use the same logic as above, with one difference - use the User::MissedReportsQuery variable as the SQLStatementSource. **Foreach Loop Container - Build Report List String and Mail** This loops through only the reports missed by the current user. So for Bob, the input set will contain: select ReportName from dbo.MyMissedReportsView where UserId = 1 /* Sales Report Absence Report */ Use the same setup as above, using the Execute SQL Task - Get Missed Reports for User output and the relevant variables. **Script Task - Build Report List** See if you can work this out using the example given above. **Script Task - Set Message Body** See if you can work this out using the example given above. **Send Mail Task** Use expressions to map mail attributes to package variables. Hopefully that's a bit clearer. Have a crack and feel free to ask if you need any more clarification. Useful links: http://msdn.microsoft.com/en-us/library/cc280492.aspx http://msdn.microsoft.com/en-us/library/ms141689.aspx http://msdn.microsoft.com/en-us/library/ms135941.aspx http://msdn.microsoft.com/en-us/library/ms137547.aspx Edits: Clarity and Missed Variable from List [1]: /storage/temp/1647-package.jpg [2]: /storage/temp/1651-variables.jpg

package.jpg (34.0 KiB)
variables.jpg (26.2 KiB)
28 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is absolutely awesome. I am working through this now but I am getting an error while trying to do the Script Task Editor. I do not have UserId or UserName, only UserEmail, so I have used the following script: Dts.Variables("User::MissedReportsQuery").Value = "select ReportName from dbo.tblMissedReports where Email_addresses = " & Dts.Variables("User::CurrentUserEmail").ToString; Dts.Variables("User::MissedReportsList").Value = ""; However, it produces an error reading: Scripts Contained in the package have compilation errors. Do you wish to save changes? Any idea what I am doing wrong?
1 Like 1 ·
Also, is this the only script I need to insert into the //TODO section of the script design editor?
0 Likes 0 ·
Perhaps the semi-colons are causing the problem? Within the script editor, any problematic statements are underlined in blue. Have a look and see if anything is showing this. Your script should show something like this: Public Sub Main() ' ' Add your code here ' Dts.Variables("User::MissedReportsQuery").Value = "select ReportName from dbo.tblMissedReports where Email_addresses = " & Dts.Variables("User::CurrentUserEmail").ToString Dts.Variables("User::MissedReportsList").Value = "" Dts.TaskResult = Dts.Results.Success End Sub
0 Likes 0 ·
Oh, one other thing - because you're using email address as the key, you'll need to make sure that your query has quotes around the value: ...where Email_addresses = ' " & Dts.Variables("User::CurrentUserEmail").ToString & " ' " - spaces around single quotes added for emphasis, don't include them in your script.
0 Likes 0 ·
I am using Visual Studio 2012 and it seems to default use public void Main () as opposed to Public Sub Main () - could this have an influence? Also, it seems to be wanting me to add a semi colon at the end of each line (i.e. after .ToString, .Value="") I am going to post a screenshot now.
0 Likes 0 ·
Show more comments
iainrobertson avatar image
iainrobertson answered
You need to cumulatively add the report names to the string. I suspect that you have something like (just showing variable names for clarity): User::MissedReportList = User::ReportName + Environment.NewLine What you need is: User::MissedReportList = User::MissedReportList + User::ReportName + Environment.NewLine A useful tip is to use messageboxes within the script to show the values of variables at the point of execution: MessageBox.Show(dts.Variables["User::MissedReportList"].Value);
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Swordfish0102 avatar image
Swordfish0102 answered
I have added a script task to bring up a message box and have positioned it next to the very first execute sql task. I have disabled everything else, and it brings up the message box saying this: ![alt text][1] [1]: /storage/temp/1660-untitled.jpg This is the same message I am getting in my email. So the error is carrying all the way through. When viewing the execute sql task editor, looking at the build query, the query returns the correct results here. Weird.

untitled.jpg (111.4 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you post your script too please?
0 Likes 0 ·
Swordfish0102 avatar image
Swordfish0102 answered
**For Execute SQL Task - Get Report User List:** SELECT DISTINCT Email_Addresses, ReportName FROM tblMissedReports Result Set to User:UserList **Script task to bring up message box:** MessageBox.Show(Dts.Variables["User::UserList"].Value.ToString()); **Foreach Loop Container - Process Users:** Collection - Foreach ADO Enumerator ADO Object source variable = User:UserList Variable Mapping = User:CurrentUserEmail **Execute SQL Task** SQLStatement = Select ReportName From dbo.tblMissedReports ResultSet (Single Row): User::MissedReportsList **ForEachLoop Conainer - Build Report List and Mail** Collection: Foreach From Variable Enumerator Variable: User:MissedReportsList Variable Mappings: User::MissedReportsQuery **Script Task - Build Report List** Dts.Variables["User::MissedReportsList"].Value = Dts.Variables["User::MissedReportsList"].Value + Environment.NewLine + Dts.Variables["User::MissedReportsQuery"]; **Script Task - Set Message Body** Dts.Variables["User::MessageBody"].Value = "The following reports are delayed" + Environment.NewLine + Dts.Variables["User::MissedReportsList"].Value; **Send Mail Task** MessageSource = User::MissedReportsQuery ![alt text][1] [1]: /storage/temp/1662-untitled.jpg I know it's not exactly like the one you had set up in the beginning. Sorry to be such a nuisance the whole time :P Please let me know where in this chaotic mess I have made the mistake. Donovan

untitled.jpg (52.0 KiB)
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

No worries, I'm having fun :) I can see a few problems: Script task to bring up message box: MessageBox.Show(Dts.Variables["User::UserList"].Value.ToString()); User::UserList is an object variable. As such it doesn't have a value. If you messagebox out the value of User::CurrentUserEmail *within the for loop container* you should see the current email address value. ====================== Execute SQL Task SQLStatement = Select ReportName From dbo.tblMissedReports ResultSet (Single Row): User::MissedReportsList Result set needs to be full result set. You need to map this to another, separate object variable, not to the string variable User::MissedReportsList You also need to assign the value of ReportName to your User::ReportName variable. IMPORTANT: At present, you are just running the same query each time because you've missed a step. Before this, add a script task to generate the user specific SQL query as in my example. ====================== Cont...
1 Like 1 ·
@iainrobertson, I'd give you +10 if I could for this extended support session. Great work!
1 Like 1 ·
Script Task - Build Report List Dts.Variables["User::MissedReportsList"].Value = Dts.Variables["User::MissedReportsList"].Value + Environment.NewLine + Dts.Variables["User::MissedReportsQuery"]; You should be concatenating the report name mapped above, not the query used to generate the list of reports. ======================= Also, your two final tasks need to be within the for loop container. This is because you need to send an email per user. If these stay outside, they will only execute once the for loop has finished. Try setting up your package so that it looks *exactly* like my original example. Then start to configure what you can from top to bottom.
0 Likes 0 ·
Swordfish0102 avatar image
Swordfish0102 answered
"IMPORTANT: At present, you are just running the same query each time because you've missed a step. Before this, add a script task to generate the user specific SQL query as in my example." "select ReportName from dbo.MyMissedReportsView where UserId =" - you stated this in your original post. However, I am unsure about this. Can a query work in the middle of a string? Would it not be better to use expressions to limit the results to the currentemail address?
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

lol, yea it emails me now saying this: select ReportName from dbo.tblMissedReports where UserId =Donovan.Swordfish@emailaddress.com
0 Likes 0 ·
And if I put the email task in the loop, it emails me continuously. And by that I mean, like 1 email a second lol
0 Likes 0 ·
Ok, the latest: It is emailing me this: Reporting Services has been disabled due to data loads being delayed. The following reports will be sent out as soon as possible: System.Object 48 48 The last 3 lines are meant to be the names of two reports. I am going to go through this again and see if I can find the mistake. This is when the first script task is disabled. When it is enabled, is leaves out the System.Object and one of the 48's. (random number? Yea.)
0 Likes 0 ·
What does your script to set the User::MissedReportsList variable look like? > And if I put the email task in the loop, it emails me continuously. This suggests that your query to generate the list of users isn't right or that your loop isn't right. How does your process flow look now? Can you post a screen shot again?
0 Likes 0 ·
Swordfish0102 avatar image
Swordfish0102 answered
![alt text][1] [1]: /storage/temp/1664-untitled.jpg **Script Task - Clear Report List**: Dts.Variables["User::MissedReportsQuery"].Value = "select ReportName from dbo.tblMissedReports where UserId =" + Dts.Variables["User::CurrentUserEmail"].Value; Dts.Variables["User::MissedReportsList"].Value = ""; Execute SQL Task - Get Missed Reports SELECT ReportName FROM table Result Set: User::ReportName (DataType = Object) **ForEachLoop Editor** Variable Enumerator Variable = User::MissedReportsList Vairable Mappings: User:ReportName (Gives same result if I make this User::MissedReportsList) **Script Task - Build Report List** Dts.Variables["User::MissedReportsList"].Value = Dts.Variables["User::MissedReportsList"].Value + Environment.NewLine + Dts.Variables["User::ReportName"].Value; **Set Message Body** Dts.Variables["User::MessageBody"].Value = "Reporting Services has been disabled due to data loads being delayed." + Environment.NewLine + "The following reports will be sent out as soon as possible:" + Environment.NewLine + Dts.Variables["User::MissedReportsList"].Value;

untitled.jpg (46.8 KiB)
16 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry for the delay in getting back to you - UK public holiday yesterday. I'm very busy today managing some work for my current client, so I'll only be able to get back to this fully tomorrow. A few things that are immediately obvious: User::ReportName should be type string, not object. This variable is used to hold the name of each missed report for each iteration of the inner loop. User::MissedReportsList should also be type string, not object. This variable is used to collect the report names for putting into the email. You need to create a *separate* object variable to get the list of reports that the user has missed. This is the object variable that you pass into the inner loop. You'll populate this variable at the "Execute SQL Task - Get Missed Reports" step. Your inner loop needs to be of type Foreach ADO Enumerator. The ADO object source variable should be your separate object variable mentioned above.
1 Like 1 ·
Public holidays for me generally involve my wife attempting to work me to death with a long list of jobs to complete. So I'm happy to be back at work :) It looks like you haven't mapped the SQLStatementSource within the "Execute SQL Task - Get Missed Reports" task. To map the generated sql statement you'll need to do the following: Change SQLSourceType to "Variable" Choose User::MissedReportsQuery from the SourceVariable list (this will appear when you change the SQLSourceType) To map the "to" line in the Send Mail Task, you'll need to use an expression. Here's how: Select "Expresions" in the task editor. Click in the box on the right hand pane, next to where it says "Expressions". This will show an ellipsis box (...). Click this. A new dialog will appear. In this new dialog, select "ToLine" on the "Property" side and then click the ellipsis box next to the "Expression" box. Another dialog will appear. In this new dialog, drag your User::CurrentUserEmail variable from the variables list to the "Expression" box. Ok everything. You should then be good to go.
1 Like 1 ·
I don't think so. You need to concatenate the single quotes into your string: "where Email_addresses = '" + Dts.Variables["User::CurrentUserEmail"].Value + "'";
1 Like 1 ·
Having seen how that displays, it looks a bit confusing. It should be like this: "where Email_addresses = [[single quote]][[double quote]] + Dts.Variables["User::CurrentUserEmail"].Value + [[double quote]][[single quote]][[double quote]];
1 Like 1 ·
![alt text][1] [1]: /storage/temp/1666-untitled3.jpg
0 Likes 0 ·
untitled3.jpg (60.6 KiB)
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.