Resolving SSRS and PowerShell New-WebServiceProxy Namespace Issue

When you’re working with PowerShell and SSRS, you may occasionally come across a script that works once, then just mysteriously decides not to work anymore on a second or third invocation. Or it may just not work period, even though you think the syntax is short and straightforward, and you know you’re not misspelling any syntax.

Please note I am running this on the PowerShell ISE, and PowerGUI – and tried on both PowerShell V2 and V3.

Common Task

What was driving me crazy (at some point, I promise I’m back to my sane self now) was trying to create a folder with property. The syntax is pretty straightforward, like this:

Import-Module SQLPS -DisableNameChecking;
$ReportServerUri  = "http://localhost/ReportServer/ReportService2010.asmx"
$proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential -Namespace SSRS ;
$proxy
$proxy.GetType().Namespace;	#gives me SSRS

#create a folder
$property = New-Object  "SSRS.Property" 
$property.Name = "Description"
$property.Value = "This folder is for any HR related reports"

#we need a property array to pass to the CreateFolder method
$properties = New-Object "SSRS.Property[]" 1;
$properties[0] = $property;
$foldername = "HR_" + (Get-Date -format "yyyy-MMM-dd-hhmmtt");
$proxy.CreateFolder($foldername, "/", $properties);

Broken??

Should be simple, right? PowerShell says, nope, not today. You get this error:

Cannot convert argument "Properties", with value: "SSRS.Property", 
for "CreateFolder" to type "SSRS.Property[]": 
"Cannot convert the "SSRS.Property" value of type "SSRS.Property"
to type "SSRS.Property"." 
At C:UsersAdministratorScriptsSSRSSSRS.ps1:22 char:1
+ $proxy.CreateFolder($foldername, "/", $properties);
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

Uh, what? Doesn’t that sound a little – wrong? It’s complaining about casting from SSRS.Property to SSRS.Property. It’s the same thing!

Also, this code works in every other .NET language (modified using that language’s syntax of course) .. why won’t it work with PowerShell? I’m definite it’s not the way I’m using arrays. That would be my first culprit if I were to investigate, because it looks weird. But I know that works, I’ve used it in so many other scripts.

So what is it?

The Problem

It looks like even when you use the -Namespace argument, PowerShell will still use and expect some of the autogenerated namespaces in the arguments. In reality, you may be using the “SSRS” namespace, but in the background it may be expecting the real, fully qualified autogenerated namespace name, like:

Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy23tServer_ReportService2010_asmx.Property

This becomes problematic, doesn’t it?

The WorkAround

The workaround is to not use the namespace, and to use the autogenerated namespace in all the references. Here’s the modified script:

cls
$ReportServerUri  = "http://localhost/ReportServer/ReportService2010.asmx"

#note no -Namespace argument
$proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential ;

#get autogenerated namespace
$type = $Proxy.GetType().Namespace
$datatype = ($type + '.Property')

#display datatype, just for our reference
$datatype

#here we create a new object based on the autogenerated namespace
#this would be equivalent to the following line in the original script:
#$property = New-Object  "SSRS.Property" 
$property =New-Object ($datatype);
$property.Name = "Description"
$property.Value = "Finance"

#generate timestamped foldername
$foldername = "HR_" + (Get-Date -format "yyyy-MMM-dd-hhmmtt");

#Report SSRS Properties
#http://msdn.microsoft.com/en-us/library/ms152826(v=sql.90).aspx
$numproperties = 1
$properties = New-Object ($datatype + '[]')$numproperties 
$properties.GetType().Name 
$properties[0] = $property;

$proxy.CreateFolder($foldername, "/", $properties);

There you go. Hopefully this saves you some time, and some headaches!

Advertisements

5 comments

  • I’m having a similar problem to this trying to add a login to a role in SSRS, using the SetPolicies method. The error I get is:
    Cannot convert the “ReportService2010.Policy” value of type “ReportService2010.Policy” to type “ReportService2010.Policy”.

    I’ve tried adapting what you’ve written here for this problem as well with no luck so far; in the mean time I thought it was worth asking if you’ve tried doing this and made it work?

    Thanks

    Julia

    Like

    • Hi Julia

      Yes I have used this script several times, works this way; what version of PowerShell and SQL Server are you using?

      Donabel

      Like

  • Glad I found this, although I was getting the same error [same type of error on DataSourceReference type] on SetItemDataSources for a report model. Now I have completed automated deployment for SSRS. Thanks!

    Like

  • Thanks, big help!!….any idea why this happens?? I had the same issue with SetPolicies Policy type. I’m using PS 3.0 Win2008 Server SP2. SSRS 2008R2.

    Like

  • There has got to be a better way to do this. Powershell, for the first time ever I am disappointed.
    Complex objects returned by a web service that have complex objects as properties require this trick to be applied recursively.
    I make a call to a web service and get back an object and then have to find what namespace it got assigned and what namespace each of its complex properties got assigned and then instantiate a new object with properties that match (I don’t know how to do this off hand, I am sure its possible though) and then recreate the existing object by copying each of its properties to the newly created complex object if I want to pass it back into a different web service method using a different URI…

    If only there was some kind of namespace parameter (that actually worked)

    thank you for listening, great blog post!

    I think I am going to do this project in C#. sad times. sad sad times.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s