Friday, March 30, 2007

How to create a Report Execution Snapshot with SSIS

This last week I have been searching the internet for a solution on how to create a Reporting Services Report Execution Snapshot with SQL Server Integration Services. SQL Server Reporting Services mainly runs on XML and SOAP methods. So calling a RS function or method is relatively simple. SSIS even has a web service task that theoretically would work perfect for this situation. I tried referencing RS .wsdl file and SSIS wouldn't accept the format. After some more research I realized the web service task is pretty much useless when connecting to Reporting Services.

Then I looked at the scripting task in SSIS and that seemed too complicated. To get it to work I would have to create a RS proxy server and write a good amount of code. It seemed too difficult for what seemed to be an easy task.

I then found the rs utility. You can write reporting services script files (.rss) using VB.net to call Reporting Services web service methods and run these files from the command line. By putting the command line command in a batch file I am able to create an Execute Process task in SSIS and call the batch file. It was that easy.

So here is my solution:

reportsnapshot.rss
Public Sub Main()
'Run this in the command line: rs -i reportsnapshot.rss -s http://server/reportserver -v reportPathParameter=%1

Dim reportPath as string = reportPathParameter
Dim schedule AS NoSchedule
Dim EnableManualSnapshotCreation as Boolean = true
Dim KeepExecutionSnapshots as Boolean = false

'Sets the credentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

'Sets the Report History options
rs.SetReportHistoryOptions(reportPath, EnableManualSnapshotCreation, KeepExecutionSnapshots, schedule)

'Updates the Report Snapshot
rs.UpdateReportExecutionSnapshot(reportPath)


End Sub


reportsnapshot.bat
rem change directory
cd to where ever you have the .rss file saved

rem This batch file calls Reporting Services Script 'reportsnapshot.rss' and passes the report path which is the %1 variable
rs -i reportsnapshot.rss -s http://server/reportserver -v reportPathParameter=%1

rem This will indicate whether there is an error. Error = 1, Non Error =0. This is used in the Process Task to indicate whether the task fails or succeeds.
EXIT [ERRORLEVEL]


Execute Process Task
Executable: Path to where you have the .bat file saved
Arguments: The path to the report (ex. "/folder/report") Note: You need the first "/". This will be passed into the batch file as the %1 parameter.
FailTaskIfReturnCodeIsNotSuccessValue: True
SuccessValue: 0 (The EXIT [ERRORLEVEL] will return a 0 or 1. 1 is fail. This will make it so the task will fail if the batch file encounters an error.)


If you are in need of clarification or if you have questions about this you can e-mail me!

2 comments:

Anonymous said...

Very useful. it solved my problem. many thanks!

Anonymous said...

very useful. it solved my problem. thank you!