Skip to content

Instantly share code, notes, and snippets.

@crshnbrn66
Last active August 30, 2018 15:19
Show Gist options
  • Save crshnbrn66/b10e43ef0dadf7f4eeae620428b2cdd9 to your computer and use it in GitHub Desktop.
Save crshnbrn66/b10e43ef0dadf7f4eeae620428b2cdd9 to your computer and use it in GitHub Desktop.
# SQLReporting.psm1 Version 0.9
# Written by Trond Hindenes
#http://hindenes.com/powershell/SQLReporting.psm1
#Import-Module Pester
#http://stackoverflow.com/questions/9178685/change-datasource-of-ssrs-report-with-powershell
<#
.SYNOPSIS
Gets SSRS instances on ComputerName
.DESCRIPTION
When passed a computername this function gets the SSRS instances on the Computername.
.EXAMPLE
Get-SSRSInstances -ComputerName tfs.yourdomain.com
Returns: Object with instance details
.PARAMETER InstanceName
This allows for querying based on this instance name.
.PARAMETER ComputerName
Local or remote computername
.NOTES
Returns an object containing the instance name for the SSRS server in question
#>
function Get-SSRSInstances
{
param
(
[string]$InstanceName,
[string]$ComputerName = $env:COMPUTERNAME
)
Begin
{
$ComputerName = Get-LocalRemoteComputer -computername $ComputerName
}
Process
{
$ReportingWMIInstances = @()
$ReportingWMIInstances += Get-WmiObject -Namespace 'Root\Microsoft\SqlServer\ReportServer' -Class '__Namespace' -ErrorAction 0 -ComputerName $ComputerName
if ($ReportingWMIInstances.count -lt 1)
{
Write-Error -Message "Couldn't find any SQL Server Reporting Instances on this computer"
}
$ReportingInstances = @()
Foreach ($ReportingWMIInstance in $ReportingWMIInstances)
{
#Find the SRS Version and admin instance
$WMIInstanceName = $ReportingWMIInstance.Name
#WMIInstanceName will be in the format "RS_InstanceName", split away the rs part
$InstanceDisplayName = $WMIInstanceName.Replace('RS_', '')
$InstanceNameSpace = "Root\Microsoft\SqlServer\RWhere-Objecthere-Objecthere-ObjectportServer\$WMIInstanceName"
$VersionInstance = Get-WmiObject -Namespace $InstanceNameSpace -Class '__Namespace' -ErrorAction 0 -ComputerName $ComputerName
$VersionInstanceName = $VersionInstance.Name
$AdminNameSpace = "Root\Microsoft\SqlServer\ReportServer\$WMIInstanceName\$VersionInstanceName\Admin"
$ConfigSetting = Get-WmiObject -Namespace $AdminNameSpace -Class 'MSReportServer_ConfigurationSetting' -ComputerName $ComputerName | Where-Object { $_.InstanceName -eq $InstanceDisplayName }
$ConfigSetting | add-member -MemberType NoteProperty -Name 'InstanceAdminNameSpace' -Value $AdminNameSpace
if ($ComputerName -ne $env:computername)
{
if ($ConfigSetting.PathName -match '\w:')
{
$driveletter = ($matches.values).trim(':')
$configSettingPath = $ConfigSetting.pathname -replace '\w:', "\\$ComputerName\$driveletter$"
}
[xml]$ReportServerInstanceConfig = Get-content $configSettingPath
}
else
{
[xml]$ReportServerInstanceConfig = Get-content $ConfigSetting.PathName
}
$ConfigSetting | add-member -MemberType NoteProperty -Name 'ConfigFileSettings' -Value $ReportServerInstanceConfig
$ReportingInstances += $ConfigSetting
}
if ($InstanceName)
{
$ReportingInstances = $ReportingInstances | Where-Object { $_.InstanceName -like $InstanceName }
}
$ReportingInstances
}
End { }
}
<#
.SYNOPSIS
Determines if the name passed is the localhost or not
.DESCRIPTION
If the name passed is the localhost then the script will send back the computername:
.example
get-localremotecomputer -computername .
yourmachinename
get-localremotecomputer -computername 127.0.0.1
yourmachinename
get-localremotecomputer -computername servername
servername
.PARAMETER computername
A description of the computername parameter.
.NOTES
Additional information about the function.
#>
function Get-LocalRemoteComputer
{
param
(
[string]$computername
)
if ($computername -eq '.' -or ($env:COMPUTERNAME -eq $computername) -or ($computername -eq 'Localhost') -or ($computername -eq '127.0.0.1'))
{
$computername = $env:COMPUTERNAME
$computername
}
else
{ $computername }
}
<#
.SYNOPSIS
Gets all items with the name specified
.DESCRIPTION
The value passed for the report name is what the script will search for.
Forexample: Get-SSRSItem -webServiceUrl $ReportWebService -reportName 'Bug Status' -Credentials $credentials
The return will be all reports that have the name of Bug Status.
.PARAMETER webServiceUrl
path to where the Services are for the given SSRS website.
.PARAMETER reportName
A description of the reportName parameter.
.PARAMETER Credentials
A description of the Credentials parameter.
.PARAMETER ItemType
A description of the ItemType parameter.
.NOTES
Additional information about the function.
#>
function Get-SSRSItem
{
param
(
[Parameter(Mandatory = $true,
ValueFromPipelineByPropertyName = $true,
Position = 0)]
[Alias('url')]
[string]$reportWebServiceUrl,
[Parameter(ValueFromPipelineByPropertyName = $true,
Position = 1)]
[Alias('identity', 'name')]
[Object]$reportName = $null,
[Parameter(ValueFromPipelineByPropertyName = $true,
Position = 2)]
[System.Management.Automation.Credential()][pscredential]$Credentials,
[Parameter(ValueFromPipelineByPropertyName = $true,
Position = 3)]
[Alias('TypeName')]
[ValidateSet('Unknown', 'Folder', 'Report', 'Resource', 'LinkedReport', 'DataSource', 'Model', 'Site', 'DataSet', 'Component', 'RdlxReport')]
[string]$ItemType
)
Begin
{
}
Process
{
Write-Verbose "Creds are $credentials"
Write-Verbose "WebServiceURL are $reportWebServiceURL"
#$ErrorActionPreference="Stop"
if ($reportWebServiceUrl -notmatch 'ReportService2010.asmx')
{
$reportWebService2010Url = "$reportWebServiceUrl/ReportService2010.asmx?WSDL"
#$reportWebServiceurl = $reportWebServiceUrl.Replace("//","/")
}
elseif ($reportWebServiceUrl -notmatch 'asmx') { $reportWebService2010Url = "$reportWebServiceUrl/ReportService2010.asmx?WSDL" }
else { $reportWebService2010Url = $reportWebServiceUrl }
#Create Proxy
Write-Verbose "Creating Proxy, connecting to : $reportWebServiceUrl"
$ssrsProxy = New-WebServiceProxy -Uri $reportWebService2010Url -UseDefaultCredential -ErrorAction 0 -namespace 'SSRSProxy' -class 'ReportService2010'
#Test that we're connected
$members = $ssrsProxy | get-member -ErrorAction 0
if (!($members))
{
if (!$credentials)
{
$credentials = Get-Credential -Message 'Enter credentials for the SSRS web service'
}
Else
{
}
$ssrsProxy = New-WebServiceProxy -Uri $reportWebService2010Url -Credential $credentials -namespace 'SSRSProxy' -class 'ReportService2010'
}
$members = $ssrsProxy | get-member -ErrorAction 0
if (!($members))
{
Write-Error 'Could not connect to the Reporting Service'
Break
}
$reportPath = '/'
$allitems = $ssrsProxy.ListChildren('/', $true)
if ($reportname)
{
$allitems = $allitems | Where-Object { $_.Name -like $reportname }
}
$allitems | ForEach-Object {
$_ | add-member -MemberType NoteProperty -Name WebServiceURL -Value $reportWebServiceUrl -force
$_ | add-member -MemberType NoteProperty -Name Credentials -Value $credentials
}
if ($ItemType)
{
$allitems = $allitems | Where-Object { $_.TypeName -eq $ItemType }
}
$allitems
}
}
<#
.SYNOPSIS
Removes SSRS Item
.DESCRIPTION
This function will remove an item from SSRS
.PARAMETER reportWebServiceUrl
Web Service to get functions from.. With the wrong url this function will not operate
.PARAMETER identity
A description of the identity parameter.
.PARAMETER Credentials
A description of the Credentials parameter.
.PARAMETER type
Specify the type of item you wish to remove valid values are:
All - this is default and will remove all items
DataSource - will remove only datasources
Folder - will remove a folder and it's contents
Report - will remove a report
.PARAMETER webServiceUrl
url for the web service to get the method calls for this script
.NOTES
Additional information about the function.
#>
function Remove-SSRSItem
{
[CmdletBinding(ConfirmImpact = 'High',
SupportsShouldProcess = $true)]
[OutputType([String])]
param
(
[Parameter(Mandatory = $true,
ValueFromPipeline = $true,
Position = 0)]
[Alias('url')]
[string]$reportWebServiceUrl,
[Parameter(ValueFromPipelineByPropertyName = $true,
Position = 1)]
[Alias('Path', 'name')]
[string]$identity = $null,
[Parameter(ValueFromPipelineByPropertyName = $true,
Position = 2)]
[System.Management.Automation.Credential()][System.Management.Automation.PsCredential]$Credentials,
[ValidateSet('DataSource', 'Folder', 'Report', 'ALL')]
[Alias('TypeName')]
$type = 'ALL',
[switch]$force
)
#$ErrorActionPreference="Stop"
Begin
{
if ($reportWebServiceUrl -notmatch 'asmx')
{
$reportWebServiceUrl = "$reportWebServiceUrl/ReportService2010.asmx?WSDL"
#$reportWebServiceurl = $reportWebServiceUrl.Replace("//","/")
}
}
Process
{
#Create Proxy
Write-Verbose "Creating Proxy, connecting to : $reportWebServiceUrl"
$ssrsProxy = New-WebServiceProxy -Uri $reportWebServiceUrl -UseDefaultCredential -ErrorAction 0 -namespace 'SSRSProxy' -class 'ReportService2010'
#Test that we're connected
$members = $ssrsProxy | get-member -ErrorAction 0
if (!($members))
{
if (!$credentials)
{
$credentials = Get-Credential -Message 'Enter credentials for the SSRS web service'
}
Else
{
}
$ssrsProxy = New-WebServiceProxy -Uri $reportWebService2010Url -Credential $credentials -namespace 'SSRSProxy' -class 'ReportService2010'
}
$members = $ssrsProxy | get-member -ErrorAction 0
if (!($members))
{
Write-Error 'Could not connect to the Reporting Service'
Break
}
$reportPath = '/'
$allitems = $ssrsProxy.ListChildren('/', $true)
if ($identity)
{
$allitems = $allitems | Where-Object { $_.Name -like $identity }
if ($type -eq 'DataSource')
{
$allitems = $allitems | Where-Object { $_.TypeName -like $type }
}
elseif ($type -eq 'Folder')
{
$allitems = $allitems | Where-Object { $_.TypeName -like $type }
}
elseif ($type -eq 'Report')
{
$allitems = $allitems | Where-Object { $_.TypeName -like $type }
}
else
{
write-verbose 'Type All is covered by the first query so drops in here when folder, report or datasource not specified'
}
}
if ($identity -eq $null)
{
Write-Verbose 'No path specified so assuming delete all'
$ThisReport = $allitems.Path
}
else
{
$ThisReport = $allitems | Where-Object { $_.Name -eq $identity }
$ThisReportPath = $ThisReport.Path
}
if ($force)
{
foreach ($item in $ThisReportPath)
{
Write-Verbose "Removing $ThisReportPath"
$ssrsProxy.deleteitem($item)
}
}
ElseIf ($psCmdlet.shouldProcess($thisreportpath, 'Remove SSRS Item'))
{
foreach ($item in $ThisReportPath)
{
Write-Verbose "Removing $ThisReportPath"
$ssrsProxy.deleteitem($item)
}
#$ssrsProxy.DeleteItem($ThisReportPath)
}
}
}
<#
.SYNOPSIS
Gets all the object for the SSRS server passed
.DESCRIPTION
This gets the objects that are defined on an SSRS Server.
if -recurse is passed then the function will recurse and retreive all objects.
.PARAMETER reportFolder
This is the folder to start the collection of objects from
.PARAMETER recurse
if you pass -recurse the fuction will recurse for all values from the starting folder location
.PARAMETER reportWebService
Web Service to get functions from.. With the wrong url this function will not operate
.PARAMETER Credentials
PSCredential object.
.NOTES
Additional information about the function.
#>
function Get-SSRSObjects
{
[CmdletBinding()]
[OutputType([object])]
param
(
[Parameter(ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true)]
[Alias('folder')]
[string]$reportFolder = '/',
[Parameter(ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true)]
[switch]$recurse,
[Parameter(Mandatory = $true,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true)]
[uri]$reportWebService,
[Parameter(ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true)]
[System.Management.Automation.Credential()][System.Management.Automation.PSCredential]$Credentials
)
$weburl = $reportFolder
$is2010 = $false
$reportRoot = "$($reportWebService.scheme)://$($reportWebService.host)/Reports"
if ($Credentials)
{
$reportProxy = new-webserviceproxy -uri $reportWebService -Credential $credentials -namespace 'SSRSProxy' -class 'ReportService2010'
}
else
{ $reportProxy = new-webserviceproxy -uri $reportWebService -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'}
if (($reportWebService.Segments | Select-Object -Last 1) -eq 'ReportService2010.asmx')
{
$web = $reportProxy.ListChildren($weburl, $recurse)
$is2010 = $true
}
else
{
$web = $ReportProxy.ListChildren($weburl)
#$web = $web | Where-Object{ $_.name -like '*.rdl' }
}
#$returnObj = @()
foreach ($item in $web)
{
If ($item.Typename -eq 'Report')
{
$dataSCName = (($reportProxy.GetItemDataSources($item.path)).name)
$datasourceType = (($reportProxy.GetItemDataSources($item.path)).item)
}
[PSCustomObject]@{
'FileName' = $item.name
'Type' = $item.Typename
'DocumentLibrary' = (($item.path).replace($item.name, ''))
'DataSourceName' = $dataSCName
#'DataSourceUrl' =$dataSourceUrl
'ReportWebService' = $reportWebService
}
}
}
<#
.SYNOPSIS
Test to see if the Datasource passed is valid
.DESCRIPTION
Checks to see if the data source exists. Then will test the data source to see if it is operational
.PARAMETER datasource
This is the full path to the datasource. where in the example datasource1 is our datasource name.
for example: \myapp\mydatasource\data sources\datasource1
example 2: \datasource1
.PARAMETER reportWebService
A description of the reportWebService parameter.
.PARAMETER Credentials
You must pass a pscredential object
.PARAMETER NoTest
if this is passed a test will not be performed on the data source.
.NOTES
Additional information about the function.
#>
function Test-ValidDataSource
{
[CmdletBinding()]
param
(
[Parameter(Mandatory = $true,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true)]
[string]$datasource,
[Parameter(Mandatory = $true,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
HelpMessage = 'Provide the full path to the DataSource')]
[string]$reportWebService,
[System.Management.Automation.Credential()][System.Management.Automation.PSCredential]$Credentials,
[switch]$NoTest
)
if ($reportWebService -notmatch '_vti_bin')
{ $datasource = Normalize-SSRSFolder $datasource }
try
{
if ($Credentials)
{
$reportProxy = new-webserviceproxy -uri $reportWebService -Credential $credentials -namespace 'SSRSProxy' -class 'ReportService2010'
}
else
{ $reportProxy = new-webserviceproxy -uri $reportWebService -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'}
$reportType = $reportProxy.getitemtype($datasource)
Write-Debug $reportType
if ($reportType -eq 'DataSource')
{
try
{
$validObject = $reportProxy.Getdatasourcecontents($datasource)
if ($validObject.gettype().name -eq 'DataSourceDefinitionOrReference' -or 'DataSourceDefinition')
{
if ($NoTest)
{
$validConnect = $false
}
else
{
$tempRef = $true # have to initialize a variable so it can be used as a reference in the next method call
$validConnect = $reportproxy.TestConnectForItemDataSource($datasource, $datasource, ($validObject.username), ($validObject.password), ([ref]$tempRef))
}
$validObject | Add-Member -type NoteProperty -Name 'Valid' -Value $validConnect
$validObject | Add-Member -Type NoteProperty -Name 'DataSource' -Value $datasource
[pscustomobject]$validObject
}
else
{
$invalid = 'invalidobject or permssion'
[pscustomobject]@{
'Extension' = $invalid
'ConnectString' = $invalid
'UseOriginalConnectString' = $false
'OriginalConnectStringExpressionBased' = $false
'CredentialRetrieval' = $invalid
'ImpersonateUserSpecified' = $false
'WindowsCredentials' = $false
'Prompt' = $invalid
'UserName' = $invalid
'Password' = $invalid
'Enabled' = $false
'EnabledSpecified' = $false
'Valid' = $false
}
}
}
catch
{
"Error was $_"
$line = $_.InvocationInfo.ScriptLineNumber
"Error was in Line $line"
}
}
}
catch
{
"Error was $_"
$line = $_.InvocationInfo.ScriptLineNumber
"Error was in Line $line"
}
}
function Normalize-SSRSFolder
{
param
(
[string]$Folder
)
if (-not $Folder.StartsWith('/'))
{
$Folder = '/' + $Folder
}
elseif ($Folder -match '//')
{
$Folder = $Folder.replace('//', '/')
}
return $Folder
}
<#
.SYNOPSIS
Uninstalls an RDL file from SQL Reporting Server using Web Service
.DESCRIPTION
Uninstalls an RDL file from SQL Reporting Server using Web Service
.NOTES
File Name: Uninstall-SSRSRDL.ps1
Author: Randy Aldrich Paulo
Prerequisite: SSRS 2008, Powershell 2.0
.EXAMPLE
Uninstall-SSRSRDL -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -path "MyReport"
.EXAMPLE
Uninstall-SSRSRDL -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -path "Reports/Report1"
#>
function Uninstall-SSRSRDL
{
param
(
[Parameter(Position = 0, Mandatory = $true)]
[Alias('url')]
[string]$reportWebServiceUrl,
[Parameter(Position = 1, Mandatory = $true)]
[Alias('path')] [string]$reportPath,
[System.Management.Automation.Credential()][pscredential]$credentials
)
#Create Proxy
Write-Verbose "[Uninstall-SSRSRDL()] Creating Proxy, connecting to : $reportWebServiceUrl"
if ($credentials) { $ssrsProxy = New-WebServiceProxy -Uri $reportWebServiceUrl -Credential $credentials -namespace 'SSRSProxy' -class 'ReportService2010' }
else { $reportProxy = new-webserviceproxy -uri $reportWebService -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'}
#Set Report Folder
if (!$reportPath.StartsWith('/')) { $reportPath = '/' + $reportPath }
try
{
Write-Verbose "[Uninstall-SSRSRDL()] Deleting: $reportPath"
#Call Proxy to upload report
$ssrsProxy.DeleteItem($reportPath)
Write-Verbose '[Uninstall-SSRSRDL()] Delete Success.'
}
catch [System.Web.Services.Protocols.SoapException]
{
$msg = "[Uninstall-SSRSRDL()] Error while deleting report : '{0}', Message: '{1}'" -f $reportPath, $_.Exception.Detail.InnerText
Write-Error $msg
}
}
<#
.SYNOPSIS
Creates an SSRS data source
.DESCRIPTION
This script creates a datasource from the PowerShell prompt.
.PARAMETER DataSourceName
A description of the DataSourceName parameter.
.PARAMETER path
Path to where the datasource will be created. This should be the root of where the source is created.
/report/report data source will be created at the second report value.
.PARAMETER reportWebService
URI to the location of the reportingService 2010 asmx page.
.PARAMETER connectString
This is the connection string that you use to connect to your database.
.PARAMETER password
Password to use if you are storing the credentials on the SQL server.
.PARAMETER UserName
Username to use for the connection if you are storing the credentiasl on the SQL Server.
.PARAMETER Extension
The Extension parameter is described as the Data Source Type in the new data source window in SSRS. Depending on your installation you may or may not have the items specified in the set statement for this function:
'SQL' = SQL Server Connection
'SQLAZURE' = SQL Azure Connection
'OLEDB' = OLEDB connection
other possible connections include: 'OLEDB-MD','ORACLE','ODBC','XML','SHAREPOINTLIST','SAPBW','ESSBASE','Report Server FileShare','NULL','WORDOPENXML','WORD','IMAGE','RPL','EXCELOPENXML','EXCEL','MHTML','HTML4.0','RGDI','PDF','ATOM','CSV','NULL','XML'
.PARAMETER windowsCredentials
windowsCredentials = When using 'Store' with credential retrieval this sets the data source to 'Use as Windows credentials when connecting to the data source'
.PARAMETER enabled
This Tells SSRS to enable the data source.
.PARAMETER ImpersonateUser
SEt this to true if you want to use the 'Impersonate the authenticated user after a connection has been made to the data source'.
.PARAMETER credentialRetrieval
CredentialRetrieval = one of four values:
None = Credentials are not required
Store = Credentials stored securely in the report server
requires setting the username and password and optional params are impersonate and windowsCredentials
Prompt = Credentials supplied by the user running the report
Integrated = Windows integrated security
.PARAMETER Credentials
The credentials parameter is required to access the web service. They should be [System.Management.Automation.PSCredential] type
.PARAMETER WebService
This is the url to the Webservice which allows for creation of
.EXAMPLE
PS C:\> $reportWebService = 'http://mySSRSServer//reportserver/reportservice2010.asmx'
PS C:\> New-SSRSDataSource -DataSourceName 'ThomTest' -path '/ThomTest' -reportWebService $ReportWebService -connectString 'Data Source=servername;Initial Catalog=DB;Integrated Security=True' -username 'domain\user' -password 'password' -Extension SQL -enabled $true -windowsCredentials $true -credentialRetrieval Store -impersonateuser $true -credentials $credentials
.NOTES
Additional information about the function.
#>
function New-SSRSDataSource
{
[CmdletBinding()]
param
(
[Parameter(Mandatory = $false)]
[string]$DataSourceName,
[string]$path,
[Parameter(Mandatory = $false)]
[uri]$reportWebService,
[string]$connectString,
[string]$password,
[string]$username,
[ValidateSet('SQL', 'SQLAZURE', 'OLEDB', 'OLEDB-MD', 'ORACLE', 'ODBC', 'XML', 'SHAREPOINTLIST', 'SAPBW', 'ESSBASE', 'Report Server FileShare', 'NULL', 'WORDOPENXML', 'WORD', 'IMAGE', 'RPL', 'EXCELOPENXML', 'EXCEL', 'MHTML', 'HTML4.0', 'RGDI', 'PDF', 'ATOM', 'CSV', 'NULL', 'XML')]
[string]$Extension = 'SQL',
[boolean]$windowsCredentials = $false,
[boolean]$enabled = $true,
[boolean]$ImpersonateUser = $false ,
[ValidateSet('None', 'Prompt', 'Integrated', 'Store')]
[string]$credentialRetrieval = 'Store',
[System.Management.Automation.Credential()][System.Management.Automation.PSCredential]$credentials
)
#https://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.createdatasource.aspx
if ($credentials) { $ssrsproxy = new-webserviceproxy -uri $reportWebService -Credential $credentials -namespace 'SSRSProxy' -class 'ReportService2010' }
else { $ssrsproxy = new-webserviceproxy -uri $reportWebService -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'}
$proxyNameSpace = $ssrsproxy.gettype().Namespace
#https://msdn.microsoft.com/en-us/library/reportservice2010.datasourcedefinition.aspx
$datasourceDef = New-Object("$proxyNameSpace.DataSourceDefinition") #definition is needed because the create expects and object with some of the properties set.
#$dataSourceProps = New-Object ("$proxyNameSpace.property")
#$ssrsExtensions = ($ssrsproxy.ListExtensions('All')).name #-join "','" for creating the set statement for extensions.
#for some reason you have to set the extension and datasouce in the definition before attempting to create.
$datasourceDef.connectstring = $connectString
$datasourcedef.Extension = $Extension
if ($credentialRetrieval -eq 'Store')
{
$datasourceDef.WindowsCredentials = $WindowsCredentials
$datasourceDef.password = $password
$datasourceDef.CredentialRetrieval = $credentialRetrieval
$datasourceDef.username = $username
}
$datasourceDefHash = @{
'ConnectString' = $connectString; 'UserName' = $username; 'Password' = $password; 'WindowsCredentials' = $windowsCredentials; 'Enabled' = $enabled; 'Extension' = $Extension; 'ImpersonateUser' = $ImpersonateUser; 'ImpersonateUserSpecified' = $true; 'CredentialRetrieval' = $credentialRetrieval
}
#convert the hashtable to an array of proxynamespace property items. https://msdn.microsoft.com/en-us/library/reportservice2010.property.aspx
$propertyCollection = $datasourceDefHash.Keys.foreach{ @{ Name = $_; Value = $dataSourceDefHash[$_] } -as "${proxyNamespace}.property" }
try
{
$ssrsproxy.CreateDataSource($DataSourceName, $path, $true, $datasourceDef, $propertyCollection)
}
catch
{
"Error was $_"
$line = $_.InvocationInfo.ScriptLineNumber
"Error was in Line $line"
}
}
function New-SSRSProxy
{
param
(
[string]$reportWebService,
[Parameter(Mandatory = $true,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true)]
[System.Management.Automation.Credential()][System.Management.Automation.PSCredential]$Credentials
)
Begin
{
if ($reportWebService -notmatch 'asmx')
{
$reportWebService = "$reportWebService/ReportService2010.asmx?WSDL"
#$reportWebServiceurl = $reportWebServiceUrl.Replace("//","/")
}
}
Process
{
#Create Proxy
Write-Verbose "Creating Proxy, connecting to : $reportWebService"
if ($Credentials) { $ssrsproxy = New-SSRSProxy -reportWebService $reportWebService -namespace 'SSRSProxy' -class 'ReportService2010' -Credentials $credentials }
else { $ssrsProxy = New-WebServiceProxy -Uri $reportWebService -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010' -ErrorAction 0 }
#Test that we're connected
$members = $ssrsProxy | get-member -ErrorAction 0
if (!($members))
{
if (!$Credentials)
{
$Credentials = Get-Credential -Message 'Enter credentials for the SSRS web service'
}
Else
{
}
$ssrsProxy = New-WebServiceProxy -Uri $reportWebService -Credential $Credentials -namespace 'SSRSProxy' -class 'ReportService2010'
}
$ssrsProxy
}
End { }
}
<#
.SYNOPSIS
Removes SSRS datasource
.DESCRIPTION
Removes a data source from the SSRS server.
.PARAMETER DataSourceName
A description of the DataSourceName parameter.
.PARAMETER ReportWebService
A description of the ReportWebService parameter.
.PARAMETER Credentials
A description of the Credentials parameter.
.EXAMPLE
PS C:\> Remove-SSRSDatasource -DataSourceName 'Value1' -ReportWebService 'Value2'
.NOTES
Additional information about the function.
#>
function Remove-SSRSDatasource
{
[CmdletBinding()]
param
(
[string]$DataSourceName,
[Parameter(Mandatory = $true)] [string]$ReportWebService,
[Parameter(Mandatory = $true)]
[System.Management.Automation.Credential()][Pscredential]$Credentials
)
if ($Credentials)
{
Remove-SSRSItem -reportWebServiceUrl $ReportWebService -Credentials $Credentials -name $DataSourceName -type DataSource -force
}
else
{ Remove-SSRSItem -reportWebServiceUrl $ReportWebService -name $DataSourceName -type DataSource -force}
}
<#
.SYNOPSIS
This function uploads files to sharepoint
.DESCRIPTION
Takes either a folder with single set of files or multiple files and uploads them to Sharepoint
destpath -- this is the destinanation that the files are intended to go to.
localfilePath -- this is the source of the files where they are coming from can be either a single file or folder
Credentials -- this contains the credentials that will authenticate to the Sharepoint server to do the upload.
fileext -- this is the extension to upload default extension is *.rdl
.EXAMPLE
Set-UploadReport -DestPath 'http://yourSharepointSever/sites/YourSite/SubSite/Shared Documents/Sales and Marketing/Survey Reports/CQE Division Reporting' -LocalFilePath 'C:\Documents\Finance Analytics and BI\DSS_Reports_SSRS\DSS_Reports_SSRS' -credentials $credentials -fileext '*.rdl' -method 'PUT'
.EXAMPLE
Set-UploadReport
#>
Function Set-UploadReport
{
param
(
[Parameter(mandatory)]
[string]$DestPath,
[Parameter(mandatory)]
[string]$LocalFilePath,
[System.Management.Automation.Credential()][System.Management.Automation.PSCredential]$credentials,
[string]$fileExt = '*.rdl',
[Parameter(Mandatory)]
[ValidateSet('PUT', 'DELETE')]
[string]$method
)
# Set the variables
$documentlibrary = $DestPath
$Files = get-childitem -Path $LocalFilePath | Where-Object {$_.Extension -like $fileExt}
$func = 'func Set-UploadReport:'
$Uploadedfiles = @()
try
{
foreach ($file in $files)
{
$fileName = $file.Name
if ($documentlibrary.EndsWith($fileName))
{
$documentlibrary = $documentlibrary.replace($filename, '')
if ($documentlibrary.EndsWith('/'))
{ $documentlibrary = $documentlibrary.Substring(0, $documentlibrary.length - 1) }
}
if ($documentlibrary.EndsWith('/')) {$documentlibrary = $documentlibrary.trim('/')}
$dest = "$documentlibrary/$fileName"
if ($method -eq 'PUT')
{
if ($credentials)
{ Invoke-WebRequest -Method $method -Uri $dest -credential $Credentials -InFile ($file.FullName) }
else { Invoke-WebRequest -Method $method -Uri $dest -UseDefaultCredentials -InFile ($file.FullName)}
$methodname = 'Uploaded'
}
elseif ($method -eq 'DELETE')
{
$methodname = 'Deleted'
if ($credentials) { Invoke-WebRequest -Method $method -Uri $dest -Credential $Credentials }
else { Invoke-WebRequest -Method $method -Uri $dest -UseDefaultCredentials }
}
write-verbose "$func $methodName $dest with Method $method"
$Uploadedfiles += $dest
}
}
catch
{
$line = $_.InvocationInfo.ScriptLineNumber
"Error was in Line $line cannot upload to $documentlibrary filename $file with Method $method"
}
return $Uploadedfiles
}
function New-ReportObject
{
param ($files)
foreach ($f in $files)
{
[xml]$tempXml = get-content $f.FullName
$dssnname = ($tempXml.Report.DataSources.DataSource.Name) #-join ','
if ($dssnname)
{
foreach ($d in $dssnname)
{
New-Object -TypeName PSObject -Property @{
'FileName' = $f.Name
'DataSourceName' = $d
'DirectoryName' = $f.DirectoryName
'CreationTime' = $f.creationTime
}
}
}
else
{
New-Object -TypeName PSObject -Property @{
'FileName' = $f.Name
'DataSourceName' = $null
'DirectoryName' = $f.DirectoryName
'CreationTime' = $f.creationTime
}
}
}
}
#-------------------------------------------------------------------------------------------
# Name: Set-SSRSDataSourceInfo
# Description: This script will set the data source url for all reports in a doclib
# Usage: Run the function with the DocLibUrl,DataSourceName and datasourcename parameters
# By: Ivan Josipovic, softlanding.ca
# found here: https://gallery.technet.microsoft.com/Set-SSRS-Data-Source-3b074747
#commands https://technet.microsoft.com/en-us/library/ee890108.aspx
#command builder for sharepoint http://www.microsoft.com/en-us/download/details.aspx?id=27588
#https://blogs.infosupport.com/managing-ssrs-reports-with-powershell/
# By: Ivan Josipovic, softlanding.ca
# found here: https://gallery.technet.microsoft.com/Set-SSRS-Data-Source-3b074747
#commands https://technet.microsoft.com/en-us/library/ee890108.aspx
#command builder for sharepoint http://www.microsoft.com/en-us/download/details.aspx?id=27588
#https://blogs.infosupport.com/managing-ssrs-reports-with-powershell/
#-------------------------------------------------------------------------------------------
Function Set-SSRSDataSourceInfo
{
<#
.SYNOPSIS
providies a means to set the SSRS Datasource.
.DESCRIPTION
if a set is passed to this fucntion it sets the value for the Datasource to the uri specified in the datasource uri only used for Sharepoint sites.
.PARAMETER DocLibUrl
Documentlibrary where the rdl lives or is present
.PARAMETER DataSourceName
Fully Qualified url for the DataSourceName that the RDL is going to be set to.
.PARAMETER reportDataSourceName
Name that the report has for the datasource.
.PARAMETER FileName
Name of the RDL file
.PARAMETER reportWebService
LInk to the ReportService2010.asmx webservice
.PARAMETER method
SET - this sets the data source to the name specified in the datasourename uri for the passed in REport filename
GET - this will get the datasource for the name specified in the datasourcename uri for the passed in Report Filename.
.PARAMETER Credentials
If Credentials are passed. those credentials are used to deploy and set the datasource. If no credentials are passed, than the logged in user running the script will be used to for authentication.
.EXAMPLE
Set-SSRSDataSourceInfo -DocLibUrl 'http:///MyWebserver.mycompany.biz/sites/reports/Shared Documents' -DataSourceName 'http://MyWebserver.mycompany.biz/sites/reports/Shared Documents/MyDataSource.rsds' -reportDataSourceName MyDatasource -FileName $report -reportWebService 'http://mysite/sites/bi/_vti_bin/ReportServer/ReportService2010.asmx' -method SET
The script sets up a proxy to the webservvice, it then attaches to the documentlibarary url and
Set-SSRSDataSourceInfo -DocLibUrl 'http:///MyWebserver.mycompany.biz/sites/reports/Shared Documents' -DataSourceName 'http://MyWebserver.mycompany.biz/sites/reports/Shared Documents/MyDataSource.rsds' -reportDataSourceName MyDatasource -FileName $report -reportWebService 'http://mysite/sites/bi/_vti_bin/ReportServer/ReportService2010.asmx' -method GET
.INPUTS
List of input types that are accepted by this function.
.OUTPUTS
List of output types produced by this function.
#>
param
(
[parameter(mandatory)]
[uri]$DocLibUrl,
[parameter(mandatory)]
[uri]$DataSourceName,
[string]$reportDataSourceName,
[parameter(mandatory)]
[string]$FileName,
[parameter(mandatory)]
[uri]$reportWebService,
[Parameter(Mandatory)]
[ValidateSet('GET', 'SET')]
[string]$method,
[string]$extFilter = '*.rdl',
[System.Management.Automation.PSCredential]$Credentials
)
$webfound = $false;
$weburl = $DocLibUrl;
$is2010 = $false
while ($webfound -eq $false)
{
if ($Credentials)
{$reportProxy = new-webserviceproxy -uri $reportWebService -Credential $credentials -namespace 'SSRSProxy' -class 'ReportService2010'}
else
{$reportProxy = new-webserviceproxy -uri $reportWebService -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'}
if (($reportWebService.Segments | Select-Object -Last 1) -eq 'ReportService2010.asmx')
{
$web = $reportProxy.ListChildren($weburl, $false)
$web = $web | Where-Object { $_.name -like $extFilter }
$is2010 = $true
}
else
{
$web = $ReportProxy.ListChildren($weburl)
$web = $web | Where-Object { $_.name -like '*.rdl' }
}
if (($datasourcename.Segments | Select-Object -last 1) -like '*.rsds')
{
#$datasourceLoc = ($datasourcename.AbsoluteUri.trim(($datasourcename.Segments | Select-Object -last 1))).trim('/')
$datasourceloc = ($datasourcename.AbsoluteUri.replace(($datasourcename.Segments | Select-Object -last 1), '').trim('/'))
}
else
{
$datasourceLoc = $datasourcename
}
if ($is2010)
{ $dsnObject = $reportProxy.listchildren($datasourceLoc, $false) }
else
{ $dsnObject = $reportProxy.listchildren($datasourceLoc) }
if ($web -ne $null)
{
$webfound = $true;
}
else
{
write-verbose "$func The Web could not be found";
return -1;
}
}
if ($method -eq 'SET')
{
$f = $FileName.ToLower()
$singleDoc = $web.path.tolower() |where-object {$_ -eq "$($DocLibUrl.OriginalString)/$f"}
$dataSources = $reportProxy.GetItemDataSources($singleDoc) #this gets our datasources for the current selected document
$proxyNameSpace = $dataSources.gettype().Namespace
$dc = $dsnobject | Where-Object { $_.path -eq $datasourcename.AbsoluteUri -or $_.path -eq $datasourcename.OriginalString }
if ($dc)
{
$d = $dataSources | Where-Object {$_.name -like $reportDataSourceName }
$newDataSource = New-Object ("$proxyNameSpace.DataSource")
$newDataSource.Name = ($datasourcename).segments | select -last 1
$newDataSource.Item = New-Object ("$proxyNamespace.DataSourceReference");
$newDataSource.Item.Reference = $dc.path #$datasourcename;#
$d.item = $newDataSource.item
$reportProxy.SetItemDataSources($singleDoc, $d)
$returnObj = 'success'
}
}
if ($method -eq 'GET')
{
$returnObj = @()
foreach ($item in $web)
{
if ($item.name -match '.rdl')
{
$dataSCName = (($reportProxy.GetItemDataSources($item.path)).name)
$durl = (($dsnObject | where-object { $_.name -like "$dataScName*" }).path)
if (($dataScName -is [array]))
{
$i = 0
$tempVar = $null
foreach ($s in $dataScname)
{
if ($i -eq (($dataScName.count) - 1))
{
$tempVar += "$s"
$durl += (($dsnObject | where-object { $_.name -like "$s*" }).path)
}
else
{
$tempVar += "$s;"
$durl += (($dsnObject | where-object { $_.name -like "$s*" }).path) + ';'
}
++$i
}
$dataSCName = $tempVar
}
if (($durl -is [array]))
{
$i = 0
$tempVar = $null
foreach ($s in $durl)
{
if ($i -eq (($durl.count) - 1))
{
$tempVar += "$s"
}
else
{
$tempVar += "$s;"
}
++$i
}
$durl = $tempVar
}
$obj = New-Object PSObject
$obj | Add-Member -MemberType NoteProperty -Name 'FileName' -Value $item.name
$obj | Add-Member -MemberType NoteProperty -name 'DocumentLibrary' -Value (($item.path).replace($item.name, ''))
$obj | add-member -MemberType NoteProperty -Name 'DataSourceName' -Value $dataSCName
$obj | Add-Member -MemberType NoteProperty -Name 'DataSourceUrl' -value $durl
$obj | add-member -MemberType NoteProperty -Name 'ReportWebService' -value $reportWebService
$returnObj += $obj
}
}
}
#$returnObj
return $returnObj
}
<#
.SYNOPSIS
Creates a folder or set of folders passed for the reportfolder
.DESCRIPTION
The value passed for the report name is what the script will search for.
Forexample: Get-SSRSItem -webServiceUrl $ReportWebService -reportName 'Bug Status' -Credentials $credentials
The return will be all reports that have the name of Bug Status.
.PARAMETER webServiceUrl
path to where the Services are for the given SSRS website.
.PARAMETER reportName
A description of the reportName parameter.
.PARAMETER Credentials
A description of the Credentials parameter.
.PARAMETER ItemType
A description of the ItemType parameter.
.NOTES
Additional information about the function.
#>
function New-SSRSFolder
{
param
(
[Parameter(Mandatory = $true,
ValueFromPipelineByPropertyName = $true,
Position = 0)]
[Alias('url')]
[string]$reportWebServiceUrl,
[Parameter(ValueFromPipelineByPropertyName = $true,
Position = 1)]
[Alias('name', 'dir')]
[Object]$FolderName = $null,
[Parameter(ValueFromPipelineByPropertyName = $true,
Position = 2)]
[System.Management.Automation.Credential()][pscredential]$Credentials
)
Begin
{
}
Process
{
Write-Verbose "Creds are $credentials"
Write-Verbose "WebServiceURL are $reportWebServiceURL"
#$ErrorActionPreference="Stop"
if ($reportWebServiceUrl -notmatch 'ReportService2010.asmx')
{
$reportWebService2010Url = "$reportWebServiceUrl/ReportService2010.asmx?WSDL"
#$reportWebServiceurl = $reportWebServiceUrl.Replace("//","/")
}
elseif ($reportWebServiceUrl -notmatch 'asmx') { $reportWebService2010Url = "$reportWebServiceUrl/ReportService2010.asmx?WSDL" }
else { $reportWebService2010Url = $reportWebServiceUrl }
#Create Proxy
Write-Verbose "Creating Proxy, connecting to : $reportWebServiceUrl"
if ($Credentials)
{$ssrsProxy = New-WebServiceProxy -Uri $reportWebService2010Url -Credential $Credentials -ErrorAction 0 -namespace 'SSRSProxy' -class 'ReportService2010'}
else {$ssrsProxy = New-WebServiceProxy -Uri $reportWebService2010Url -UseDefaultCredential -ErrorAction 0 -namespace 'SSRSProxy' -class 'ReportService2010'}
write-verbose "Find out if the folder exists already if it exists then we'll skip"
if (!((get-ssrsitem -reportWebServiceUrl $reportWebServiceUrl -reportName $FolderName -ItemType Folder).path | Where-Object {$_ -eq $FolderName}))
{
$Foldernames = $FolderName -split '/'
$folderRoot = '/'
foreach ($folder in $FolderNames)
{
if ($folder -ne '')
{
$folderBlder += "/$folder" #keep track of where we are in the folder structure
if (!((get-ssrsitem -reportWebServiceUrl $reportWebServiceUrl -reportName $Folder -ItemType Folder).path | Where-Object {$_ -eq $folderBlder}))
{
$Reporttype = $ssrsProxy.gettype().Namespace
$dataType = ("$Reporttype.Property")
$property = New-object ($dataType)
$property.Name = "Description"
$property.Value = ""
$numproperties = 1
$properties = New-Object ($datatype + '[]')$numproperties
$properties[0] = $property
$ssrsproxy.CreateFolder($folder, $folderRoot, $properties)
write-debug "created new folder $folderBlder"
}
$folderRoot = $folderBlder # this is so we can specify the root.
}
}
}
else
{}
}
}
function Publish-SSRSItemNative
# http://randypaulo.wordpress.com/2012/02/21/how-to-install-deploy-ssrs-rdl-using-powershell/
(
[Parameter(Position = 0, Mandatory = $true)]
[Alias("url", 'uri')]
[string]$webServiceUrl,
[ValidateScript( {Test-Path $_})]
[Parameter(Position = 1, Mandatory = $true)]
[Alias("rdl", 'File')]
[string]$rdlFile,
[Parameter(Position = 2)]
[Alias("folder")]
[string]$reportFolder = "",
[Parameter(Position = 3)]
[Alias("name")]
[string]$reportName = "",
[Parameter(Position = 4)]
[Alias('ReportType')]
[ValidateSet('Report', 'DataSet', 'Resource', 'Component', 'DataSource', 'Model')]
[string]$Type = "Report",
[bool]$force = $false,
[System.Management.Automation.PsCredential]$Credentials
)
{
#$ErrorActionPreference="Stop"
#Create Proxy
Write-Verbose "[Install-SSRSRDL()] Creating Proxy, connecting to : $webServiceUrl"
$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential -ErrorAction 0 -namespace 'SSRSProxy' -class 'ReportService2010'
#Test that we're connected
$members = $ssrsProxy | get-member -ErrorAction 0
if (!($members))
{
if (!$credentials)
{
$credentials = Get-Credential -Message "Enter credentials for the SSRS web service"
}
Else
{
}
$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -Credential $credentials -namespace 'SSRSProxy' -class 'ReportService2010'
}
$members = $ssrsProxy | get-member -ErrorAction 0
if (!($members))
{
Write-Error "Could not connect to the Reporting Service"
Break
}
$reportPath = (split-path $reportFolder -Parent).Replace("\", "/")
$reportLeaf = split-path $reportfolder -Leaf
if ($force)
{
#Check if folder is existing, create if not found
try
{
$ssrsProxy.CreateFolder($reportLeaf, $reportPath, $null)
Write-Verbose "[Install-SSRSRDL()] Created new folder: $reportFolder"
}
catch [System.Web.Services.Protocols.SoapException]
{
if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
{
Write-Verbose "[Install-SSRSRDL()] Folder: $reportFolder already exists."
}
else
{
$msg = "[Install-SSRSRDL()] Error creating folder: $reportFolder. Msg: '{0}'" -f $_.Exception.Detail.InnerText
Write-Error $msg
}
}
}
#Set reportname if blank, default will be the filename without extension
if (!($reportName)) { $reportName = [System.IO.Path]::GetFileNameWithoutExtension($rdlFile); }
Write-Verbose "[Install-SSRSRDL()] Report name set to: $reportName"
#Get Report content in bytes
Write-Verbose "[Install-SSRSRDL()] Getting file content (byte) of : $rdlFile"
$byteArray = Get-Content -Path $rdlFile -Encoding byte
$msg = "[Install-SSRSRDL()] Total length: {0}" -f $byteArray.Length
Write-Verbose $msg
#$reportFolder = $reportFolder
Write-Verbose "[Install-SSRSRDL()] Uploading to: $reportFolder"
#Call Proxy to upload report
#$warnings = $ssrsProxy.CreateReport($reportName,$reportFolder,$force,$byteArray,$null)
[Ref]$UploadWarnings = $null
if (!((get-ssrsitem -reportWebServiceUrl $WebServiceUrl -reportName $reportname -ItemType Report).path | Where-Object {$_ -eq "$reportfolder/$reportname"}))
{
try
{
$ssrsProxy.CreateCatalogItem($type, $reportName, $reportFolder, $force, $byteArray, $null, $UploadWarnings) #| out-null
}
catch
{
"Error was $_"
$line = $_.InvocationInfo.ScriptLineNumber
"Error was in Line $line"
"ReportName: $reportname"
"ReportFolder: $reportFolder"
"Force = $Force"
}
}
else
{
if ($force)
{
try
{
$ssrsProxy.CreateCatalogItem($type, $reportName, $reportFolder, $force, $byteArray, $null, $UploadWarnings)
}
catch
{
"Error was $_"
$line = $_.InvocationInfo.ScriptLineNumber
"Error was in Line $line"
"ReportName: $reportname"
"ReportFolder: $reportFolder"
"Force = $Force"
}
}
Else
{write-output "To force $reportname to be updated use -force `$true" }
}
write-output "$reportname uploaded"
}
function set-SSRSDataSourceInfoNative
{
<#
.SYNOPSIS
providies a means to set the SSRS Datasource.
.DESCRIPTION
if a set is passed to this fucntion it sets the value for the Datasource to the uri specified in the datasource uri only used for Native sites.
.PARAMETER DocLibUrl
Documentlibrary where the rdl lives or is present
.PARAMETER DataSourceName
Fully Qualified url for the DataSourceName that the RDL is going to be set to.
.PARAMETER reportDataSourceName
Name that the report has for the datasource.
.PARAMETER FileName
Name of the RDL file
.PARAMETER reportWebService
Link to the ReportService2010.asmx webservice
.PARAMETER method
SET - this sets the data source to the name specified in the datasourename uri for the passed in REport filename
GET - this will get the datasource for the name specified in the datasourcename uri for the passed in Report Filename.
.PARAMETER Credentials
If Credentials are passed. those credentials are used to deploy and set the datasource. If no credentials are passed, than the logged in user running the script will be used to for authentication.
.EXAMPLE
Set-SSRSDataSourceInfo -DocLibUrl 'http:///MyWebserver.mycompany.biz/sites/reports/Shared Documents' -DataSourceName 'http://MyWebserver.mycompany.biz/sites/reports/Shared Documents/MyDataSource.rsds' -reportDataSourceName MyDatasource -FileName $report -reportWebService 'http://mysite/sites/bi/_vti_bin/ReportServer/ReportService2010.asmx'
.INPUTS
List of input types that are accepted by this function.
#>
param
(
[parameter(mandatory)]
[string]$Reportname, #with no extension SSRS has no name for the file in native mode
[parameter(mandatory)]
[string]$reportPath,
[parameter(mandatory)]
[string]$DataSourceName,
[parameter(mandatory)]
[string]$DataSourcePath,
[parameter(mandatory)]
[uri]$reportWebService,
[System.Management.Automation.PSCredential]$Credentials
)
if ($Credentials)
{$reportProxy = new-webserviceproxy -uri $reportWebService -Credential $credentials -namespace 'SSRSProxy' -class 'ReportService2010'}
else
{$reportProxy = new-webserviceproxy -uri $reportWebService -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'}
$f = $ReportName.ToLower()
try
{
$dataSources = $reportProxy.GetItemDataSources("$reportpath/$reportname")
}
catch
{
"Error was $_"
$line = $_.InvocationInfo.ScriptLineNumber
"Error was in Line $line"
"ReportName: $reportname"
"ReportPath: $reportpath"
}
$proxyNameSpace = $dataSources.gettype().Namespace
$dc = $reportProxy.GetDataSourceContents($DataSourcePath)
if ($dc)
{
$d = $dataSources | Where-Object {$_.name -like $DataSourceName }
$newDataSource = New-Object ("$proxyNameSpace.DataSource")
$newDataSource.Name = $datasourcename
$newDataSource.Item = New-Object ("$proxyNamespace.DataSourceReference")
$newDataSource.Item.Reference = $DatasourcePath
$d.item = $newDataSource.item
$reportProxy.SetItemDataSources("$reportpath/$f", $d)
$set = ($reportproxy.GetItemDataSources("$reportPath/$f")).name
write-verbose "$reportname set to data source $set"
$returnobj = 'success'
}
$returnobj
}
<#
.SYNOPSIS
Updates an SSRS data source
.DESCRIPTION
This script updates a datasource from the PowerShell prompt.
.PARAMETER DataSourceName
DataSourcename - this is the full path in Sharepoint to the Data source. Http://sharepoint/mysite/mydatasources/datasource.rsds
.PARAMETER reportWebService
URI to the location of the reportingService 2010 asmx page.
.PARAMETER connectString
This is the connection string that you use to connect to your database.
.PARAMETER password
Password to use if you are storing the credentials on the SQL server.
.PARAMETER UserName
Username to use for the connection if you are storing the credentiasl on the SQL Server.
.PARAMETER Extension
The Extension parameter is described as the Data Source Type in the new data source window in SSRS. Depending on your installation you may or may not have the items specified in the set statement for this function:
'SQL' = SQL Server Connection
'SQLAZURE' = SQL Azure Connection
'OLEDB' = OLEDB connection
other possible connections include: 'OLEDB-MD','ORACLE','ODBC','XML','SHAREPOINTLIST','SAPBW','ESSBASE','Report Server FileShare','NULL','WORDOPENXML','WORD','IMAGE','RPL','EXCELOPENXML','EXCEL','MHTML','HTML4.0','RGDI','PDF','ATOM','CSV','NULL','XML'
.PARAMETER windowsCredentials
windowsCredentials = When using 'Store' with credential retrieval this sets the data source to 'Use as Windows credentials when connecting to the data source'
.PARAMETER enabled
This Tells SSRS to enable the data source.
.PARAMETER ImpersonateUser
SEt this to true if you want to use the 'Impersonate the authenticated user after a connection has been made to the data source'.
.PARAMETER credentialRetrieval
CredentialRetrieval = one of four values:
None = Credentials are not required
Store = Credentials stored securely in the report server
requires setting the username and password and optional params are impersonate and windowsCredentials
Prompt = Credentials supplied by the user running the report
Integrated = Windows integrated security
.PARAMETER Credentials
The credentials parameter is required to access the web service. They should be [System.Management.Automation.PSCredential] type
.PARAMETER WebService
This is the url to the Webservice which allows for creation of
.EXAMPLE
PS C:\> $reportWebService = 'http://mySSRSServer//reportserver/reportservice2010.asmx'
PS C:\> Update-SSRSDataSource -DataSourceName 'ThomTest' -reportWebService $ReportWebService -connectString 'Data Source=servername;Initial Catalog=DB;Integrated Security=True' -username 'domain\user' -password 'password' -Extension SQL -enabled $true -windowsCredentials $true -credentialRetrieval Store -impersonateuser $true -credentials $credentials
.NOTES
Additional information about the function.
#>
function Update-SSRSDataSource
{
[CmdletBinding()]
param
(
[Parameter(Mandatory = $false)]
[string]$DataSourceName,
[Parameter(Mandatory = $false)]
[uri]$reportWebService,
[string]$connectString,
[string]$password,
[string]$username,
[ValidateSet('SQL', 'SQLAZURE', 'OLEDB', 'OLEDB-MD', 'ORACLE', 'ODBC', 'XML', 'SHAREPOINTLIST', 'SAPBW', 'ESSBASE', 'Report Server FileShare', 'NULL', 'WORDOPENXML', 'WORD', 'IMAGE', 'RPL', 'EXCELOPENXML', 'EXCEL', 'MHTML', 'HTML4.0', 'RGDI', 'PDF', 'ATOM', 'CSV', 'NULL', 'XML')]
[string]$Extension = 'SQL',
[boolean]$windowsCredentials = $false,
[boolean]$enabled = $true,
[boolean]$ImpersonateUser = $false ,
[ValidateSet('None', 'Prompt', 'Integrated', 'Store')]
[string]$credentialRetrieval = 'Store',
[System.Management.Automation.Credential()][System.Management.Automation.PSCredential]$credentials
)
#https://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.createdatasource.aspx
if ($credentials) { $ssrsproxy = new-webserviceproxy -uri $reportWebService -Credential $credentials -namespace 'SSRSProxy' -class 'ReportService2010'}
else { $ssrsproxy = new-webserviceproxy -uri $reportWebService -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'}
$proxyNameSpace = $ssrsproxy.gettype().Namespace
#https://msdn.microsoft.com/en-us/library/reportservice2010.datasourcedefinition.aspx
$datasourceDef = New-Object("$proxyNameSpace.DataSourceDefinition") #definition is needed because the create expects and object with some of the properties set.
$datasourceDef.connectstring = $connectString
$datasourcedef.Extension = $Extension
if ($credentialRetrieval -eq 'Store')
{
$datasourceDef.WindowsCredentials = $WindowsCredentials
$datasourceDef.password = $password
$datasourceDef.CredentialRetrieval = $credentialRetrieval
$datasourceDef.username = $username
}
try
{
$ssrsproxy.SetDataSourceContents($DataSourceName, $datasourceDef)
}
catch
{
"Error was $_"
$line = $_.InvocationInfo.ScriptLineNumber
"Error was in Line $line"
}
}
<#
.Synopsis
Set's the data set location for a report.
.DESCRIPTION
This function takes the reportWebservice, the path to the report that requires a dataset. And sets the source for the report to the location where the dataset is located in sharepoint.
.EXAMPLE
set-ssrsdatasetinfo -reportwebservice http://yoursharepointserver.com/sites/datasourcetest/_vti_bin/reportserver/reportservice2010.asmx -reportPath http://yoursharepointserver.com/sites/datasourcetest/Shared%20Documents/new.rdl -datasetname 'ScheduleChanges' -datasetPath http://yoursharepointserver.com/sites/datasourcetest/Shared%20Documents/Datasets/ScheduleChanges.rsd
.EXAMPLE
set-ssrsdatasetinfo -reportwebservice http://yoursharepointserver.com/sites/datasourcetest/_vti_bin/reportserver/reportservice2010.asmx -reportPath http://yoursharepointserver.com/sites/datasourcetest/Shared%20Documents/new.rdl -datasetname 'ScheduleChanges' -datasetPath http://yoursharepointserver.com/sites/datasourcetest/Shared%20Documents/Datasets/ScheduleChanges.rsd -credentials $credentials
This takes the report defined by the reportdatase
#>
Function Set-SSRSDataSetInfo
{
param
(
[parameter(mandatory)]
[uri]$reportWebService,
[parameter(mandatory)]
[uri]$reportPath,
[parameter(mandatory)]
[string]$dataSetName,
[uri]$dataSetPath,
[System.Management.Automation.PSCredential]$Credentials
)
if (-not $Credentials)
{$Proxy = New-WebServiceProxy -Uri $reportWebService -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'}
else
{$Proxy = New-WebServiceProxy -Uri $reportWebService -Credential $Credentials -namespace 'SSRSProxy' -class 'ReportService2010'}
$datasets = $proxy.getitemreferences($reportPath.AbsoluteUri, 'DataSet')
$datasetReference = $dataSets | Where-Object {$_.name -eq $dataSetName}
if (-not $dataSetReference)
{
throw "$reportPath does not contain a dataSet reference with name $DataSetName"
}
$proxyNamespace = $dataSetReference.GetType().Namespace
$dataSetReference = New-Object ("$($proxyNamespace).ItemReference")
$dataSetReference.Name = $DataSetName
$dataSetReference.Reference = $DataSetPath.AbsoluteUri
Write-Verbose "Set dataSet reference '$DataSetName' of item $($reportPath.AbsoluteUri) to $DataSetPath"
$Proxy.SetItemReferences($($reportPath.AbsoluteUri), @($dataSetReference))
}
Function Set-NativeSSRSDataSetInfo
{
param
(
[parameter(mandatory)]
[uri]$reportWebService,
[parameter(mandatory)]
[string]$reportPath,
[parameter(mandatory)]
[string]$dataSetName,
[string]$dataSetPath,
[System.Management.Automation.PSCredential]$Credentials
)
if (-not $Credentials)
{$Proxy = New-WebServiceProxy -Uri $reportWebService -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'}
else
{$Proxy = New-WebServiceProxy -Uri $reportWebService -Credential $Credentials -namespace 'SSRSProxy' -class 'ReportService2010'}
$datasets = $proxy.getitemreferences($reportPath, 'DataSet')
$datasetReference = $dataSets | Where-Object {$_.name -eq $dataSetName}
if (-not $dataSetReference)
{
throw "$reportPath does not contain a dataSet reference with name $DataSetName"
}
$proxyNamespace = $dataSetReference.GetType().Namespace
$dataSetReference = New-Object ("$($proxyNamespace).ItemReference")
$dataSetReference.Name = $DataSetName
$dataSetReference.Reference = $DataSetPath
Write-Verbose "Set dataSet reference '$DataSetName' of item $($reportPath.AbsoluteUri) to $DataSetPath"
$Proxy.SetItemReferences($reportPath, @($dataSetReference))
}
function New-DataSetObject
{
param ($files)
foreach ($f in $files)
{
[xml]$tempXml = get-content $f.FullName
$datasetname = $null
if ($tempxml.Report.DataSets.DataSet.SharedDataSet.SharedDataSetReference)
{ $datasetname = ($tempxml.Report.DataSets.DataSet.SharedDataSet.SharedDataSetReference) }
# elseif($tempXml.SharedDataSet.DataSet.query.DataSourceReference)
# { $datasetname = ($tempXml.SharedDataSet.DataSet.query.DataSourceReference)}
foreach ($d in $datasetname)
{
New-Object -TypeName PSObject -Property @{
'FileName' = $f.Name
'DataSetName' = $d
'DirectoryName' = $f.DirectoryName
'CreationTime' = $f.creationTime
}
}
}
}
function New-DataSetDatasourceObject
{
param ($files)
foreach ($f in $files)
{
[xml]$tempXml = get-content $f.FullName
$datasetname = ($tempxml.SharedDataSet.DataSet.query.DataSourceReference)
if ($datasetname)
{
foreach ($d in $datasetname)
{
New-Object -TypeName PSObject -Property @{
'FileName' = $f.Name
'DataSourceName' = $d
'DirectoryName' = $f.DirectoryName
'CreationTime' = $f.creationTime
}
}
}
else
{
New-Object -TypeName PSObject -Property @{
'FileName' = $f.Name
'DataSourceName' = $null
'DirectoryName' = $f.DirectoryName
'CreationTime' = $f.creationTime
}
}
}
}
function Set-CacheOptions
{
param(
[parameter(mandatory)]
[uri]$reportWebService,
[parameter(mandatory)]
[uri]$reportPath,
[parameter(mandatory)]
[int]$Minutes,
[System.Management.Automation.PSCredential]$Credentials
)
if (-not $Credentials)
{$Proxy = New-WebServiceProxy -Uri $reportWebService -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'}
else
{$Proxy = New-WebServiceProxy -Uri $reportWebService -Credential $Credentials -namespace 'SSRSProxy' -class 'ReportService2010'}
$proxyNamespace = $proxy.GetType().Namespace
$timeExpiration = ((New-Object ("$($proxyNamespace).TimeExpiration")) -as ([type]"$($proxyNameSpace).ExpirationDefinition"))
$timeExpiration.Minutes = $minutes
$proxy.SetCacheOptions($reportPath.AbsoluteUri, $true, $timeExpiration)
}
function Set-NativeCacheOptions
{
param(
[parameter(mandatory)]
[uri]$reportWebService,
[parameter(mandatory)]
[string]$reportPath,
[parameter(mandatory)]
[int]$Minutes,
[System.Management.Automation.PSCredential]$Credentials
)
if (-not $Credentials)
{$Proxy = New-WebServiceProxy -Uri $reportWebService -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'}
else
{$Proxy = New-WebServiceProxy -Uri $reportWebService -Credential $Credentials -namespace 'SSRSProxy' -class 'ReportService2010'}
$proxyNamespace = $proxy.GetType().Namespace
$timeExpiration = ((New-Object ("$($proxyNamespace).TimeExpiration")) -as ([type]"$($proxyNameSpace).ExpirationDefinition"))
$timeExpiration.Minutes = $minutes
$proxy.SetCacheOptions($reportPath, $true, $timeExpiration)
}
function Deploy-SharepointReports
{
param(
[parameter(mandatory)][string]$documentLibrary,
[parameter(mandatory)][string]$dataSourceTarget,
[parameter(mandatory)][string]$webservice,
[parameter(mandatory)][string]$username,
[parameter(mandatory)][string]$password,
[object]$datasets,
[string]$datasetFilter,
[string]$TargetDatasetFolder,
[parameter(mandatory)][string]$DataSource,
[parameter(mandatory)][string]$reportPath,
[parameter(mandatory)][string]$reportExtension,
[parameter(mandatory)][string]$connectString,
[parameter(mandatory)][string]$setDatasourceOnly,
[object]$cacheFiles2Set
)
write-output "Sharepoint server: `t`t $documentLibrary"
$datasourceFilename = "$($DataSource).rsds"
$datasourceName = $DataSource
write-output "Checking Datasource: `t`t$dataSourceTarget/$datasourcefilename"
if (!((Test-ValidDataSource -datasource "$dataSourceTarget/$datasourcefilename" -reportwebService $webservice).Valid))
{
#todo need to check if just the password is bad if it's bad then need to add Update here
New-SSRSDataSource -datasource $dataSourceFileName -path $dataSourceTarget -reportWebService $webservice -connectString $connectString -username $username -password $password -Extension SQL -enabled $true -windowsCredentials $true -credentialRetrieval Store -ImpersonateUser $true
Write-output "Datasource was created $dataSourceName using $connectString with username $username"
}
else
{
write-output "Datasource Test: `t`t$dataSourceName using $connectString with username $username =Success"
}
if ($setDatasourceOnly -eq $false)
{
$uploadedReports = Set-UploadReport -destpath $documentLibrary -LocalFilePath $reportPath -fileExt $reportExtension -method PUT
$u = ($uploadedReports -join "`r`n").trim()
Write-Output "_____________Report Upload___________"
Write-Output "$u"
Write-Output "_____________________________________"
}
$reports = New-ReportObject -files (Get-ChildItem -Path $reportPath -Filter $reportExtension)
foreach ($report in $reports)
{
if ($report.datasourcename -eq $datasourceName)
{
$status = Set-SSRSDataSourceInfo -DocLibUrl $documentLibrary -DataSourceName "$dataSourceTarget/$datasourceFileName" -reportDataSourceName $datasourceName -FileName $($report.filename) -reportWebService $webservice -method SET
write-output "The following: `t`t $($report.filename) datasource was updated to $dataSourceTarget/$datasourceFileName"
}
$updateCacheFile = $cacheFiles2Set | Where-Object {$_.cachefile -eq $report.filename}
$cachevalue = $updateCacheFile.minutes
if ($Updatecachefile)
{
Set-CacheOptions -reportWebService $webservice -reportPath "$documentlibrary/$($report.filename)" -Minutes $cacheValue
Write-Output "___________RDL Cache_________________"
Write-Output "The following: `t`t$documentlibrary/$($report.filename) Cache was set to $cacheValue"
Write-Output "_____________________________________"
}
}
if ($dataSets)
{
write-verbose "upload the dataset files"
if ($setDatasourceOnly -eq $false)
{
$uploadedDataSets = Set-UploadReport -destpath $TargetDatasetFolder -LocalFilePath $reportPath -fileExt $datasetFilter -method PUT
$u = ($uploadedDataSets -join "`r`n").trim()
Write-Output "______________DataSet Upload_________"
Write-Output "$u"
Write-Output "_____________________________________"
}
Write-verbose "Get all the reports that have data sets in them"
$dataSetObjects = New-DataSetObject -files (get-childitem -path $reportpath -filter $reportExtension )
Write-Verbose 'Set the reports dataset to the location where the dataset lives in sharepoint'
foreach ($ds in $datasetObjects)
{
$status = set-SSRSDataSetInfo -reportWebService $webservice -reportPath "$documentLibrary/$($ds.FileName)" -dataSetName $ds.DataSetName -dataSetPath "$TargetDatasetFolder/$($ds.dataSetName)$datasetfilter"
Write-Output "The following: `t`t $($ds.datasetname) was set for $($ds.Filename)"
}
Write-Verbose 'Set the datasource for each data set'
$reports = New-DataSetDatasourceObject -files (Get-ChildItem -Path $reportPath -Filter "*$datasetFilter")
foreach ($report in $reports)
{
if ($report.datasourcename -eq $DataSource)
{
$status = Set-SSRSDataSourceInfo -DocLibUrl $TargetDatasetFolder -DataSourceName "$dataSourceTarget/$datasourceFileName" -reportDataSourceName 'DataSetDataSource' -FileName $report.filename -reportWebService $webservice -method SET -extFilter "*$datasetfilter"
write-output "The following: `t`t $report datasource was updated to $dataSourceTarget/$datasourceFileName"
}
$updateCacheFile = $cacheFiles2Set | Where-Object {$_.cachefile -eq $report.filename}
$cachevalue = $updateCacheFile.minutes
if ($Updatecachefile)
{
Set-CacheOptions -reportWebService $webservice -reportPath "$TargetDatasetFolder/$($report.filename)" -Minutes $cacheValue
Write-Output "___________RSD Cache_________________"
Write-Output "The following: `t`t $documentlibrary/$($report.filename) Cache was set to $cacheValue"
Write-Output "_____________________________________"
}
}
}
}
function Deploy-NativeSSRS
{
param(
[parameter(mandatory)][string]$documentLibrary,
[parameter(mandatory)][string]$dataSourceTarget,
[parameter(mandatory)][string]$webservice,
[parameter(mandatory)][string]$username,
[parameter(mandatory)][string]$password,
[object]$datasets,
[string]$datasetFilter,
[string]$TargetDatasetFolder,
[parameter(mandatory)][string]$DataSource,
[parameter(mandatory)][string]$reportPath,
[parameter(mandatory)][string]$connectString,
[parameter(mandatory)][string]$reportExtension,
[parameter(mandatory)][string]$setDatasourceOnly,
[object]$cacheFiles2Set
)
$datasourcename = $DataSource
if (!((Test-ValidDataSource -datasource "$dataSourceTarget/$datasourcename" -reportwebService $webservice).Valid))
{
if (!((get-ssrsitem -reportWebServiceUrl $webservice -reportName $datasourceName -ItemType Datasource).path | Where-Object {$_ -eq "$dataSourceTarget/$datasourceName"}))
{
write-output "Creating folder structure in $($test.TargetServerURL)/$datasource"
new-ssrsfolder -reportwebservice $webservice -foldername $dataSourceTarget
}
New-SSRSDataSource -datasource $dataSourceName -path "$dataSourceTarget" -reportWebService $webservice -connectString $connectString -username $username -password $password -Extension SQL -enabled $true -windowsCredentials $true -credentialRetrieval Store -ImpersonateUser $true
Write-output "Datasource was created $dataSourceName using $connectString with username $username"
}
$reports = New-ReportObject -files (Get-ChildItem -Path $reportPath -Filter $reportExtension) #this is to allow for uploading the new reports
if ($setDatasourceOnly -eq $false)
{
foreach ($report in $reports)
{
$fileExt = $reportExtension.trim('*')
$reportname = $report.filename -replace $fileext, ''
Publish-SSRSItemNative -webServiceUrl $webservice -rdlFile "$($report.DirectoryName)\$($report.filename)" -reportName $reportname -reportFolder $documentlibrary -force $true
}
}
$reports = New-ReportObject -files (Get-ChildItem -Path $reportPath -Filter $reportExtension) # this is done to make sure everything has been uploaded.
foreach ($report in $reports)
{
$fileExt = $reportExtension.trim('*')
$reportname = $report.FileName -replace $fileext, ''
if ($report.DataSourceName -eq $datasourcename)
{
$status = Set-SSRSDataSourceInfoNative -ReportName $reportname -reportPath $documentLibrary -DataSourceName $datasourceName -DataSourcePath "$dataSourceTarget/$datasourceName" -reportWebService $webservice
write-output "The following $report datasource was updated to $datasourcename"
write-output "The following: `t`t $($report.filename) datasource was updated to $dataSourceTarget/$datasourceFileName"
}
$updateCacheFile = $cacheFiles2Set | Where-Object {$_.cachefile -eq $report.FileName}
$cachevalue = $updateCacheFile.minutes
if ($Updatecachefile)
{
Set-NativeCacheOptions -reportWebService $webservice -reportPath "$documentlibrary/$reportname" -Minutes $cacheValue
#Set-CacheOptions -reportWebService $webservice -reportPath "$documentlibrary/$($report.filename)" -Minutes $cacheValue
Write-Output "___________RDL Cache_________________"
Write-Output "The following: `t`t$documentlibrary/$reportname Cache was set to $cacheValue"
Write-Output "_____________________________________"
}
}
if ($dataSets)
{
$fileExt = $datasetFilter.trim('*')
write-verbose "upload the dataset files"
if ($setDatasourceOnly -eq $false)
{
$dataSetObjects = New-DataSetDatasourceObject -files (Get-ChildItem -path $reportPath -filter "*$datasetFilter")
foreach ($dataset in $datasetObjects)
{
$reportname = $dataset.filename -replace $fileext, ''
Publish-SSRSItemNative -webServiceUrl $webservice -rdlFile "$($dataset.DirectoryName)\$($dataset.filename)" -reportName $reportname -reportFolder $TargetDatasetFolder -Type DataSet -force $true
}
}
Write-verbose "Get all the reports that have data sets in them"
Write-Verbose 'Set the reports dataset to the location where the dataset lives in native'
Write-verbose 'if a report has a dataset this loop sets the dataset in the report to the data set location'
$dataSetObjects = New-DataSetObject -files (get-childitem -path $reportpath -filter $reportExtension)
$reportExt = $reportExtension.trim("*")
foreach ($ds in $datasetObjects)
{
$status = set-NativeSSRSDataSetInfo -reportWebService $webservice -reportPath "$documentLibrary/$($ds.FileName -replace $reportExt,'')" -dataSetName $ds.DataSetName -dataSetPath "$TargetDatasetFolder/$($ds.dataSetName)"
Write-Output "The following: `t`t $($ds.datasetname) was set for $($ds.Filename)"
}
Write-Verbose 'Set the datasource for each data set'
$reports = New-DataSetDatasourceObject -files (Get-ChildItem -Path $reportPath -Filter "*$datasetFilter")
foreach ($report in $reports)
{
$reportname = $report.FileName -replace $fileext, ''
if ($report.DataSourceName -eq $datasourcename)
{
$status = Set-SSRSDataSourceInfoNative -ReportName $reportname -reportPath $TargetDatasetFolder -DataSourceName 'DataSetDataSource' -DataSourcePath "$dataSourceTarget/$datasourceName" -reportWebService $webservice
write-output "The following: `t`t $report datasource was updated to $dataSourceTarget/$datasourceFileName"
}
$updateCacheFile = $cacheFiles2Set | Where-Object {$_.cachefile -eq $report.FileName}
$cachevalue = $updateCacheFile.minutes
if ($Updatecachefile)
{
#$r = $report.FileName
Set-NativeCacheOptions -reportWebService $webservice -reportPath "$TargetDatasetFolder/$reportname" -Minutes $cacheValue
Write-Output "___________RDL Cache_________________"
Write-Output "The following: `t`t$TargetDataSetFolder/$reportname Cache was set to $cacheValue"
Write-Output "_____________________________________"
}
}
}
}
<#
.Synopsis
Tests a url to see if Sharepoint url or some other url
.DESCRIPTION
Given a url tests to see if the url is sharepoint or not
.EXAMPLE
test-is2013Sharepointurl -url 'http://sharepoint.com'
returns true if it is a sharepoint 2013 url
#>
function Test-Is2013SharepointUrl
{
[OutputType([boolean])]
Param
(
# Param1 help description
[string]$Url,
[pscredential]$credentials
)
$issharepoint = $false
if (($url -like "http://*") -or ($url -like "https://*"))
{
if ($credentials)
{
try
{
$results = Invoke-WebRequest -uri $Url -Credential $credentials -ErrorAction Ignore
}
catch
{
write-verbose "Error was $_"
$line = $_.InvocationInfo.ScriptLineNumber
write-verbose "Error was in Line $line"
$issharepoint = $false
}
}
else
{
try
{ $results = Invoke-WebRequest -uri $Url -UseDefaultCredentials -ErrorAction Ignore}
catch
{
write-verbose "Error was $_"
$line = $_.InvocationInfo.ScriptLineNumber
write-verbose "Error was in Line $line"
$issharepoint = $false
}
}
if ($results.headers.psobject.Properties.value | Where-Object {$_ -like 'MicrosoftSharePointTeamServices'})
{
$issharepoint = $true
}
}
else
{
throw "$url is not a HTTP or HTTPS url"
}
$issharepoint
}
param
(
[String] [Parameter(Mandatory)] $SolutionFile,
#this is the evironment that is in the Solution config to read where things need to go (TEST/DEV/QA/Prodution)
#for example - C:\test\Test Automated Deployment Sharepoint.rptproj
[string] [Parameter(Mandatory)] $Environment,
#this is the name of the Datasource as it exists in Sharepoint without the .rsds extension
[string] [Parameter(Mandatory)] $DataSource,
#This is the connection string that will be set for the report data source.
#this is only necessary if this is the first time the reports are being deployed.
#'data source=PHBSRptDBStg ;initial catalog=homebuilderrep;Integrated Security=True'
[string] [Parameter(Mandatory)] $connectString ,
#the username that the Datasource will run under
#defaults to the user that this script is running under
[string] [Parameter(Mandatory)] $username = "$([environment]::UserDomainName)\$([environment]::Username)",
[string] [Parameter(Mandatory)] $Password = 'password',
[string] $reportExtension = '*.rdl'
)
#$environment = 'test'
if(test-path $solutionfile -pathtype Leaf)
{
$reportPath = (get-item $solutionfile).DirectoryName
[xml]$x = get-content $solutionfile
$test = ($x.Project.Configurations.configuration |?{$_.name -eq $environment}).options
$webservice = "$($test.TargetServerURL)_vti_bin/ReportServer/ReportService2010.asmx"
$dataSourceTarget = $test.TargetDataSourceFolder
$documentLibrary = $test.TargetFolder
$d = gci "$reportPath\$datasource*.rds"
$dataSourceName = $d.name -replace $d.Extension,'' #remove the extension from the reportdatasource physical filename.
#[xml]$rdsXml = get-content (gci "$reportPath\$datasource.rds")
#$connectString = $rdsXml.RptDataSource.ConnectionProperties.ConnectString
#$username = 'phcorp\servicedssdev' #"$([environment]::UserDomainName)\$([environment]::Username)"
#$password = ''
#$reportExtension = '*.rdl'
if($MyInvocation.MyCommand.Path)
{
$scriptpath = $MyInvocation.MyCommand.Path
$d = Split-Path $scriptpath
write-output -InputObject "Script Directory -->$D"
import-module "$d\sqlreporting.psd1" -Force
}
else{import-module .\sqlreporting.psd1 -Force}
if(($datasourceTarget -like "http://*") -or ($dataSourceTarget -like "https://*")) # this is to tell the script that we are deploying to sharepoint
{
$datasourceFilename = "$($DataSource).rsds"
if(!((Test-ValidDataSource -datasource "$dataSourceTarget/$datasourcefilename" -reportwebService $webservice).Valid))
{
New-SSRSDataSource -datasource $dataSourceFileName -path $dataSourceTarget -reportWebService $webservice -connectString $connectString -username $username -password $password -Extension SQL -enabled $true -windowsCredentials $true -credentialRetrieval Store -ImpersonateUser $true
Write-output "Datasource was created $dataSourceName using $connectString with username $username"
}
Set-UploadReport -destpath $documentLibrary -LocalFilePath $reportPath -fileExt $reportExtension -method PUT
$reports = New-ReportObject -files (Get-ChildItem -Path $reportPath -Filter $reportExtension)
foreach($report in (($reports | Where-Object{$_.datasourcename -eq $datasourceName}).filename))
{
$status = Set-SSRSDataSourceInfo -DocLibUrl $documentLibrary -DataSourceName "$dataSourceTarget/$datasourceFileName" -reportDataSourceName $datasourceName -FileName $report -reportWebService $webservice -method SET
write-output "The following $report datasource was updated to $dataSourceTarget/$datasourceFileName"
}
}
else #Native SSRS deployment falls in here
{
if(!((Test-ValidDataSource -datasource "$dataSourceTarget/$datasourcename" -reportwebService $webservice).Valid))
{
if(!((get-ssrsitem -reportWebServiceUrl $webservice -reportName $datasourceName -ItemType Datasource).path | Where-Object{$_ -eq "$dataSourceTarget/$datasourceName"}))
{
write-output "Creating folder structure in $($test.TargetServerURL)/$datasource"
new-ssrsfolder -reportwebservice $webservice -foldername $dataSourceTarget
}
New-SSRSDataSource -datasource $dataSourceName -path $dataSource -reportWebService $webservice -connectString $connectString -username $username -password $password -Extension SQL -enabled $true -windowsCredentials $true -credentialRetrieval Store -ImpersonateUser $true
Write-output "Datasource was created $dataSourceName using $connectString with username $username"
}
$reports = New-ReportObject -files (Get-ChildItem -Path $reportPath -Filter $reportExtension) #this is to allow for uploading the new reports
Publish-SSRSReportNative -webServiceUrl $webservice -rdlFile "$($reports.DirectoryName)\$($reports.filename)" -reportFolder $documentlibrary -force $true
$reports = New-ReportObject -files (Get-ChildItem -Path $reportPath -Filter $reportExtension) # this is done to make sure everything has been uploaded.
foreach($report in (($reports | Where-Object{$_.datasourcename -eq $datasourceName}).filename))
{
$fileExt = $reportExtension.trim('*')
$status = Set-SSRSDataSourceInfoNative -ReportName ($report.trim($fileext)) -reportPath $documentLibrary -DataSourceName $datasourceName -DataSourcePath "$dataSourceTarget/$datasourceName" -reportWebService $webservice
write-output "The following $report datasource was updated to $datasourcename"
}
}
}
@crshnbrn66
Copy link
Author

Updated with latest copy that works for me.

@silverl
Copy link

silverl commented Jul 6, 2018

Line 1379, looks like another whole copy of the script was pasted in here by accident.

@crshnbrn66
Copy link
Author

thank you I missed that.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment