question

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 ...

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.

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

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);

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.

**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

"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?

![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;