One way to bulk upload your XML files into SQL Server is by using PowerShell. In the script below I am using SQL Server 2012 and PowerShell V3 (CTP).
Note that you can use the script in SQL Server 2005/2008/R2 and PowerShell V2, with some slight changes in syntax.
I use Invoke-Sqlcmd to get this done.
I’ve been working with quite a few PowerShell/SMO scripts for the last little while, and so far so good. Just recently I needed to map some orphaned users, and I thought – why not? Let’s do it in PowerShell.
This would have been the script that *should* work:
#unfortunately this doesn't work
$user.Login = "marymargaret";
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.
I just have to blog this little gem. Every now and then I would be running a PowerShell script, I’d get an error but I wouldn’t get anything useful from the default error message besides the line number.
The PowerShell ISE is much improved in V3, needless to say I am really enjoying writing scripts with this tool! It is however, not without hiccups.
I was trying to run the Invoke-Sqlcmd cmdlet when I got the following error in the PowerShell ISE
Mixed mode assembly is built against version ‘v2.0.50727’ of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.
Basically just means PowerShell has support for side-by-side runtime, and we need to specify an activation policy in a config file. The config file name must be placed in the same directory as your exe file, and must be named the same as your exe file, plus a .config extension.
I wanted (needed!) to write a script to automatically generate a number of reports based on sets of parameters. Most of the scripting I’ve done before are for straight up parameters that I can pass using SMO or through the URL string.
This time, I had a cascading query-based parameter, ie one parameter that is filled out based on another parameter. I was doing my usual drill with PowerShell when I got the following infamous error:
“This report requires a default or user-defined value for the report parameter. To run or subscribe to this report, you must provide a parameter value.”
Which is weird because I did provide all the required parameters. A quick search leads me to a few blog posts, most of which mentioned that it’s because of my query based parameter. Most also suggest the resolution is to convert my parameter which has query-based parameter to be non-query based.
The only problem is I can’t. And I don’t want to. That’s not a resolution. This report is meant to be used both by users for ad hoc purposes, and for massive downloading for month-end reports. Removing the query-based values for a parameter *IS NOT* a resolution. Not for me anyway.
Here’s a short PowerShell script that :
1. Connects to your report server
2. Loops through a set of parameters stored in an array
3. Saves the PDF version of the report to a local folder, with appropriate names
Here’s a snippet of code to set your SSRS parameters programmatically using PowerShell
$params = $null;
#here's a sample usage http://msdn.microsoft.com/en-us/library/microsoft.reporting.winforms.reportparameterinfo(v=vs.80).aspx
$params = new-object 'Microsoft.Reporting.WinForms.ReportParameter' 3
$params = new-Object Microsoft.Reporting.WinForms.ReportParameter("FISCALYEAR", $fiscalyear, $false);
$params = new-Object Microsoft.Reporting.WinForms.ReportParameter("MONTHENDDATE", $monthenddate, $false);
$params = new-Object Microsoft.Reporting.WinForms.ReportParameter("SALESGROUP", $salesgroup, $false);
Of course this is just a sample, you can definitely extend this by using SMO to automatically pull parameter values, or use values stored in a file (among a million other things you can do with ubercool PowerShell)