SSIS and Reporting Services Web Services
This post covers accessing web services in an SQL Server 2008 SSIS package, in particular the web service provided by Reporting Services 2005 in both MSRS 2005 and MSRS 2008. I have a need to generate reports on a schedule and/or on an event driven basis from Reporting Services based on a number of outside conditions. I could use Reporting Services 2008 Enterprise Edition which has data driven subscriptions but this falls short in the event driven category and will run the report regardless based on a schedule only. This post is more or less a walkthrough of the ReportService web service based on applied to SQL Server 2008 SSIS Script task. The information below works in both Data Flow and Control Flow Script tasks. (Read: there is nothing earths shaking going on here but you need to start somewhere and being familiar with the basics is going to help us in the long run.)
Reporting Services exposes a number of web services classes in SQL Server 2005 and SQL Server 2008 Reporting Service. For this post we will be looking into the web service namespace. For future reference there is a ReportExecution, ReportService2006 and ReportServiceAuthentication web service name spaces that are available for use in both 2005 and 2008 of Reporting services.
Before we can get into building our report delivery engine we need to get a feel for how to interact with the Reporting Services programmatically. We will do this by setting up a simple SSIS package that outputs basic properties from a given report on a Reporting Services environment. This is not as simple as dragging the web service component from the tool bar on to the control flow tab and typing in the URL of the web server. Unfortunately the SSIS web service task is incompatible with the level of control we need to have to successfully execute our goal.
Step 1 – Drag the Script Task onto the control flow. Note most of what I am going to show you can be done exactly the same inside a data flow task as a data source, transformation, or destination.
Step 2 – Ok, not really a step but I want to make that point that the script language drop now shows and supports C#.
Step 3 – Once the script is open left click on the reference section and choose Add Web Reference. Note the namespace of this particular script task. You will need to reference this later in the Using Statement to properly reference the web service.
Step 4 – Put in the URL of your reporting services server followed with reportservice2005.asmx?wsdl to get the WSDL for the ReportService2005 web service class. Then rename the web reference name to something meaningful to you, ReportService2005 is a good choice. (and follows along with the MSDN documentation example if you need a really good reason)
WSDL URL Example: http://FQDN:8080/ReportServer_SQL2008/reportservice2005.asmx?wsdl
Note: I did have issues referencing the https version of the WSDL from my local machine inside visual studio, so I went with the http reference.
Step 5 – Reference the name space by adding your script tasks name space along with name of the web service.
Step 6 – Code Explanation:
Ok here is a little detail on what we are doing first we declare a Boolean fireAgianFlag for use later in a fire Information event. Next we declare a new instance of the class. Once we have created an instance we set its credentials and URL properties. The URL is set to the instance of Reporting Services running on my local machine, pointing to the reportservice2005.asmx page. Next we create two new instances of the property class set the name properties to Description and Name and add them to an array of property objects. The get properties method will fill any property array sent to it filling the value property of the property array with information that corresponds to the value in the name of that particular property object. (So basically we have an array that has two objects each object is a property object. The property object has two properties of its own name and value. I only mentioned this because the word property is use a whole lot in the above few sentences and reads a little funny to me so I expect it may be a little confusing for everyone else.) After the property array is created and set with the appropriate values it is used in the call to the ReportService classes’ method. This method fills the property array with information for a given report, in this example the ConnectionInfo in the StevesReportingExamples folder of the root of the ReportServer directory. We then loop through the property array and show the name and value of each property we have retrieved data for.
Step 7 – Running the package you see that I have a number of property names and there values on the right hand side outputted by the fire information event. I have a few more than those shown in the code screen shot above because I added a number of additional property objects to the property array. The code for the additional properties is below, its worth to note there are a few more properties specific to reports, folders, and objects that are available but not shown.
Additional valid property objects names and values:
//Item(Generic) Properties
Property[] properties = new Property[12];
Property name = new Property();
Property description = new Property();
Property CreatedBy = new Property();
Property CreationDate = new Property();
Property Hidden = new Property();
Property ID = new Property();
Property ModifiedBy = new Property();
Property ModifiedDate = new Property();
Property Path = new Property();
Property Size = new Property();
Property Type = new Property();
Property VirtualPath = new Property();
properties[0] = name;
properties[1] = description;
properties[2] = CreatedBy;
properties[3] = CreationDate;
properties[4] = Hidden;
properties[5] = ID;
properties
= ModifiedBy;
properties[7] = ModifiedDate;
properties
= Path;
properties[9] = Size;
properties[10] = Type;
properties[11] = VirtualPath;
//Report Specific Properties
Property[] Reportproperties = new Property;
Property Language = new Property();
Property ReportProcessingTimeout = new Property();
Property ExecutionDate = new Property();
Property CanRunUnattended = new Property();
Property HasParameterDefaultValues = new Property();
Property HasDataSourceCredentials = new Property();
Property IsSnapshotExecution = new Property();
Property HasScheduleReadyDataSources = new Property();
Reportproperties[0] = Language;
Reportproperties[1] = ReportProcessingTimeout;
Reportproperties[2] = ExecutionDate;
Reportproperties[3] = CanRunUnattended;
Reportproperties[4] = HasParameterDefaultValues;
Reportproperties[5] = HasDataSourceCredentials;
Reportproperties = IsSnapshotExecution;
Reportproperties[7] = HasScheduleReadyDataSources;
//Folder Specific Properties
Property[] Folderproperties = new Property[1];
Property Reserved = new Property();
Folderproperties[0] = Reserved;
//Resource Specific Properties
Property[] Resourceproperties = new Property[1];
Property MimeType = new Property();
Resourceproperties[0] = MimeType;
//Item(Generic) Properties
name.Name = "name";
description.Name = "description";
CreatedBy.Name = "CreatedBy";
CreationDate.Name = "CreationDate";
Hidden.Name = "Hidden";
ID.Name = "ID";
ModifiedBy.Name = "ModifiedBy";
ModifiedDate.Name = "ModifiedDate";
Path.Name = "Path";
Size.Name = "Size";
Type.Name = "Type";
VirtualPath.Name = "VirtualPath";
//Report Specific Properties
Language.Name = "Language";
ReportProcessingTimeout.Name = "ReportProcessingTimeOut";
ExecutionDate.Name = "ExecutionDate";
CanRunUnattended.Name = "CanRunUnattended";
HasParameterDefaultValues.Name = "HasParameterDefaultValues";
HasDataSourceCredentials.Name = "HasDataSourceCredentials";
IsSnapshotExecution.Name = "IsSnapshotExecution";
HasScheduleReadyDataSources.Name = "HasScheduleReadyDataSources";
//Folder Specific Properties
Reserved.Name = "Reserved";
//Resource Specific Properties
MimeType.Name = "MimeType ";
I hope that’s helpful and I’m pretty sure I’ll post a few more entries around this topic.