Create SSRS DataSource from Powershell

PowerShell Help
1

Hello all I am trying to create DataSource using powershell,

function Create-SSRSDataSource1
{
[CmdletBinding()]

param(
     
     [Parameter(Mandatory = $false)]
     [string]$DataSourcePath='D:\TestTFS\Report Project1\Report Project1\SampleReport.rds',
     
     [string]$path,
     
     [Parameter(Mandatory = $false)]
     [string]$reportWebService,
     
     [string]$connectString="Data Source=.;Initial Catalog=forumsApp",
     [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.PSCredential]$credentials
    )

    if (-not $reportWebService.EndsWith('.asmx')) {
		if (-not $reportWebService.EndsWith('/')) {
			$reportWebService += '/'
		}
		$reportWebService += 'ReportService2010.asmx'
	}

    $ssrsproxy = New-WebServiceProxy -Uri $reportWebService -UseDefaultCredential -Namespace "SSRS"

    [xml]$XmlDataSourceDefinition = Get-Content $DataSourcePath;

    $xmlDataSourceName = $XmlDataSourceDefinition.RptDataSource | where {$_ | get-member ConnectionProperties};

    $path = $reportWebService + "/"

    try
    { 
        $type = $ssrsproxy.GetType().Namespace;
    }
    catch
    { 
        throw $_.Exception;
    }
    
    $dataSourceDefinitionType = ($type + '.DataSourceDefinition');
    $dataSourceDefinition = New-Object ($dataSourceDefinitionType);
    $dataSourceDefinition.Extension = $xmlDataSourceName.ConnectionProperties.Extension; #SQL

    $dataSourceDefinition.ConnectString = $ConnectString
    $credentialRetrievalDataType = ($type + '.CredentialRetrievalEnum'); 
    $credentialRetrieval = new-object ($credentialRetrievalDataType);
    #$credentialRetrieval.value__ = 1;# Stored
    $dataSourceDefinition.CredentialRetrieval = $credentialRetrieval;
    $dataSourceDefinition.WindowsCredentials = $true;
    $dataSourceDefinition.UserName = "wallero";
    $dataSourceDefinition.Password = "wallero@123";
    try
    { 
        $newDataSource = $ssrsproxy.CreateDataSource($xmlDataSourceName.Name,$path,$true,$dataSourceDefinition,$null); 
    }
    catch
    { 
        throw $_.Exception; 
    }
    
}

Create-SSRSDataSource1  -path "/SampleReport" -reportWebService "http://my-pc:85/ReportServer" -username "abcuser" -password "abcuser@123"

I am getting the exception as follows

Cannot convert argument “Definition”, with value: “SSRS.DataSourceDefinition”, for “CreateDataSource” to type “SSRS.DataSourceDefinition”:
“Cannot convert the “SSRS.DataSourceDefinition” value of type “SSRS.DataSourceDefinition” to type “SSRS.DataSourceDefinition”.”
At C:\Users\Wallero\Downloads\Reporting-Only-Install_merge_5.1.17016.02\Create-SSRSDataSource1.ps1:71 char:9

  •     $newDataSource = $ssrsproxy.CreateDataSource($xmlDataSourceNa ...

  •     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : NotSpecified: (:slight_smile: , ParentContainsErrorRecordException
    • FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument
2

Unfortunate I think you might have better luck in a forum dealing with SSRS. You’re not really encountering a PowerShell problem, here; this is core .NET and it may be outside our audiences experience.

3

I have some code that does create a datasource and some writeups for this
my blog:

See this gist: SQLReporting.psm1 · GitHub