Video Screencast Help

Workflow Integration with SQL Reporting Services

Created: 23 May 2013 • Updated: 26 May 2013 | 7 comments
This issue has been solved. See solution.

Hello -

I would like to import data from a SQL Reporting Services (SSRS) report into Workflow.  It seems like this may be possible using SSRS SOAP API and Workflow's Web Service Caller Generator, but I'm not sure.  Does anyone know if its possible?  If so, can anyone provide me with the basic steps for doing this?

Thanks in advance, Tom

Comments 7 CommentsJump to latest comment

reecardo's picture

On the new project screen, specify a project name and select Integration project. On the next screen, specify a name for the generator and select Web Service Generator.

Now the web service generator is open. On the 1st screen, add the URL of the web service you'd like to generate against and click Next. On the 2nd screen, it's easiest to just accept the defaults and click Next. On the 3rd screen, select any (or all) methods you'd like to generate against and click Next. On the final screen, it's easiest to just accept the defaults and click Next.

Now that you're done with the generator, Compile and Close the project.

The instructions above are for standard web services. If these SQL web services are WCF services, use the WCF generator.

It's important to note that some web methods can be blocked from being generated against... for example if the method accepts input of type DataSet or XmlNode.

TomS's picture

Thanks reecardo -

I think I'm good from the Workflow side of things, but looking for more direction from the SQL Server Reporting Services side.  For example, I know what URL to use if I want to view the SSRS report in a Web browser, but I'm not sure what URL to use for the Web service.  If I use the same URL in Workflow it just gives me an error message so either I need to provide a different URL or SSRS doesn't support this integration.

Do you have any experience integrating Workflow with SSRS or know of anyone who has?

Thanks for your help on this,

TomS's picture

Hello All -  I've made some progress on this, discovering that SSRS has a Web Service interface different from the Web user interface.  Once I used Workflow's Web service generator wizard to point to the Web Service interface, it enumerated all of the available methods (see the ReportExecution2005.jpg image) and that part seems to be working fine.

I also found some good information from Microsoft on the methods needed to render a report...,v=sql.105).aspx

Now the problem I'm running into is when I try to use the Load Report Component (created as part of the Web service generator, and per the Microsoft article, the first step in rendering a report) Workflow requires me to provide a variable called the "Trusted User Header Value" (see the LoadReportComponent.jpg image).  I'm not familiar with this, but I can tell from the component's help function that the variable type is as follows:  Variable (TrustedUserHeaderHeaderStorage)

I'm guessing this is related to the HTTP session itself between the Workflow process and the SSRS Web service, but am wondering if any has any experience with this or insights to share.  Any/all thoughts are greatly appreciated.

Thanks, Tom

ReportExecution2005.jpg LoadReportComponent.jpg
Aryanos's picture

Hi Tom,

Just curious why you need the data from SSRS? can't you get the information directly from the database? Is there something specific that you're trying to do?

I like my beats fast and my bass down low

TomS's picture

Hi Aryanos -

Good question... to the extent that I've successfully developed SQL db generator WF components, I'd actually prefer to connect directly to the database to pull the information rather than via SSRS.  That said, the database is proprietary so (A) the schema is large and not easy to understand, and (B) the vendor won't support this integration.  Their solution comes with canned SSRS reports and they support development of customized reports using SSRS, so in terms of Workflow, for this solution I think integration with SSRS as opposed to SQL itself is the way to go.

I'm getting closer on this... I just discovered that Workflow supports a data type called TrustedUserHeaderHeader (see image), a complex variable that consists of (A) a user name, and (B) a user token.  The Load Report Component will accept a variable of this type, so now I'll try creating a user token via the Basic Authentication Token component and see if that works.

That said, still appreciate any/all insights.  Tnx, Tom

Aryanos's picture

Hi Tom,

Ah, thanks for letting me understand why you're approaching it that way. I've never tried to do it from a SSRS report as the applications that I've connected to always had some APIs or web services exposed. Sorry I can't help, but good luck with this project.

I like my beats fast and my bass down low

TomS's picture

Good news here... I got this to work!

A few notes for anyone looking to integrate SSRS with Workflow...

- I created a Workflow variable of type TrustedUserHeaderHeaderStorage using the Add New Data Element component.  This complex data type requires a user name and a user token.  For user name I'm using the name of the ID I with which I want to connect to SSRS, and for user token, the Workflow component seems prepopulated with a Byte[] Array value.  I don't fully understand this data type, but I didn't modify the Byte[] Array value from what the component provided and I'm not having any issues.  This variable is used as the value for the Trusted User Header Value in the Load Report Component (which is available after integration with the SSRS Web service).

- The Microsoft link in my earlier posting in this thread does a good job of explaining the steps necessary to render an SSRS report.  Essentially the three components (all available through the integration with the SSRS Web service) needed in sequence are (1) Load Report Component, (2) Set Execution Parameters Component, (3) Render Component.  In particular, the Load Report Component returns a value called the Execution ID that needs to be supplied for the other two components to enable SSRS to keep state through the process.

- SSRS can render the report in a number of different formats.  I selected the IMAGE format for this Workflow.  Using a Single Value Mapping component similar to what reecardo described in this thread...

I mapped the Render Component Result[] element returned by the Render Component to the Contents[] of a File Data Type variable I created in Workflow.  Lastly I used an Image to PDF component (part of Workflow's PDF dll) to format the File Data Type variable as a PDF file.

Of course, there are other details, but those are the key points I found in developing this integration.