Creating a DataDriven Subscription from a file

In working with SSRS I found that if I wanted to do a delete of an RDL.  Problem is if I delete an RDL the Subscriptions are deleted as well.   So since I’ve put most all of my SSRS File management and Data source creation in Continuous Integration I needed a way to save off subscriptions before I attempted a delete.  I talk about how that is done in this post Saving SSRS Subscriptions to File.  This post will be about how I consume the saved off files and put the subscription in place in another environment.

If you’ve been following my other blog posts on SSRS you’ll know I’ve written about creating an SSRS Datasource. Testing an SSRS Datasource in each of these scripts I start with the following :  Reportservice2010.asmx Webservice to get to any functions that are needed to operate on SSRS. I assume you’ve read one of these articles and that you need to get a proxy to this.

Onto the method that we’ll call to recreate the DataDriven Subscription:

CreateDataDrivenSubscription A call to this method requires the following classes passed to it:

Path to where the report is : item

ExtensionSetttings – An ExtensionSettings object that contains a list of settings that are specific to the delivery extension.

DataRetrievalPlan – Type: ReportService2010.DataRetrievalPlan
A DataRetrievalPlan object that provides settings that are required to retrieve data from a delivery query. The DataRetrievalPlan object contains a reference to a DataSetDefinition object and a DataSourceDefinitionOrReference object.

description – Type: System.String A meaningful description that is displayed to users.

eventtype – Type: System.String
The type of event that triggers the data-driven subscription. The valid values are TimedSubscription or SnapshotUpdate

Matchdata – Type: System.String
The data that is associated with the specified EventType parameter. This parameter is used by an event to match the data-driven subscription with an event that has fired.

paramatervalueorfieldreference – Type: ReportService2010.ParameterValueOrFieldReference[]
An array of ParameterValueOrFieldReference objects that contains a list of parameters for the item.

Since I’m using a Powershell Class one of the first things I must make sure I do is have a proxy to the WebService I want to call. Everything else will fail if i haven’t done that first. Then I’ll read in my reportFiles that I want to operate on With Get-childitem.  I chose to use the xml export method.


$ssrsproxy = New-WebServiceProxy -Uri http://yourwebsite/yourreports/_vti_bin/ReportServer/ReportService2010.asmx -UseDefaultCredential -namespace 'SSRSProxy' -class 'ReportService2010'
$reportExportPath = 'C:\temp\reports3'
$reportFiles = Get-ChildItem $reportExportPath -Filter *.xml

Now that I have My reports that I want to operate on I can iterrate through each of these objects and rebuild the object and submit it to the new server.   So that I can change url’s from one server to another I Use a $source and $destination variable to assist.

Since each saved off report has 7 items in it we have to go through each item (object) in the xml and convert them back to the same type of object that the method call expects. So the first Object that we need to rebuild that is a little more complex is the $extensionsettings.  When you look at the extension settings they can have two other objects inside the extension setting. One of them is a ParameterFieldReference and the other is a Parametervalue.  So we have to test each of the names of the object properties to see what the name is so we know whether to build a ParameterFieldReference or a Parametervalue.


foreach($parameterField in $reportobject.extensionSettings.ParameterValues)
{
if($parameterfield.psobject.Properties.name[0] -eq 'ParameterName') #rebuild the object into an extenstion setting this one contains a parameter field reference
{
$a = [SSRSProxy.ParameterFieldReference]::new()
Write-Verbose 'Create a object of type ParameterField reference.'
$a.FieldAlias = $parameterfield.fieldalias
$a.ParameterName = $parameterfield.ParameterName
}
elseif($parameterfield.psobject.Properties.name[0] -eq 'Name') #rebuild the object into an extension settings object this one contains a param value
{
$a = [SSRSProxy.ParameterValue]::New()
Write-Verbose 'Create a object of type ParameterValue reference.'
$a.Label = $parameterField.Label
$a.Name = $parameterField.Name
$a.Value = $parameterField.Value
}
$paramvalues += $a
}
$extensionSettings.ParameterValues = [ssrsproxy.parametervalueorfieldreference[]]$paramvalues

