Executing SSRS report via a console application though a SQL Server Agent job

Question

I have a SSRS solution that contains 20 reports all of which utilise a shared data source called "DataWarehouse". This datasource has been configured to use Windows Authentication. These reports have been deployed to a server.

I have a requirement where I need to be able to automatically run these reports once a data warehouse has completed loading overnight. Sometimes the warehouse load might take 5 hrs, sometimes it might take 10 and so I don't want schedule these reports at a particular time using reporting services, I want the warehouse load to determine when its done and trigger the reports.

Now, I have a mechanism to do this:

1. I have written a c# console application that accepts a report name and a file path which runs the report and saves it do wherever it needs to.
2. I have an SSIS package that executes the console application from (i). I use an SSIS package because the DBA where I work will not enable xp_cmdshell.
3. I have an agent job that runs the SSIS package when the warehouse load completes.

Now if I run the SSIS package on my local machine (as me) the package executes successfully and the report is generated and saved. When I deploy the SSIS package to the server and try and run it through the agent job it fails with the following error:

System.Web.Services.Protocols.SoapException: The permissions granted to user 'NT SERVICE\SQLSERVERAGENT' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'NT SERVICE\SQLSERVERAGENT' are insufficient for performing this operation.
   at Microsoft.ReportingServices.Library.ReportExecution2005Impl.LoadReport (String Report, String HistoryID, ExecutionInfo2& executionInfo)
   at Microsoft.ReportingServices.WebServer.ReportExecutionService.LoadReport (String Report, String HistoryID, ExecutionInfo& executionInfo)

My initial guess was that this means the SSIS job gets executed as 'NT SERVICE\SQLSERVERAGENT' and this user does not have access to run reports on the report server. So I jumped on to the report server and tried adding NT SERVICE\SQLSERVERAGENT as a user who could run reports but I was still getting errors. Can anybody help me figure how I can get this working? Thanks

Answer 1

This will help you execute the SSIS package in the command prompt, and you can run the package on the server in scheduled tasks from the batch file (you may need special permissions to run a batch file in scheduled tasks). What you will want to do is create a windows batch file to allow you to automatically run the SSIS package via Command Prompt and you will need to use Dtexec. Here is what your batch file will look like:

@ECHO OFF
Some comment about what the package is doing

Dtexec /f "C:\some\file\path\YourPackage.dtsx

Then once you save the notepad as a windows batch file you will be able to open the batch file and it will run your package in the Command Prompt. I hope this helps

Answer 2

I edited the console application to connect to the SSRS server as a specific user. I've added what I did below incase its useful to somebody else.

To do this I created a file called account.config which just contained the username on the first line and password on the secondline:

MyUser
MyPassword

Then in my console application I have the following code:

// Read in the config file and add the lines to a list
List<string> accountDetails = File.ReadAllLines(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "account.config")).ToList();

ReportExecutionService myReportExecutionService = new ReportExecutionService();

// Add the credentials
// accountDetails[0] is the username, accountDetails[1] is the password
myReportExecutionService.Credentials = new NetworkCredential(accountDetails[0], accountDetails[1]);