Automatic deployment of SSRS reports

Here is a brief article on how to set up an automatic deployment of SSRS reports.
The automation tool of choice is OctopusDeploy. This will be used to fetch and deploy the reports to different environments.

Deployment Script

Instead of writing from scratch, there is an Open Source project with a set of scripts that can be used for this purpose. The original project is here, and it can be used for feedback and documentation, while I have a working clone available here.

While trying to run the scripts, I've run into numerous issues.

• The scripts require credentials. An article describing how to supply credentials is available here.
• The reports projects must have the DataSet directory set up as the property in the project file.
• Error "Property 'IntegratedSecurity' cannot be found on this object." is a result of not having authentication set up in a data source (.rds) file.

Deploying multiple projects is possible by using ForEach construct in powershell and calling the script passing the project names. Even better, after finding this, I have created a script that reads a solution file and extracts the report project file paths from it and feeds it to the deployment script.

The deployment script expects the reports to be available in the same folder or subfolders. To get this, we need to pack the reports into a NuGet package first and then unpack just before the actual deployment.

Creating NuGet Package for Reports

The NuGet package can be created during the build process on the build server. This can happen during a build of another project, which then gets pushed to Octopus using OctoPack.
Additional resources:

• SSRS Deployment, Octopus

The solution I'm in favour of is adding a Powershell script at the end of the build process in TFS. The script extracts the build number from the TFS Build name ($env:TF_BUILD_BUILDNUMBER) and packs all the report files into the appropriate NuGet package.
To pack the report files (*.rdl), use the src="..\Reports\**\*.rdl" specifier. More info available in Nuspec Reference. Another thing that has to be included are the *.ps1 scripts for deployment.

<file src="..\Reports\scripts\**\*.ps1" target="" />

Unfortunately, it is not possible to use OctoPack to package the Reports project for this purpose.

The Deploy.ps1 script must be located in the root of the NuGet package. For this reason, we need to link the scripts like this:

<file src="..\Reports\scripts\**\*.ps1" target="content" exclude="..\Reports\scripts\Deploy.ps1" />

<file src="..\Reports\scripts\Deploy.ps1" target="" />

 The scripts above are all the scripts from the SSRS Powershell Deploy project. In order for them to work, we have to pack the solution (*.sln) and project (*.rtpproj) files, which contain all the settings for the deployment of the .rdl files.

Once done, this package is pushed to the Octopus NuGet repository.

Deploying the Reports package

Once the package has been pushed to Octopus repository, it is time to set up the actual deployment process. According to Octopus documentation,  custom scripts can be executed during the deployment. It should be enough to create a Deploy.ps1 powershell script and it will be executed by Octopus. For this, you also need to enable Custom Scripts in the deployment process.

The Reports deployment requires credentials for authentication with a SSRS server. Therefore, the deployment script must have access to these somehow. Unfortunately, Powershell has no access to the credentials of the current user which means that these credentials have to be passed as parameters. 

More info : Get Credentials without promptAt least, the password can be encrypted so it is not stored in clear text. Be aware that there are only three ways to pass credentials in Powershell.

The simplest solution I found is to provide the variables through Octopus. It allows storage of Sensitive Variables in the deployment setup and these variables can be automatically substituted or used directly in the scripts.