SQL Inquirer or SQL Developer?

There is a big difference between being able to inquire data from SQL and being a SQL Developer, and this is something not everyone understands. SSRS/SSIS/SSAS or the SQL full stack requires more than just knowing how to create report templates, ETL packages or building a cube. In fact, SQL full stack performs better and provides better results when the back-end that supports them is thicker or stronger, with stored procedures/views/functions managing the main business logic and maintaining the SQL full stack thinner. But for being able to create all that magic in the back-end, a SQL Inquirer does not suffice, you need to have the skills of a SQL Developer.

I've had the opportunity to use those skills in several requests, and those opportunities are the ones I enjoy so much because I love coding, and yes, I am from the old school, I learned to code using Basic, Turbo Pascal, Fortran, Cobol, and others back at the end of the 80s. But it was around 1995 when I was introduced for the very first time to SQL working with Oracle databases and since then I enjoy a lot coding in the back-end.

During all these years I have met many people that are asked if they can create a certain format in SSRS and they will answer 'NO', and they are correct because SSRS by itself does not provide all the 'awesome' ideas that each final user has in mind, BUT, when you know how to code in T-SQL your answer should always be 'YES'. I've always stated to my IT clients/users that everything is possible if we have the data available and SQL Server because the rest only depends on my abilities on both ends.

Recently we were asked to create a report in SSRS that would print a general information of branch offices (branch name, address, city, state, and zip code), data must be grouped by State, sorted by branch name and the final template would print 4 branches per page but in 2 columns, filling out the first column and then the second one from top to bottom and from left to right. After looking for solutions in SSRS and seeing all the restrictions when using multicolumn layouts we decided to create a stored procedure that returns the data already sorted in the two columns and the solution is as follows:

The results the user needed were as follows (each line represents a page break):

State 1

Branch 1                                                                Branch 3

9999 Street Address Branch 1                              9999 Street Address Branch 3

City, State1                                                           City, State1

Zip Code                                                               Zip Code

Branch 2                                                                Branch 4

9999 Street Address Branch 2                              9999 Street Address Branch 4

City, State1                                                           City, State1

Zip Code                                                               Zip Code

__________________________________________________________________________

State 2

Branch 5                                                                Branch 7

9999 Street Address Branch 5                              9999 Street Address Branch 7

City, State2                                                           City, State2

Zip Code                                                               Zip Code

Branch 6                                                               

9999 Street Address Branch 6                                

City, State2                                                          

Zip Code                                                               

__________________________________________________________________________

State 3

Branch 8                                                               

9999 Street Address Branch 8                                

City, State3                                                          

Zip Code             

__________________________________________________________________________

                                                  

The solution was achieved using the code below in a stored procedure:

create proc [dbo].[Test1]

as

begin

create table #tmp

(rowid int identity,

[state] char(2) null,

value1 char(20) null,

value2 char(50) null,

value3 char(20) null,

value4 char(20) null

);

-- Generate the data

declare @i int = 1;

while @i <= 25

begin

insert into #tmp (value1, value2, value3, value4)

values ('Branch ' + convert(varchar(20), @i), '9999 Street Address Branch - ' + convert(varchar(20), @i),

'City', 'Zip Code')

set @i = @i + 1;

end;

-- Assigns the states

update #tmp

set [state] = 'AL'

where rowid = 1

update #tmp

set [state] = 'TX'

where rowid between 2 and 8

update #tmp

set [state] = 'CA'

where rowid between 9 and 17

update #tmp

set [state] = 'NM'

where rowid between 18 and 24

update #tmp

set [state] = 'FL'

where rowid > 24

--select * from #tmp

-- Arrange the data in 2 sets of branches per row

select rowid as NewRow, t.[state],

t.value1 as Col1, t.value2 as Col2, 

t.value3 as Col3, t.value4 as Col4, 

