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 "${project.artifactsDir}" -ReportServer "${ssrsInstall.reportServer}" -folderPath ${ssrsInstall.destFolderPath} -folderName "${ssrsInstall.destFolderName}"" />
<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 "${ssrsInstall.reportServer}" -folderPath ${ssrsInstall.destFolderPath} -folderName "${ssrsInstall.destFolderName}" -name "${ssrsInstall.parameter.name}" -value "${ssrsInstall.parameter.connectionString}"" />
<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 "${ssrsInstall.connectionString}" -ProjectName "${ssrsInstall.projectName}" -FolderName "${ssrsInstall.folderName}"" />
<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”