Building Custom SSIS Components

Introduction

I’ve been using SSIS for a long time and have always been curious about developing custom sources and destinations.

After having a bit of a look through the MDSN documentation (http://msdn.microsoft.com/en-us/library/ms345161.aspx) last night I thought I would give it a go.

So over the next couple of blog posts I’m going to attempt to create a custom Connection Manager, Source and Destination for RabbitMQ.

RabbitMQ is a widely used message broker built on the Advanced Message Queuing Protocol (AMQP).

Hopefully at the end of this series we will have:

  • Connection Manager which can be used by both the source and destination components which connects to the RabbitMQ broker
  • Source component which can read from a queue
  • Destination component which can send messages to an exchange

You can follow the source code updates in my GitHub repository.

The Series

As the series goes on I will add links to each article here.

Useful Resources

MSDN Documentation: http://msdn.microsoft.com/en-us/library/ms345161.aspx

Developing a Custom Source Component: http://msdn.microsoft.com/en-us/library/ms136088.aspx

Developing a Custom Destination Component: http://msdn.microsoft.com/en-us/library/ms135899.aspx

SQLBits 8 – SSIS Custom Components: http://sqlbits.com/Sessions/Event8/SSIS_Custom_Componenets by Dave Ballantyne (I haven’t watched this yet)

Advertisements

SSRS Report Templates

In this post I will outline a very simple approach to creating a report template for your SSRS solution.

Getting Started

Create a new (or open an existing) reporting solution and add a new report, let’s call it _Template.rdl – this report will form the basis of our template.

Open this report and add any standard formatting or style that you want for all future reports. Maybe you have standard header/footers, e.g. all titles need to be blue and there should be a footer which shows the date the report was produced.

My template looks like this:

SSRS Sample templateHere you can see that I’ve added a header and footer, in the header I’ve added the ReportName built-in field into a textbox and applied some formatting to it.

The idea being that all future reports should use this template. Now, we could just instruct all the report developers that they need to open this file from the solution make some changes then do a “Save As”.  But  this is not exactly ideal.

Report Templates

Instead what we want is for this template to appear in the Add New Item dialogue which appears when the user right clicks Reports and selects Add New Item.

Add New Items

After some quick googling, you will soon discover that all we need to do is copy our template into a directory in the Visual Studio directory in Program Files:

For VS2010: C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

For VS2012: C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

This would be a good start, however what happens when the template is updated by another developer and checked into source control? The next time someone else does a “Get Latest” they will need to check if the Template has been changed and remember which directory to copy this file into.

A better way is to use symbolic links. In Windows this is achieved using the mklink command (http://technet.microsoft.com/en-us/library/cc753194(v=ws.10).aspx)

mklink "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject\My Report Template.rdl" "C:\dev\Reporting\_Template.rdl"

You can execute this from command prompt, just be aware that you will need to be running it as Administrator.

After running this command open the Add New Items dialogue, and you will see the new report appear as a template:

SSRS-Templates-3

Testing SSRS Reports

In this post I’ll outline how I used a project from Codeplex http://unittestssrs.codeplex.com to write tests against SSRS reports.

From the description from the project page: Allows testing reports deployed to reporting server 2008R2 or 2012 in Native or SharePoint mode. The tests cases are created in xml file in declarative form.

Getting Started

1. Download and extract the latest release from the Codeplex site  http://unittestssrs.codeplex.com

2. Open the Settings.xml file, in here you will need to set a few different options:

  • Path this is the path to the report server, it will probably look something like this: http://my-reportserver/ReportServer
  • Mode this can be Native or Sharepoint.
  • UserName and UserPassword these only need to be set if the report server requires authentication, otherwise you can keep the defaults
  • HttpClientCredentialType this will be either Windows or Ntlm

Configuring Tests

Test cases are written in the Settings.xml file. The basic structure is this:

<Settings>
  <ReportServer Path="[Path]" Mode="[Mode]" UserName="@UserName" UserPassword="@UserPassword" HttpClientCredentialType="[CredentialType]">
    <Folder Name="[NameOfFolderOnReportServer]">
      <Report Name="[NameOfReportInReportFolder]">
        <Params>
          <Param Name="[ParamFromReport]" Value="[Value]" />
          <!-- More parameters can be specified -->
        </Params>
        <TestCases>
          <!-- Test cases go here -->
        </TestCases>
      </Report>
    </Folder>
  </ReportServer>
</Settings>

A few notes:

  1. The settings for the <ReportServer> attributes are outlined in the Getting Started section.
  2. I had problems initially with my reports because there were a few in the same folder that had similar names, this caused the project to throw an exception. This I didn’t figure out until I downloaded the source code and stepped through line by line.

Setting up a sample report

At present the only supported test cases are to assert IsNotNull and AreEqual, however I noticed that the AreEqual assertion only works on numeric data. So forget trying to test if columns contain text, you can also only test data that appears in the body of the report. Which for most people probably isn’t going to pose any problems.

To write test cases you need to know or understand XPath, and how it relates to your SSRS reports.

All the reports that I’m testing use a Matrix (or Tablix as they are also known).

  1. As an example I created a new report which connects to an SSAS cube and pulls out four measures, I then added a matrix to the report and dragged in the measures from my data set, here is what my report now looks like:
    SSRS example report to test
    I have highlighted the following: data set, the matrix/tablix in the design window and Row Groups and Column Groups. These are important to note when it comes to writing the XPath for the test cases.
  2. The next step is to run the report, and then save the output as XML.SSRS-Testing-2
  3. Then open the XML output SSRS XML Report OutputIf you take a close look you will see a <Tablix2> node, this refers to the name that SSDT gave the Matrix when I added it to the report, you will also notice the <RowGroup_Collection> and <Column_Group> nodes which correspond to the highlighted sections in the first step. Using these XML output we can now write some tests!
  4. Open the Settings.xml file from the SSRS Unit Test project, you should already have the <ReportServer> node attributes configured, add a new <Report> node to the <Folder> section. To test the report I’ve created for this post I added this:
    <Report Name="SummaryReport_ForAutomatedTests"></Report>
  5. Next we add a <TestCases> node, so we now have:
     <Report Name="SummaryReport_ForAutomatedTests"><TestCases></TestCases></Report>

Now we are ready to add a <TestCase>

NB: You will also need to deploy the report to the report server and folder you specified in the Settings.xml file.

Writing Test Cases

This is where knowing a bit of XPath comes in handy, however we are helped a lot by having the report XML output.

Example: Checking that the Total matches the expected value (AreEqual):

Looking at the XML output I can see that Textbox14 corresponds to the field representing my Total. If I wanted to I could go back to SSDT and give this field a better name, but for now I’m happy keeping it as Textbox14. I also know from looking at the XML output that the value I expect to have returned is 3233.

So, I will add the following to the <TestCases> collection:

<TestCase Assert="AreEqual" Path="//Tablix2/RowGroup_Collection/RowGroup[1]/ColumnGroup_Collection/ColumnGroup[1]/Textbox14/@Total" Value="3233" />
  • Assert=”AreEqual” tells SSRS Unit Test that we want to check the value of a field.
  • Path=”//Tablix2/RowGroup_Collection/RowGroup[1]/ColumnGroup_Collection/ColumnGroup[1]/Textbox14/@Total” tells SSRS Unit Test how to traverse the XML to get the value of the field
  • Value=”3233″ is the value that we are expecting.

Example: Checking that the Answered matches the expected value (AreEqual):

Next up we can do the same thing for the Answered field in the report, once again looking at the XML output we can take a guess that Textbox25 corresponds to the Answered field, so can add the following:

<TestCase Assert="AreEqual" Path="//Tablix2/RowGroup_Collection/RowGroup[1]/ColumnGroup_Collection/ColumnGroup[1]/Textbox25/@Answered" Value="1000" />

Example: Checking that Failed has a value (IsNotNull):

Lastly, we can test that the failed field has a value (IsNotNull), by once more looking at the XML output we can see that Textbox27 corresponds to the failed field. Our test case will look like this:

<TestCase Assert="IsNotNull" Path="//Tablix2/RowGroup_Collection/RowGroup[1]/ColumnGroup_Collection/ColumnGroup[1]/Textbox27/@Failed" />

Test Output

We can now run SSRS Unit Test and if it is configured correctly, then it should produce a file called something like “TestSuite yyyy-mm-dd hh-mm-ss.xml”. This file looks a lot like the Settings.xml file, but with an extra field.

SSRS Testing OutputYou will see highlighted in the picture above that a Passed attribute has been added to each <TestCase> which we defined in the Settings.xml file, this will either say True or False. Depending on if the test case passes.

There is also an xls transform that you can apply to the test results xml file, which produces a nice html file.

SSRS-Testing-6
SSRS-Testing-5