(select x.value1 from #tmp x where x.rowid = t.rowid+2 and x.[state] = t.[state]) as Col5,

(select x.value2 from #tmp x where x.rowid = t.rowid+2 and x.[state] = t.[state]) as Col6,

(select x.value3 from #tmp x where x.rowid = t.rowid+2 and x.[state] = t.[state]) as Col7,

(select x.value4 from #tmp x where x.rowid = t.rowid+2 and x.[state] = t.[state]) as Col8

into #tmp2

from #tmp t

--select * from #tmp2

-- create final tmp table

select cast(null as int) as NewRow, [state], 0 as [pagenumber], 

Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8 

into #tmp3 

from #tmp2 

where NewRow is null

declare @ii int=1, @state char(2), @pagenumber int = 1;

declare c_state cursor for

select distinct [state] from #tmp2

open c_state

fetch next from c_state into @state

-- Fetch the different states

while @@FETCH_STATUS = 0

begin

select @ii = min(NewRow) from #tmp2 where [state] = @state;

-- Remove the rows already existing in the second set of data

while @ii <= (select max(NewRow) from #tmp2 where [state] = @state)

begin

insert into #tmp3

(NewRow, [state], [pagenumber], Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8)

select NewRow, [state], @pagenumber, Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8 from #tmp2 where NewRow in (@ii, @ii + 1) and [state] = @state;

set @pagenumber = @pagenumber + 1;

set @ii = @ii + 4;

end;

fetch next from c_state into @state

end;

close c_state;

deallocate c_state;

select * from #tmp3 order by NewRow

end;

The stored procedure returns the following:

The SSRS format is as simple as shown below. One table with two columns, each column in the SSRS grid displaying the 4 columns in the results above per each branch.

Each group defined by the State and the Page Number:

And the Page Breaks as follows:

The Preview of the report will display:

By keeping it simple in the front and writing the majority of the complications of a specific format in the back allows you to achieve faster and better results plus make it easier for maintenance.

So, are you a SQL Inquirer or a SQL Developer?

Thanks!

Render SSRS Report into PDF file from SSIS package

This solution exemplifies a simple way to render a report into PDF/XLS/CSV file and then email it to a variable list of recipients. The solution uses SSRS, SSIS and some code in C#. This can be used instead of the SSRS subscriptions and offers more flexibility in setting up the list of recipients without the need of having the SQL Server Enterprise version.

Step 1. Create a new SSIS package using Visual Studio 2008/2012/2014. I have tested the same process in all the 3 versions, they have some differences on the way to deploy the packages but that part is not explained in this brief guide.
Step 2. Include the following objects in your new package:

a. Email Recipient. It is an Execute SQL Task used to get the data needed to create the email per each Recipient.
b. Email Report Cycle. It is a Foreach Loop container required to fetch the data for each recipient.
c. Extract and Send Report. It is a Script Task used to render the SSRS report and email it to each Recipient.
d. Expresion Task. It is used to assign a value to a SQL statement that is executed in the last step.
e. Email Succeeded. It is an Execute SQL Task used to store in one table the emails that were succesful for future reference.






Step 3. Define the variables as displayed in the following pic, but please notice that this definition needs to include as well the variables to get the parameters used for your SSRS report that will be rendered in future steps. In this case the report will use the LoginID as a unique parameter.

Step 4. Email Recipient - Open the editor of the first Execute SQL Task and configure it according to your parameters. In this case, the task is receiving five columns: LoginID, UserName, EMail, EMailFrom, and EMailBody, and all the Data Set is loaded into the Object variable named as User::RecipientList. In this list of variables, you need to include as well the values for the parameters needed for your report in SSRS, in this case, the report is using only the LoginID as a parameter.

Step 5. Specify the Data Set destination variable as shown below:

Step 6. Email Report Cycle - Double click on the Foreach object to open the editor. Then follow the screens below to configure it:

Select Foreach ADO enumerator and the User::RecipientList as the ADO source variable. Then move to the section of Variable Mappings and Add each variable in the order they come in your first Execute SQL Task on Step 4.

Step 7. Extract and Send Report - These step will guide you on how to configure the Script Task to render the report, create the PDF file in memory using a Stream, attaching the PDF file to an email and send it out. Double click on the Script Task to open the Editor.

In the ReadOnlyVariables row add the five variables defined previously (LoginID, UserName, EMail, EMailFrom, and EMailBody). Remember that in this step you need to include the variables with values for the parameters required by your SSRS report. Click on the "Edit Script" button and the VS Script Editor for C# will be opened. As you may know, this Script Editor is prefilled with the basic lines requiered by an script, so once opened, follow the instructions described below:

Right-Click on the References section in the Solution Explorer Panel, then select "Add Service Reference..." This will open the following screen:

Click on "Advanced..." This will open the following screen:

Click on "Add Web Reference..." This will open the following screen:

In the URL line specify the following: http://{Replace with your Report Server Web Service URLs}/ReportExecution2005.asmx Then Click on the green icon and if the right URL was specified all the available Web Services will be listed. Name your Web Reference and Click on the "Add Reference" button. This will add the Web Reference to your project and now you are able to use them. Now, copy the following code to your script in the appropiate sections and replace with your own values were asked:

#region
Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Net.Mail;
using System.Web.Services.Protocols;
using {Add the web reference to your Web Service};
#endregion

public void Main()
{
// Initiating report execution properties
ReportExecutionService rexec = new ReportExecutionService();
rexec.Credentials = System.Net.CredentialCache.DefaultCredentials;
rexec.Url = "http://{Replace with your Report Server Web Service URLs}/ReportExecution2005.asmx";

// Render format e.g. MHTML/HTML4.0/PDF/EXCEL/IMAGE/CSV byte[]
result = null;
string reportPath = "{Replace with the SSRS Report Path}";
string format = "PDF";
string historyID = null;
string devInfo = @"False";

// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[3];
parameters[0] = new ParameterValue();
parameters[0].Name = "{Replace with the parameter name as it is defined in your report}";
parameters[0].Value = Dts.Variables["{Replace with the variable name defined in your SSIS package}"].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");
Console.WriteLine("SessionID: {0}", rexec.ExecutionHeaderValue.ExecutionID);
try
{
result = rexec.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
execInfo = rexec.GetExecutionInfo(); Console.WriteLine("Execution date and time: {0}", execInfo.ExecutionDateTime);
}

catch (SoapException e)
{
Console.WriteLine(e.Detail.OuterXml);
Dts.Events.FireError(0, "Error ", 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 = {Replace with your file name or any name convention you want to use} + ".pdf";
try
{
System.IO.MemoryStream ms = new System.IO.MemoryStream(result);
string htmlMessageTo = Dts.Variables["User::EMail"].Value.ToString();
string htmlMessageFrom = Dts.Variables["User::EMailFrom"].Value.ToString();
string htmlMessageSubject = {Replace with the subject you want to use};
string htmlMessageBody = Dts.Variables["User::EMailBody"].Value.ToString();
string smtpServer = {Replace with the name of your smtp Server};
SendMailMessage(htmlMessageTo, htmlMessageFrom, htmlMessageSubject, htmlMessageBody, true, smtpServer, ms, reportFile);
ms.Dispose();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Dts.Events.FireError(0, "Error", e.Message + "\r" + e.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}

Dts.TaskResult = (int)ScriptResults.Success;

}

private void SendMailMessage(string SendTo, string From, string Subject, string Body, bool IsBodyHtml, string Server, MemoryStream ms, String reportFile)
{
MailMessage htmlMessage;
SmtpClient mySmtpClient;
htmlMessage = new MailMessage(From, SendTo, Subject, Body);
htmlMessage.IsBodyHtml = IsBodyHtml;
Attachment attach = new Attachment(ms, reportFile, System.Net.Mime.MediaTypeNames.Application.Pdf);
htmlMessage.Attachments.Add(attach);
mySmtpClient = new SmtpClient(Server);
mySmtpClient.Send(htmlMessage);
htmlMessage.Dispose();
}

Step 8. The last two steps of the project are only used to create a new row in a table that stores details of the successful emails, but they are not explained in detail here. But with these steps your files should be good to go.

Step 9. Deploy your package, the deployment process depends on the Visual Studio version you are using.

Step 10. To schedule the execution of this process you can use the SQL Job Agent, create a new job and call your SSIS package that has been previously deployed then schedule your new SQL job to run according to your requirements.

Let me know your comments, questions or findings.

Thanks!