Once we’ve built the extensionsettings we now need to rebuild the DataRetrievalPlan . The data retreival plan includes the reference to the new location for the DataSource.  this is where we use the source and destination to our advantage. This is done by settting the item on the dataretrieval plan to the datasource reference we wish to use using the object DataSourceReference


[SSRSProxy.DataRetrievalPlan]$DataRetrievalPlan = New-Object SSRSProxy.DataRetrievalPlan
 Write-Verbose 'Create a object of type DataRetrievalPlan reference.'

 $DataRetrievalPlan.DataSet = $reportobject.DataRetrievalPlan.DataSet

 $DataRetrievalPlan.DataSet = $reportobject.DataRetrievalPlan.DataSet
 [SSRSProxy.DataSourceReference]$dsReference = $reportobject.DataRetrievalPlan.Item
 $src = ([uri]$source).absoluteuri
 $dest = ([uri]$destination).absoluteuri
 $dsReference.Reference = (([uri]$dsReference.Reference).AbsoluteUri) -replace $src,$dest
 Write-Verbose "Datasource Reference $dsreference use the value for the datasource you want this data driven report to consume"
 $DataRetrievalPlan.Item = $dsReference

now the last few bits of information that need to be added is the ParameterValueorFieldReference and the report description, eventtype and match data.


$description = $reportobject.Description
 $eventtype = $reportobject.eventtype
 $matchdata = $reportobject.matchdata

$b = [Ssrsproxy.parameterfieldreference]::new()
 Write-Verbose 'Create a object of type parameterfieldreference reference.'
 $b.FieldAlias = $reportobject.parameters.fieldalias
 $b.ParameterName = $reportobject.parameters.ParameterName
 [SSRSProxy.ParameterValueOrFieldReference]$ParameterValueOrFieldReference = $b

Now that we have those set the last thing for us to do is to set the destination for the report where this should go.  Then we’ll call the method and hope we don’t hit an exception.


$itemPath = "$destination/$($reportobject.subscription.report)"
 try
 {
 Write-Verbose "Now that the object is re-constituted we can put this in the SSRS instance we wish to push it to"
 $ssrsproxy.CreateDataDrivenSubscription($itempath , $extensionsettings , $DataRetrievalPlan, $description, $eventtype, $matchdata, $ParameterValueOrFieldReference) 
 }
 Catch
 {
 "Error was $_"
 $line = $_.InvocationInfo.ScriptLineNumber
 "Error was in Line $line"
 }

 

Full script for this follows:

Write-Verbose " Destination for where the saved subscriptions will be pushed to"
$destination = 'http://yourwebsite/sites/datasourcetest/Shared%20Documents'
$source = 'http://yourwebsite/sites/Reports/Shared%20Documents'

