Running PowerShell Scripts From Within An SSRS 2008 DataSet
A couple of weeks ago, I tweeted a question out to the community about whether there was any way to run PowerShell scripts from within a SQL Server Reporting Services 2008 DataSet query. The answers that I received back were discouraging. Even though Microsoft is touting PowerShell as the best thing since sliced bread (I have to admit, it is pretty darned cool!) and integrating into all of its current products, the integration with SQL Server 2008 seems to have stopped at giving you a mini PowerShell console (SQLPS) and a SQL Server provider to give you easier access to running SQL Server commands from within PowerShell. This integration hasn’t gone beyond the database engine, so if you want to run PowerShell scripts from within Reporting Services, you have to get creative. That posed a big problem for me because the report I was writing depended on some PowerShell scripts that I had written.
After walking away from the problem for an hour or two, it finally hit me. Since a Reporting Services 2008 DataSet query runs T-SQL code, including stored procedures, why don’t I just write a stored procedure that I can use to run a PowerShell script. Below, is the stored procedure that I wrote. It is really pretty simple. It takes as a parameter, the command line command that you would normally type in at a PowerShell command line to run your script. This information would include the script path\name and any parameters that the PowerShell script requires.
USE [MyDatabase]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[Utility].[RunPowerShellScript]‘) AND type in (N’P', N’PC’))
DROP PROCEDURE [Utility].[RunPowerShellScript]
GO
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [Utility].[RunPowerShellScript]
@Script varchar(2000)
AS
set nocount on;
declare @cmd varchar(2000);
set @cmd = ’sqlps -c ‘ + ‘”‘ + @Script + ‘”‘
exec master..xp_cmdshell @cmd, no_output;
GO
In the above code, “[MyDatabase]” of course refers to the database that you would want this stored procedure to be stored in. So, walking through the code, all this script really does is create a stored procedure called Utility.RunPowerShellScript that runs the xp_cmdshell extended stored procedure with a command string that calls the SQL Server PowerShell mini console (SQLPS) in command line mode and passes to that command line whatever you passed into the stored procedure as a parameter. For my own purposes, I have created a schema called “Utility” so that I can easily identify stored procedures, such as this one, as helper stored procedures. Feel free to omit this if you like. So an example of how you could use this stored procedure after you have created it would be as follows. Say you wanted to run a PowerShell script called “Get-DriveSpace” that returns total size and free space information for the drives on a server that you pass in and resides in the D:\Scripts folder. All you would need to do is type:
exec DBA_Console.Utility.RunPowerShellScript‘”D:\Scripts\Get-DiskSpace.ps1 -Server MyServer”‘
Where “MyServer” is the name of the server that you are passing into the PowerShell script as a parameter.
That’s it. Short and simple and now I have a mechanism to call all of the PowerShell scripts that I want from within a Reporting Service DataSet query.
Tweets that mention Running PowerShell Scripts From Within An SSRS 2008 DataSet - SQLServerTimes2 -- Topsy.com
October 12th, 2009 at 7:13 am #
[...] This post was mentioned on Twitter by Ted Krueger. Ted Krueger said: Pretty cool idea! RT @loriedwards: [Blog]Running PowerShell Scripts From Within An SSRS 2008 DataSet http://bit.ly/tn24A [...]
nintendost
November 16th, 2009 at 11:37 pm #
Article very interesting, I will necessarily add it in the selected works and I will visit this site
forex robot
November 18th, 2009 at 5:20 am #
Keep posting stuff like this i really like it.
Bodestone
November 27th, 2009 at 9:10 am #
This is something i have just been looking at but quite often I’d be interested in the output too.
For me I’d just put
exec master..xp_cmdshell ’sqlps -c “MyScript”‘, no_output;
In the t-sql.
I am pondering a CLR function though, that takes script name as a param and returns console output as a one column table.
It could then be directly queried and joined to for instance.
If I get round to it and it turns out nice I’ll update.
Paydaymister
December 2nd, 2009 at 5:52 am #
author’s note seemed to me very helpful and changed my outlook on many things.
Figarowexs
December 2nd, 2009 at 3:14 pm #
Article very interesting, I will necessarily add it in the selected works and I will visit this site
Antiagingmen
December 3rd, 2009 at 12:16 am #
Thanks to your article I really liked. Be sure to add your blog to their favorites
Antiaginmoon
December 5th, 2009 at 1:26 am #
You have a very cool blog! Thanks for this review, I found a lot of new and interesting. You are in my bookmarks
Paydaymen
December 27th, 2009 at 9:47 am #
Thank you for a good story, I really enjoyed your blog. Be sure to give a link to your friends!
Dublemen
January 9th, 2010 at 11:37 pm #
Hello, I want to congratulate your site with 2010. I am sure that, in this new year, your article will please his readers.
Каталог статей
February 1st, 2010 at 8:05 pm #
Should you tell it — a false way.