Create a SSIS project or package with SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS). In this example, I am using Visual Studio 2012 equipped with Business Intelligence for Visual Studio 2012 (SSDT BI) templates (download here)
Create a Execute SQL Task by dragging one from the SSIS toolbox onto the control flow, and change the name to Extract Email Recipient. Create a Foreach Loop Coailntainer below it and create two Script tasks in the container, and rename the tasks to Extract Report and Em Report respectively. Connect the tasks. Here is how it should look like,
In the variables windows, create 5 variables; DepartmentID, RecipientEmail, RecipientName, RecipientResultSet and ReportFile like shown below. (Make sure the data type is Object for RecipientResultSet)
Configure the Extract Email Recipient (Execute SQL Task). Configure the data source connection and the SQL statement
Select the Result Set, enter 0 in the Result Name and select User::RecipientResultSet as Variable Name. This step map the query result to the variable.
The next step is to configure the for each loop. Double click the for each loop container to open the editor. Select Collection on the left pane. Change the Enumerator to Foreach ADO Enumerator. Select the User::RecipientResultSet in the ADO object source variable drop down list.
Select the Variable Mappings on the left pane. Add thee variables, User::RecipientEmail, User::RecipientName, User::DepartmentID and map them to index 0, 1, 2 respectively.
The next step is to configure the extract report script task. Double click the task to open the editor. Assign User::DepartmentID to the ReadOnlyVariables and User::ReportFile to ReadWriteVariables. Click Edit Script to add the script below.
This task utilize the Report Server Web Service to process and render report hosted in SQL Server Reporting Server via ReportExecution2005 endpoint. Open the Reporting Services Configuration Manger, and locate the Report Server Web Service URLs. Copy the address for later use.
In order to utilize the Report Server Web Service, we need to add a reference. Right click on References and select Add Service Reference. Click Advanced in the Add Service Reference window, and click Add Web Reference in the Service Reference Settings window.
In the Add Web Reference window, type or paste the Report Server Web Service URL with ReportExecution2005.asmx at the end of the string and click the arrow button. Give it Web Reference Name as TestReportExecution2005. Click Add Reference.
Add the web service reference namespace (change to your script namespace)
Type or copy the script (C#) below into the extract report script task.
Here is the final namespace.
#region Namespaces using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.IO; using System.Web.Services.Protocols; using ST_8ca6d57caecf488aa42b0ae8ff74f91d.TestReportExecution2005; #endregion
In the main function
// Initiating report execution properties
ReportExecutionService rexec = new ReportExecutionService();
rexec.Credentials = System.Net.CredentialCache.DefaultCredentials;
rexec.Url = "<Your Report Server Web Service URL>/ReportExecution2005.asmx";
// Render arguments
// Render format e.g. MHTML/HTML4.0/PDF/EXCEL/IMAGE/CSV
byte[] result = null;
string reportPath = "/TestRS/ProductReport";
string format = "MHTML";
string historyID = null;
string devInfo = @"<deviceinfo><toolbar>False</toolbar></deviceinfo>";
// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[3];
parameters[0] = new ParameterValue();
parameters[0].Name = "DepartmentID";
parameters[0].Value = Dts.Variables["User::DepartmentID"].Value.ToString();
string encoding = null;
string mimeType;
string extension;
Warning[] warnings = null;
string[] streamIDs = null;
ExecutionInfo execInfo = new ExecutionInfo();
ExecutionHeader execHeader = new ExecutionHeader();
rexec.ExecutionHeaderValue = execHeader;
execInfo = rexec.LoadReport(reportPath, historyID);
rexec.SetExecutionParameters(parameters, "en-us");
try
{
result = rexec.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
execInfo = rexec.GetExecutionInfo();
}
catch (SoapException e)
{
Dts.Events.FireError(0, "report rendering", e.Message, "\r" + e.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
//Write the contents of the report to a mht/html/pdf/csv/xlsx/tiff file to be attached in the email
string reportFile = "ProductListing.mht";
try
{
FileStream stream = File.Create(reportFile, result.Length);
stream.Write(result, 0, result.Length);
stream.Close();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
Dts.Events.FireError(0, "saving report file", e.Message, "\r" + e.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
//passing the filename for later attachment
Dts.Variables["User::ReportFile"].Value = reportFile;
Save the script and click OK on the task.The next step is to configure the Email Report script task. Double click the task to open the editor. Assign User::RecipientEmail, User::RecipientName, User::ReportFile to the ReadOnlyVariables. Click Edit Script to add the script below.
Here is the script (C#). The final namespace.
#region Namespaces using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.Net.Mail; using System.IO; using System.Text; #endregion
In the main function
MailMessage email = new MailMessage();
email.To.Add(Dts.Variables["User::ReceipientEmail"].Value.ToString());
email.From = new MailAddress("<your sender email address>");
email.Subject = "Product Listing";
email.Body = Dts.Variables["User::ReceipientName"].Value.ToString();
string reportFile = Dts.Variables["User::ReportFile"].Value.ToString();
try
{
//For MHT file. Decode MHTML to HTML and embed in email body
if (Path.GetExtension(reportFile) == ".mht")
{
var decodedHtml = new StringBuilder();
using (var reader = new StreamReader(reportFile))
{
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
if (line != "Content-Transfer-Encoding: base64") continue;
reader.ReadLine();
while ((line = reader.ReadLine()) != String.Empty)
if (line != null)
decodedHtml.Append(
Encoding.UTF8.GetString(
Convert.FromBase64String(line)));
break;
}
}
email.Body = email.Body + Environment.NewLine + decodedHtml.ToString();
email.IsBodyHtml = true;
}
//For regular html file
//html = File.ReadAllText(reportFile);
else
{
//Attach the file
Attachment attachmentFile = new Attachment(reportFile);
email.Attachments.Add(attachmentFile);
}
}
catch (Exception e)
{
Dts.Events.FireError(0, "create email message", e.Message, "\r" + e.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
SmtpClient smtpClient = new SmtpClient("<your exchange server>");
try
{
smtpClient.Send(email);
File.Delete(reportFile);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
Dts.Events.FireError(0, "smtp emailing", e.Message, "\r" + e.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
if (email != null)
{
email.Dispose();
}
if (stmpClient != null)
{
smtpClient.Dispose();
}
}
The script above embed the file if it is in MHTML format in the email body, and attach as a file otherwise. I am trying to imitate the same behavior as the data driven subscription. The MHT file (in MHTML format) is converted from Base64 into html and embed the html string in the email body.You may ask why not just render the report in HTML4.0 in the extract report script task, and just embed the html string? The problem is when the report is rendered directly in html, it is missing a few important properties like min-width and width. This result the table squashed when rendered in Microsoft Outlook like shown below.
However, when it is rendered in MHTML, the properties persist.
Both Manager A and Manager B should receive the email with the product listing report. Above is the example of email body received by Manager A. Notice that there are only 3 products which are from the Manager A's department, similar to data driven subscription. This method doesn't require a SQL Server Business Intelligence or Enterprise edition, but require more development works.
No comments:
Post a Comment