SQL Server Reporting Services build and deploy automation

In sequence to my previous post, here, I also implemented the build and deploy automation for Reporting Services Solutions.

The Build is made using MSBuild.

The deploy uses PowerShell, back in November 2016 the SSRS team announced the availability of 28 new PowerShell commands for working with SSRS (in Native Mode) available in the PowerShell Gallery and in GitHub. These commands use the SSRS API so a lot of the commands will work with SSRS instances as old as SQL Server 2008 R2, however some of the commands like Register-PowerBI, will only work with a SSRS 2016+ instance. These scripts can be used in three flavor, SOAP, REST and WMI API, I opted to use the SOAP version.

1. Pre-requisites:

1.1. SQL Server Data Tools 2015 (SSDT2015), you can download them here.
1.2. Visual Studio using CLR 4.0 or above to build an library (assembly).
1.3. Power Shell 3.0 (Scripting the deploy)
1.4. SSRS > 2008 R2
1.5. HTTP, port 80, connectivity from buidl/deploy server to SSRS.
1.5. Connectivity between deploy server and Report Server, protocol HTTP port 80.

2. Build Solution via MSBuild

As announced by SQL Server Reporting Services Team, here, Visual Studio 2015 Reporting Services solutions with SSDT 2015 are now compatible with MSBuild. I had leverage this feature to build the solutions and generate the artifacts to deploy.

Like in the latest post the MSBuild command is invoked via a Ruby\Rake task:

module Build

    class CompileSSRSTask < Task

        attr_reader :solutions

        def initialize(rake_name, build_spec, logger)
            super(rake_name, build_spec, logger)
            @solutions = []
          end

          def setup
            @solutions = get_projects_to_compile
          end

          def execute            
           
       #Build projects not Solution
       
            @solutions.each do |sln|
                get_rptprj_from_solution(sln).each do |dtProj|

                    puts 'Solução: ' + sln
          
                    puts 'Projecto encontrado na solução: ' + dtProj  
          
            sdk_path = Build::DOT_NET_4_0_SDK_PATH

                    puts 'SDK Path: ' + sdk_path

                    msbuild_path = get_msbuild_path
                    
                    if(!msbuild_path.nil? && msbuild_path != "")
          
                      msbuild = "\"#{msbuild_path}/msbuild.exe\""
            
                    else
          
                      msbuild = "#{sdk_path}/msbuild"
            
                    end

                    command = "#{msbuild} #{dtProj}"

                    puts command

                    exec_and_log(command)

                end
            end

          end

          private
          def get_projects_to_compile
      build_spec.get_spec_for(rake_name.to_sym, :projects_to_compile, Rake::FileList[File.join(build_spec.working_dir_path, '*VSSSRS*.sln')])
          end

          def get_rptprj_from_solution(slnfile)
      references = []
      dtproj = []			
      dirname = ""
      File.open(slnfile, "r") do |f|
      dirname = File.dirname  f
        f.each_line do |line|
        if(line.start_with? "Project")
          tokens = line.split(',')
          candidate = tokens[1]	
          if(!candidate.nil?)
            if(candidate.include? ".rptproj")
              references.push candidate.gsub('"','').strip!
            else
              puts "\tskipping #{candidate}." if @verbose
            end
          end
        end
        end
      end

      references.each do |c|	
        c = File.join(dirname,c)
        dtproj.push c
      end
      
      return dtproj
        end
        
        def get_msbuild_path
            msbuild_version = build_spec.get_spec_for(rake_name.to_sym, :msbuild_version, "")
            if(msbuild_version == "14" || msbuild_version == "14.0")
                Build::MSBUILD_14_PATH
            end
        end
      
    end

end

3. Deploy via Power Shell 3

Now that we have the deploy artifacts, dtsx , datasources and datasets files, let us deploy it in a automated process.

To execute the deploy we used the Power Shell scripts of ReportingServiceTools 0.0.4.2.

This Power Shell module typically is installed via PS Consolse:

