Create SSRS DataSource from Powershell

This topic contains 2 replies, has 3 voices, and was last updated by  Thom Schumacher 6 months, 3 weeks ago.

  • Author
    Posts
  • #62415

    powershelluser143
    Participant

    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: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

  • #62737

    Don Jones
    Keymaster

    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.

  • #67654

    Thom Schumacher
    Participant

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

    https://powershellposse.com/?s=datasource

    See this gist:

You must be logged in to reply to this topic.