$reportFiles = Get-ChildItem $reportExportPath -Filter *.xml
foreach($file in $reportFiles)
{
 $reportobject = Import-Clixml -path ($file.fullname)
 Write-Verbose "Create a object of type ExtensionSettings"
 $extensionSettings = New-Object -typename 'SSRSProxy.ExtensionSettings' 
 $extensionSettings.Extension = $reportobject.extensionSettings.Extension
 $paramvalues = @()
 foreach($parameterField in $reportobject.extensionSettings.ParameterValues)
 {
 if($parameterfield.psobject.Properties.name[0] -eq 'ParameterName') #rebuild the object into an extenstion setting this one contains a parameter field reference
 {
 $a = [SSRSProxy.ParameterFieldReference]::new()
 Write-Verbose 'Create a object of type ParameterField reference.'
 $a.FieldAlias = $parameterfield.fieldalias
 $a.ParameterName = $parameterfield.ParameterName
 }
 elseif($parameterfield.psobject.Properties.name[0] -eq 'Name') #rebuild the object into an extension settings object this one contains a param value
 {
 $a = [SSRSProxy.ParameterValue]::New()
 Write-Verbose 'Create a object of type ParameterValue reference.'
 $a.Label = $parameterField.Label
 $a.Name = $parameterField.Name
 $a.Value = $parameterField.Value
 }
 $paramvalues += $a
 }
 $extensionSettings.ParameterValues = [ssrsproxy.parametervalueorfieldreference[]]$paramvalues

 [SSRSProxy.DataRetrievalPlan]$DataRetrievalPlan = New-Object SSRSProxy.DataRetrievalPlan
 Write-Verbose 'Create a object of type DataRetrievalPlan reference.'

 $DataRetrievalPlan.DataSet = $reportobject.DataRetrievalPlan.DataSet
 [SSRSProxy.DataSourceReference]$dsReference = $reportobject.DataRetrievalPlan.Item
 $src = ([uri]$source).absoluteuri
 $dest = ([uri]$destination).absoluteuri
 $dsReference.Reference = (([uri]$dsReference.Reference).AbsoluteUri) -replace $src,$dest
 Write-Verbose "Datasource Reference $dsreference use the value for the datasource you want this data driven report to consume"
 $DataRetrievalPlan.Item = $dsReference
 $description = $reportobject.Description
 $eventtype = $reportobject.eventtype
 $matchdata = $reportobject.matchdata

 $b = [Ssrsproxy.parameterfieldreference]::new()
 Write-Verbose 'Create a object of type parameterfieldreference reference.'
 $b.FieldAlias = $reportobject.parameters.fieldalias
 $b.ParameterName = $reportobject.parameters.ParameterName
 [SSRSProxy.ParameterValueOrFieldReference]$ParameterValueOrFieldReference = $b

 $itemPath = "$destination/$($reportobject.subscription.report)"
 try
 {
 Write-Verbose "Now that the object is re-constituted we can put this in the SSRS instance we wish to push it to"
 $ssrsproxy.CreateDataDrivenSubscription($itempath , $extensionsettings , $DataRetrievalPlan, $description, $eventtype, $matchdata, $ParameterValueOrFieldReference) 
 }
 Catch
 {
 "Error was $_"
 $line = $_.InvocationInfo.ScriptLineNumber
 "Error was in Line $line"
 }
}

I hope this helps someone

Until then keep Scripting

Thom

Advertisements

Saving SSRS Subscriptions to File

In working with SSRS I found that if I wanted to do a delete of an RDL.  Problem is if I delete an RDL the Subscriptions are deleted as well.   So since I’ve put most all of my SSRS File management and Data source creation in Continuous Integration I needed a way to save off subscriptions before I attempted a delete.  This article is about the PowerShell I wrote to accomplish this task.

If you’ve been following my other blog posts on SSRS you’ll know I’ve written about creating an SSRS Datasource. Testing an SSRS Datasource in each of these scripts I start with the following :  Reportservice2010.asmx Webservice to get to any functions that are needed to operate on SSRS. I assume you’ve read one of these articles and that you need to get a proxy to this.

Onto the method that we’ll call to get the two different types of SSRS Subscriptions:

Normal subscriptions & DataDriven Subscriptions

For a normal subscription you’ll need to call the method listSubscriptions . This method expects the name of the report that you wish to get the subscriptions for.  To get the names of the reports we’ll use the .listChildren method and list all the children of the current site and then find each subscription for each report.

function Get-Subscriptions
{
  param([object]$ssrsproxy, [string]$site, [switch]$DataDriven)
  write-verbose 'Path to where the reports are must be specified to get the subscriptions you want.. Root (/) does not seem to get everything'
  $items = $ssrsproxy.ListChildren($site,$true) | Where-Object{$_.typename -eq 'report'}
  $subprops = $ddProps= @()

Now that we have the list of reports in the $items variable we can now ask for the subscription foreach item


foreach($item in $items)
 {
 $subs = $ssrsproxy.ListSubscriptions($item.path)

}

Now that we have our Subscriptions in the $subs variable we can now check to see if there was a return result if there was a return result we can then the the properties of each subscription type. We’ll know that we have a DataDriven subscripition by the property each of the subscriptions in the $subs array (.isdatadriven -eq $true)


 if($subs)
 {
   foreach($sub in $subs)
   {

    if($sub.isdatadriven -eq 'true')
      {
        $ddProps += Get-DataDrivenSubscriptionProperties -subscription $sub -ssrsproxy $ssrsproxy
      }
      elseif(-not $DataDriven)
      {
        $subProps += Get-SubscriptionProperties -subscriptionid $sub -ssrsproxy $ssrsproxy
      }

    }
 }
 if($DataDriven)
 {$ddProps}
 else {
 $subprops
 }

Now Onto explaining the Get-DataDrivenSubscriptionProperties and the Get-Subscription Properties.

The Get-SubscriptionProperties calls the method GetSubcriptionProperties with the ID of the subscription and then returns 7 objects through a reference.   In the function below I first set all the 7 reference variables to null then I call the method.  On successful return from the method I add to my powershell object [SSRSObject] (which is a  powershell class). If I choose to not use a class and instead want to use a standard object the standard object code is commented out so that it can be used if needed.


function Get-SubscriptionProperties
{
 param([string]$Subscription,
 [object]$ssrsproxy)
 $subextensionSettings = $subDataRetrievalPlan = $subDescription = $subactive = $substatus = $subeventtype = $submatchdata = $subparameters = $Null

 $subOwner = $ssrsproxy.GetSubscriptionProperties($subscription.SubscriptionID,[ref]$subextensionSettings,[ref]$subDescription,[ref]$subactive,[ref]$substatus,[ref]$subeventtype,[ref]$submatchdata,[ref]$subparameters)
 $ssrsobject = [SSRSObject]::New()
 $ssrsobject.subscription = $Subscription
 $ssrsobject.Owner = $subOwner
 $ssrsobject.ExtensionSettings = $subextensionSettings
 $ssrsobject.Description = $subDescription
 $ssrsobject.DataRetrievalPlan = $subDataRetrievalPlan
 $ssrsobject.Active = $subactive
 $ssrsobject.Status = $substatus
 $ssrsobject.EventType = $subeventtype
 $ssrsobject.MatchData = $submatchdata
 $ssrsobject.Parameters = $subparameters
 <#
 [PSCustomObject]@{
 'Owner' = $subOwner
 'extensionSettings' = $subextensionSettings
 'Description' = $subDescription
 'active' = $subactive
 'status' =$substatus
 'eventtype' =$subeventtype 
 'matchdata' = $submatchdata
 'parameters' = $subparameters
 }
 #>
}

For calling the Get-DataDrivenSubscriptionProperties we do all the same things as with the previous subscription type.  We call the method GetDataDrivenSubscriptionProperties it returns the same 7 reference objects.  On successful return from the method I add to my powershell object [SSRSObject] (which is a  powershell class). If I choose to not use a class and instead want to use a standard object the standard object code is commented out so that it can be used if needed.


function Get-DataDrivenSubscriptionProperties 
{
 param([object] $Subscription,
 [object]$ssrsproxy)
 $ssrsobject = [SSRSObject]::New()
 $sid = $Subscription.SubscriptionID
 $ddextensionSettings = $ddDataRetrievalPlan = $ddDescription = $ddactive = $ddstatus = $ddeventtype = $ddmatchdata = $ddparameters = $Null
 $ddOwner = $ssrsproxy.GetDataDrivenSubscriptionProperties($sid,[ref]$ddextensionSettings,[ref]$ddDataRetrievalPlan`
 ,[ref]$ddDescription,[ref]$ddactive,[ref]$ddstatus,[ref]$ddeventtype,[ref]$ddmatchdata,[ref]$ddparameters)

 $ssrsobject.subscription = $Subscription
 $ssrsobject.Owner = $ddOwner
 $ssrsobject.ExtensionSettings = $ddextensionSettings
 $ssrsobject.Description = $ddDescription
 $ssrsobject.DataRetrievalPlan = $ddDataRetrievalPlan
 $ssrsobject.Active = $ddactive
 $ssrsobject.Status = $ddstatus
 $ssrsobject.EventType = $ddeventtype
 $ssrsobject.MatchData = $ddmatchdata
 $ssrsobject.Parameters = $ddparameters
 $ssrsobject
 <# [PSCustomObject]@{
 'Owner' = $ddOwner
 'extensionSettings' = $ddextensionSettings
 'DataRetrievalPlan' = $ddDataRetrievalPlan
 'Description' = $ddDescription
 'active' = $ddactive
 'status' =$ddstatus
 'eventtype' =$ddeventtype 
 'matchdata' = $ddmatchdata
 'parameters' = $ddparameters
 } #>
}

Now that I have each of the reports in an object  I now persist this to disk with either Export-clixml or with convertto-json cmdlets

function New-XMLSubscriptionfile
{
[CmdletBinding()]
[Alias()]
param([psobject]$subscriptionObject, [string]$path)
if(test-path $path -PathType Leaf)
{
$path = split-path $path

}
if(-not(test-path $path))
{
mkdir $path
}
foreach($sub in $subscriptionObject)
{
$reportName = (($sub.subscription.report).split('.'))[0]
$filename = "$path\$reportName.xml"
$sub | Export-Clixml -Depth 100 -path $filename
}
}

function New-JsonSubscriptionFile
{
[CmdletBinding()]
[Alias()]
param([psobject]$subscriptionObject, [string]$path)
if(test-path $path -PathType Leaf)
{
$path = split-path $path

}
if(-not(test-path $path))
{
mkdir $path
}
foreach($sub in $subscriptionObject)
{
$reportName = (($sub.subscription.report).split('.'))[0]
$filename = "$path\$reportName.json"
$sub | convertto-json -Depth 100 | out-file $filename
}
}

To see the entire script see this Gist

I hope this helps someone

Until then keep Scripting

Thom

Create SSRS Data Source

Continuing on with my earlier discussion on SSRS and testing data sources.  I’ve come up with a means to create a data source.  This article will demonstrate how I did that.

In testing the data source I had to create a proxy to the SSRS server again we’ll need to do the same thing so we can get to the Create method for the Data source.

$reportWebService = 'http://yourReportServer/ReportServer/ReportService2010.asmx'
$credentials = Get-Credential
$reportproxy = New-WebServiceProxy -uri $reportWebService -Credential $credentials

The reportWebService is a link to my Webservice on my ssrs instance which when proxied will allow me to get at all the methods and properties of this class Reportservice2010.asmx

The method we’ll be using for this discussion is ReportingService2010.CreateDataSource.

This method requires three variables.

[string] DataSource,
[string] Parent,
[boolean]Overwrite,
[DataSourceDefinition] Definition,
[Property[] ]Properties

The Datasource is a String = The name for the data source including the file name and, in SharePoint mode, the extension (.rsds).

Parent = The fully qualified URL for the parent folder that will contain the data source.  In My case I’m going to use /ThomTest

Where the location from root on the SSRS server is the folder Named ThomTest.

Overwrite = This tells the function if it finds it to overwrite what is there.

DataSourceDefition = This is a DataSourceDefinition class that contains the values for the DataSource. This includes things like:

ConnectStringCredentialRetrieval, Enabled, EnabledSpecified ImpersonateUserImpersonateUserSpecifiedPasswordPrompt, UserName, WindowsCredentials

For each of the above properties here is what I’ve been able to discover so far for where they are used:

2016-08-03 16_08_38-Clipboard

[Property[] ]Properties =  ReportService2010.Property[]– an array of properties that are nearly the same thing as the data source definition. So some of the same data collected to create the data source definition is used in this property Array collection.

The tough part of this creation of the datasource was getting the values passed into the PowerShell function to be accepted by the proxied method.  In order to do this I stumbled on this great article on StackOverflow. This allowed me to get at the classes from the proxied webservice via calls similar to the one below:

$ssrsproxy = New-SSRSProxy -reportWebService $reportWebService `
-Credentials $credentials
 $proxyNameSpace = $ssrsproxy.gettype().Namespace

So in order to get to the class I need for the DataSourceDefinition .  All i need to do is take the ProxyName space and append it to the proxied name space.

$proxyNameSpace = $ssrsproxy.gettype().Namespace 
$datasourceDef = New-Object("$proxyNameSpace.DataSourceDefinition")

Now my $datasourceDef is a DatasourceDefinition object which contains the properties I showed above.  Since it is now in an object all I need to do now to set the items I need is to refer to them via . notation:

$datasourceDef = New-Object("$proxyNameSpace.DataSourceDefinition")
PS PS:\> $datasourceDef


Extension : 
ConnectString : 
UseOriginalConnectString : False
OriginalConnectStringExpressionBased : False
CredentialRetrieval : Prompt
WindowsCredentials : False
ImpersonateUser : False
ImpersonateUserSpecified : False
Prompt : 
UserName : 
Password : 
Enabled : False
EnabledSpecified : False

PS PS:\> $datasourcedef.Connectstring = 'MyConnectionSTring'

Ok now the fourth parameter is the tough one this is where I had to get help from @Poshoholic on how to get a hashtable for the values into a Array of properties that the create will accept.

Here is what the Hashtable looks like:

 PS PS:\> $datasourceDefHash = @{
 'ConnectString' = $connectString; 'UserName' = $username; `
'Password' = $password; 'WindowsCredentials' = $windowsCredentials; `
'Enabled' = $enabled; 'Extension' = $Extension; `
'ImpersonateUser' = $ImpersonateUser; 'ImpersonateUserSpecified' = $true; `
'CredentialRetrieval' = $credentialRetrieval
 }

My understanding of what is needed is a property Collection so I named my variable a property collection:

 $propertyCollection = $datasourceDefHash.Keys.foreach`
{ @{ Name = $_; Value = $dataSourceDefHash[$_] }`
 -as "${proxyNamespace}.property" }

The magic here is where we are iterating through our keys and then casting each name and value to the $proxynamespace.property which is our ReportService2010.Property[] array.  @Poshoholic informed that because the name of the class is dynamic we have to use the -as key word to allow it to be ‘cast’ into the property we need.  Wow I’m glad he helped me or I’d have been here a very long time.

Now to put it all together. I originally wrote this function to all for continuous deployments and creation of data sources. The only value I really wanted to use was the Storing of the username and password (Credentials stored securely in the report server). In addition I need the checkbox for this option checked ( Use as Windows credentials when connecting to the data source).  with the Username and password entered upon calling the function.

So here is what my param block looks like:

  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.PSCredential]$credentials
 )

Now that I have my user passing in their credentials and the items I need with the default values I can now call some of the methods and Items I described above:

  #https://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.createdatasource.aspx
 $ssrsproxy = New-SSRSProxy -reportWebService $reportWebService -Credentials $credentials
 $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"
 }

The actual piece that is doing the creation of the data source is this line

$ssrsproxy.CreateDataSource($DataSourceName, $path, $true, $datasourceDef, `
$propertyCollection)

The script in its full form is below:

<#
 .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.PSCredential]$credentials
 )
 #https://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.createdatasource.aspx
 $ssrsproxy = New-SSRSProxy -reportWebService $reportWebService -Credentials $credentials
 $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.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"
 $ssrsProxy = New-WebServiceProxy -Uri $reportWebService -UseDefaultCredential -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
 }
 $ssrsProxy
 }
 End { }
}

I hope this helps someone

Until then keep scripting

 

Thom