PS>Install-Module -Name ReportingServicesTools

As in the latest post, we need an offline installation, after installing it in your local machine, copy the module to the Deploy Server. During the online installation when no scope is defined, or when the value of the Scope parameter is AllUsers, the module is installed to %systemdrive%:\Program Files\WindowsPowerShell\Modules. When the value of Scope is CurrentUser, the module is installed to $home\Documents\WindowsPowerShell\Modules.

I wrote two scripts, one to install the report project folder in the reporting server.
The second one allows the parametrization of the datasource connection string.

3.1. PowerShell Deploy script

Param
(
  [string]$sourceFolder = $(throw "sourceFolder is required."), #required parameter, 
  [string]$ReportServer = $(throw "ProjectFilePath is required."), #required parameter, 
  [string]$folderPath = $(throw "EnvironmentName is required."), #required parameter,
  [string]$folderName = $(throw "sqlConnectionString is required.") #required parameter
)

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition

$scriptPathInclude = $scriptPath + "\log.ps1"

$scriptPathIncludeRS = $scriptPath + "\ReportingServicesTools\ReportingServicesTools.psd1"

# Garantir importação manual dos comandos, caso não estejam definidos
if (-Not (Get-Command "LogMessage" -errorAction SilentlyContinue))
{
    write-Output "A importar " $scriptPathInclude
    . $scriptPathInclude
}

LogMessage('A importar ' + $scriptPathIncludeRS )

Import-Module "$scriptPathIncludeRS" -Force

#Get-Command -Module ReportingServicesTools

try 
{

$folderExists = $false

#Set Verbose Mode

$VerbosePreference = "continue"

$folders = Get-RsFolderContent -RsFolder $folderPath -ReportServerUri $ReportServer

foreach ($element in $folders) {

  If($element.Name -eq $folderName)
    {
          $folderExists = $true
          break
    }
}

#Criar folder no root do report server

If($folderExists -eq $false)
{
    New-RsFolder -ReportServerUri $ReportServer -Path $folderPath -Name $folderName -Verbose  
}

$fullpath = $folderPath + '/' + $folderName 

#Override do conteudo do folder

Write-RsFolderContent -ReportServerUri $ReportServer -Path $sourceFolder -Destination $fullpath -Overwrite -Verbose

LogMessage($folderName + ' folder has been deployed successfully')

Exit 0

}
Catch
{
  LogMessage ('[EXCEPTION] ' + $_)
  
  $err = $_.Exception
    
  while ( $err.InnerException )
    {
    $err = $err.InnerException
    LogMessage $err.Message
    };
    continue
  
  exit 1;
}

3.1. DataSource Connection String parametrization

Param
(
  [string]$sqlConnectionString = $(throw "sqlConnectionString is required."), #required parameter,
  [string]$folderPath = $(throw "folderPath is required."), #required parameter,  
  [string]$FolderName = $(throw "FolderName is required."), #required parameter
  [string]$name = $(throw "parameter name is required."), #required parameter 
  [string]$value = $(throw "parameter value is required.") 
)

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition

$scriptPathInclude = $scriptPath + "\log.ps1"

$scriptPathIncludeRS = $scriptPath + "\ReportingServicesTools\ReportingServicesTools.psd1"

# Garantir importação manual dos comandos, caso não estejam definidos
if (-Not (Get-Command "LogMessage" -errorAction SilentlyContinue))
{
    write-Output "A importar " $scriptPathInclude
    . $scriptPathInclude
}

LogMessage('A importar ' + $scriptPathIncludeRS )

Import-Module "$scriptPathIncludeRS" -Force

#Get-Command -Module ReportingServicesTools

