Powershell DPE is not working with SQL Server 2012 /SSDT

Jun 4, 2014 at 8:59 AM
Hi

The Powershell DPE is working fine with BIDS (Visual Studio 2008), but not with SSDT (Visual Studio 2010), nor when SSRS reports are deployed to SSRS 2012. For some reason the extension can not be loaded...

Anyone that has succeeded? SQL 2008 is "old" now, and it's a shame this excellent extension is not usable in SQL 2012 and SQL 2014.

An update for later SQL releases would be much appreciated!

Brgds
Håkan Björkqvist
Jun 5, 2014 at 11:34 PM
Hi, thanks for reporting the issue. I've started trying to repro on SQL 2012 using the 'SQL Server 2012 SP1 Enterprise on Windows Server 2012' VM Image from Azure.

I rebuilt the assembly using the SSRS assembly from SQL 2014, found here:
C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.Interfaces.dll

I had to tweak the install script slightly but was able to get the DPE loaded in BIDS (Visual Studio 2010 Shell).

Trying now to get the DPE to load in Report Manager...
Jun 5, 2014 at 11:55 PM

Good news:

  • I was able to use BIDs to design and publish a report to Report Server.
  • I was able to use BIDs to preview the dataset using the Query Designer
  • I was able to use Report Manager to run the report

Bad news:

  • I was not able to preview the report in BIDs, got this error:
An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset 'DataSet1'.
The type initializer for 'System.Management.Automation.SessionStateScope' threw an exception.
Dynamic operations can only be performed in homogeneous AppDomain.
  • I had issues with some scripts in the Query Designer, workaround was to Select-Object to get the properties I wanted. Error was:
An error occurred while reading data from the query result set.

Status

Looks like there is still work to do on this thing, but it is limping along with a couple of workarounds. Now that I have the repro's and it is pretty easy to setup I should be able to make some progress on it.
Jun 10, 2014 at 7:50 AM
Hi Craig

Sounds promising! I'm "stand-by". Personally I can do without Query Manager (hardly ever use it). As a workaround I can use BIDS (VS 2008) to develop reports and deploy to SSRS 2012. But an assembly for SSRS 2012 (Report Manager) is most needed from my perspective.

Need to have: Assembly on SSRS server (Report Maneger)
Nice to have: Preview in SSDT - workaround, use BIDS
Not needed: Query Manager

Many thanks!
Håkan Björkqvist
Jun 24, 2014 at 12:13 AM
Ah, that is pretty easy then. You should be able to just build against the SSRS 2012 DLL.
Jul 2, 2014 at 11:56 AM
Hi Craig

I managed to do that and to get simple reports working (without parameters). However I stumbled on some problems I could not solve
  • The reports seem to use the SSRS service account when starting PowerShell, not the DataSource credentials
  • For some reason parameter values are not working (not passed to the Powershell session?)
    • works in BIDS, but not in SSDT and not when deployed to SSRS 2012
Close, but not enough...

Best regards
Håkan Björkqvist