Last active
August 30, 2018 15:19
-
-
Save crshnbrn66/b10e43ef0dadf7f4eeae620428b2cdd9 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # 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 | |
| } | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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" | |
| } | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Line 1379, looks like another whole copy of the script was pasted in here by accident.