try 
{

#Look for DataSources in the dest folder and set their values

$dataSourcefullpath = $folderPath + '/' + $folderName + '/' + $name

$dataSource = Get-RsDataSource -ReportServerUri $sqlConnectionString -Path $dataSourcefullpath 

if ($dataSource -ne $null) 
{
  #Change Connection

  Write-Host $dataSource.Name

  $dataSource.ConnectString = $value

  Set-RsDataSource -ReportServerUri $sqlConnectionString -RsItem $dataSourcefullpath -DataSourceDefinition $dataSource

  Write-Output "Changed datasource $($name)"
}
else 
{
  Write-Warning "DataSource $($name) does not exists!"
  Exit 1
}         

Exit 0

}
Catch
{
  LogMessage ('[EXCEPTION] ' + $_)
  
  $err = $_.Exception
    
  while ( $err.InnerException )
    {
    $err = $err.InnerException
    LogMessage $err.Message
    };
    continue
  
  exit 1;
}

3.2 The PowerShell Orchestration 

To execute the deployment I use a aplication in NANT version 0.92. This application receives as input a XML file with the indication of the location of the artifacts to install and an configuration xml file. The look like this:

<ssrsInstall><ssrs prefix="ssrs" reportServer="http://myServer/ReportServer_SQL01" destFolderPath="/reports/sales" destFolderName="grocery">
    <dataSources>
        <dataSource name="SQL01" connectionString ="data source= sql01.dev.loc;initialcatalog=sales"/>
        <dataSource name="Notfound"connectionString="value"/>
    <dataSources/>
</ssrs>...
</ssrsInstall>

Specific configurations to deploy the SSRS solutions:

reportServer- Report Server Virtual Directory;

destFolderPath- Report server folder path;

destFolderName- Reports folder name;

dataSource- (root node to apply datasource parametrization)

name- data source name;

connectionString – connectionstring

The NANT task is the following:

<?xml version="1.0" ?>
<project>
  <target name="ssrsInstall">
    
    <!-- Declare properties -->
    
    <property name="ssrsInstall.reportServer" value=""/>		
    <property name="ssrsInstall.destFolderPath" value=""/>
    <property name="ssrsInstall.destFolderName" value=""/>
    <property name="ssrsInstall.parameter.connectionString" value=""/>			
    <property name="ssrsInstall.parameter.name" value=""/>					
    <property name="ssrsInstall.prefix" value="" />			
      
    <!-- Iterate over ssrs instalations settings -->
    <xml-foreach file="${ManifestFile}" xpath="/deploymentButlerManifest/environment[@name='${Env}']/ssrsInstall/ssrs">    
      
      <!-- Read instalation properties -->
      <xmlpropertybinding>				
        <get xpath="@reportServer" property="ssrsInstall.reportServer"/>				
        <get xpath="@destFolderPath" property="ssrsInstall.destFolderPath"/>
        <get xpath="@destFolderName" property="ssrsInstall.destFolderName"/>
        <get xpath="@prefix" 	        property="ssrsInstall.prefix" />	
      </xmlpropertybinding>
    
      <do>
        <echo message="[project.prefixList]: ${project.prefixList}"></echo>
        <echo message="[ssrsInstall.prefix]: ${ssrsInstall.prefix}"></echo>
        <stringMatch stringCompare="${ssrsInstall.prefix}" stringList="${project.prefixList}" returnProperty="ssrsInstall.prefixMatch"></stringMatch>	
        <if test="${(string::to-upper(ssrsInstall.prefixMatch) == string::to-upper(ssrsInstall.prefix) or string::to-upper(project.prefixList) == 'ALL')}">
      
      
          <!-- Check for mandatory values -->
          <fail message="Property reportServer not found in manifest" 	if="${ssrsInstall.reportServer == ''}" />			
          <fail message="Property destFolderPath not found in manifest" if="${ssrsInstall.destFolderPath == ''}" />
          <fail message="Property destFolderName not found in manifest"		if="${ssrsInstall.destFolderName == ''}" />
            
          <!-- Debug: print values -->
          <echo message="reportServer: ${ssrsInstall.reportServer}"/>		
          <echo message="destFolderPath: ${ssrsInstall.destFolderPath}"/>			
          <echo message="destFolderName ${ssrsInstall.destFolderName}"/>
    
          
          <!-- Execute instalation -->
          <call target="ssrsInstall.Execute"/>  
              
          <xml-foreach file="${ManifestFile}" xpath="/deploymentButlerManifest/environment[@name='${Env}']/ssrsInstall/ssrs/dataSources/dataSource"> 						
            <xmlpropertybinding>				
              <get xpath="@name" property="ssrsInstall.parameter.name"/>
              <get xpath="@connectionString" property="ssrsInstall.parameter.connectionString"/> 					
            </xmlpropertybinding>
            
            <do>							
                          
              <call target="ssrsInstall.DataSource.Execute"/>	
              
              <property name="ssrsInstall.parameter.name" value=""/>			
              <property name="ssrsInstall.parameters.connectionString" value=""/>						
              
            </do>
          </xml-foreach>	
                    
          <property name="ssrsInstall.reportServer" value=""/>			
          <property name="ssrsInstall.destFolderPath" value=""/>
          <property name="ssrsInstall.destFolderName" value=""/>
          
        </if>
      </do>			
        
    </xml-foreach>
  </target>
  
  <target name="ssrsInstall.Execute">		
  
    <property name="ssrsInstall.args" value="-sourceFolder &quot;${project.artifactsDir}&quot;  -ReportServer 	&quot;${ssrsInstall.reportServer}&quot; -folderPath ${ssrsInstall.destFolderPath} -folderName &quot;${ssrsInstall.destFolderName}&quot;" />
    
    <echo message="ssrsInstall.args: ${ssrsInstall.args}" />		
    
    <powershell4 	version="4"
            scriptfile="${deployCommon.powershell.path}\installRpt.ps1" 
            scriptargs="${ssrsInstall.args}" 
            resultproperty="result" 
            timeout ="600000"
            failonerror="true"
            verbose="true"/>
  </target>
  
  <target name="ssrsInstall.DataSource.Execute">
    
    <property name="ssrsInstall.Parameters.args" value="-sqlConnectionString &quot;${ssrsInstall.reportServer}&quot; -folderPath ${ssrsInstall.destFolderPath} -folderName &quot;${ssrsInstall.destFolderName}&quot; -name &quot;${ssrsInstall.parameter.name}&quot; -value  &quot;${ssrsInstall.parameter.connectionString}&quot;" />
    
    <echo message="ssrsInstall.Parameters.Args: ${ssrsInstall.Parameters.args}" />
    
    <powershell4 	version="4"
            scriptfile="${deployCommon.powershell.path}\installRptDataSource.ps1" 
            scriptargs="${ssrsInstall.Parameters.args}" 
            resultproperty="result" 
            timeout ="600000"
            failonerror="true"
            verbose="true"/>
  </target>
  
  <!-- <target name="ssrsInstall.Parameters.ShowAll">
    
    <property name="ssrsInstall.Parameters.ShowAll.args" value="-sqlConnectionString &quot;${ssrsInstall.connectionString}&quot; -ProjectName &quot;${ssrsInstall.projectName}&quot; -FolderName &quot;${ssrsInstall.folderName}&quot;" />
    
    <echo message="ssrsInstall.Parameters.ShowAll.Args: ${ssrsInstall.Parameters.ShowAll.args}" />
    
    <powershell4 	version="4"
            scriptfile="${deployCommon.powershell.path}\installIspacParametersShowAll.ps1" 
            scriptargs="${ssrsInstall.Parameters.ShowAll.args}" 
            resultproperty="result" 
            timeout ="600000"
            failonerror="true"
            verbose="true"/>
  </target> -->
  
  
</project>

Final notes:

This process allows the automation of  builds and deploy of SSRS solutions. The orchestration of the Ruby\Rake and Nant application is made by Hudson Server.

King Regards,
Nuno Monteiro

 

One thought on “SQL Server Reporting Services build and deploy automation”

Leave a Reply

Your email address will not be published. Required fields are marked *