Easy Installation of Your Own PowerShell Modules

I’ve been working a lot with PowerShell of late, developing modules and deploying them to my own Modules folder. This is a pretty basic set of tasks, something I do over and over. As such, I decided to script it!

First thing I wanted to do was ensure the WindowsPowerShell\Modules folder exists under the users Documents folder. To do that I created a simple function, Add-WindowsPowerShellFolder. If the WindowsPowerShell folder, and under it the Modules folder, don’t exist, they are created.

# This checks to see if the WindowsPowerShell folder is in the users
# document folder, then checks to see if the Modules folder exists under
# the WindowsPowerShell folder. If they do not exist, they are created.
# This is primarily a helper function for the Install-MyModule function
function Add-WindowsPowerShellFolder()
  $psUserPath = "C:\Users\$([Environment]::UserName)\Documents\WindowsPowerShell"
  if($(Test-Path $psUserPath) -eq $false)
    New-Item -ItemType Directory -Force $psUserPath

  $psUserModulePath = "$($psUserPath)\Modules"
  if($(Test-Path $psUserModulePath) -eq $false)
    New-Item -ItemType Directory -Force $psUserModulePath


Next, I check to see if the folder for the specific Module I’m wishing to deploy exists, and if not create it.

# This checks to see if the folder for the Module you want to install
# exists, and if not it adds it.
# This is primarily a helper function for the Install-MyModule function
function Add-ModuleFolder($ModuleName)
  $psUserModulePath = "C:\Users\$([Environment]::UserName)\Documents\WindowsPowerShell\Modules"

  $psModulePath = "$($psUserModulePath)\$($moduleName)"
  if($(Test-Path $psModulePath) -eq $false)
    New-Item -ItemType Directory -Force $psModulePath


Finally I call the main function, the Install-MyModule. This has two parameters, the name of the Module and a collection of files to copy.

First, I use the functions above to validate the needed folders exist. There is also a switch, –Replace, which will delete all files and subfolders in the target module folder.

After this is a simple routine that loops over each file and copies it to the target. This allows you to copy files from multiple folders all to the same target.

This routine does assume there are no subfolders to be deployed to the target module folder, which is pretty common.


# This is the main function of this script. It first ensures the requisite
# folders exist in order to deploy.
# If the -Replace switch is used, the target folder will be cleaned out
# prior to the copy.
# Next, it iterates over the list of files passed in and copies them to the
# target folder.
function Install-MyModule()
  param ( 
         [Parameter( Mandatory = $true,
                     ValueFromPipeline = $false,
                     ValueFromPipelineByPropertyName = $false,
                     HelpMessage = ‘Module Name.’
         [string] $ModuleName,
         [Parameter( Mandatory = $true,
                     ValueFromPipeline = $false,
                     ValueFromPipelineByPropertyName = $false,
                     HelpMessage = ‘File to deploy.’
         [string[]] $Files,
         [switch] $Replace
        )  # End the parameter block

    # Validate the PS folder exists

    # Set the path to the users modules folder
    $psUserModulePath = "C:\Users\$([Environment]::UserName)\Documents\WindowsPowerShell\Modules"

    # Add a new folder for the module name being installed
    Add-ModuleFolder -ModuleName $ModuleName

    # Set the path to the users module folder including the module to create
    $psModulePath = "$($psUserModulePath)\$($ModuleName)"

    # If the user passed the -Replace switch delete all files from
    # the target folder
    if ($Replace -eq $true)
      Remove-Item "$psModulePath\*.*" -Force -Recurse


    foreach($file in $files)
      # Copy our module to the users module folder (force will overwrite if there)
      Copy-Item $file `
                $psModulePath `

Using it is straight forward. First, place the above code into a PS1, then execute it to load the functions into memory. You could also put it in your profile if it is something you do often.

In this example I’ve developed a module called ZipCodeLookup. This example installs it for me into my modules folder.

Install-MyModule ‘ZipCodeLookup’ ‘C:\PS\MyCode\ZipCodeLookup.*’ –Replace

Now you have a simple function you can call to deploy your modules, making your development life a little easier. It can also come in useful when sending a module to a coworker or setting up some common scripts on a different machine. It takes care of making sure the folders are in place as well as copying in your module.

Chattanooga SQL Saturday June 27 2015–Zero to Hero with PowerShell and SQL Server

This Saturday, June 27 2015, I will be at SQL Saturday #410 in Chattanooga, TN. I’ll be presenting a session “Zero to Hero with PowerShell and SQL Server”, in which we’ll start with the basics of PowerShell, then move into working with SQL Server via the PS SQL Provider.

This is going to be an extremely fast paced session, so you may wish to download the code samples ahead of time. I have already uploaded the samples to the event site. Just go to the Sessions menu, Schedule, and you’ll see a bit download button under the session. As of right now I’m the last session of the day, in room 219, but that is subject to change so be sure to check the schedule upon arrival.

Be warned, there is far more code in the download then we’ll be able to cover in a one hour session. It is well commented though, and you should be able to understand it after the session.

If you are looking for even more PowerShell goodness, my friend Aaron Nelson ( @sqlvariant | blog ) is doing a PowerShell for Data Professionals just after lunch. Our two sessions should work well together for those interested in using PowerShell in the world of SQL Server.

Hope to see you there!

SSRS 2012 Report Manager can’t load Microsoft.ReportingServices.SharePoint.ObjectModel

So I did it again, I broke my SQL Server. Well, sort of. I have a Hyper-V VM of Windows Server 2012R2 I use for development. On it I had SQL Server 2012 Developer Edition with all the latest service packs. I recently needed to do some work with 2014 as well, so installed SQL Server 2014 Developer Edition side by side. Everything seemed happy, until I opened up the SQL Server 2012 Report Manager webpage. It looked OK at first, but when I started clicking on things I started getting this error:

System.Configuration.ConfigurationErrorsException: Could not load file or assembly ‘Microsoft.ReportingServices.SharePoint.ObjectModel’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference

Icky. So a web search turned up one hit, a connect item filed by Brian Judge:


At the bottom, Brian gives the clue on how to fix the issue when he says:

If I change the redirect to stay on for the following policies then the problem appears to be resolved:






Alas, there are no specific instructions on just how to change the redirect. For those not familiar with the way these things work, I wanted to amplify his fix.

First, open a command window in administrator mode. I used the one that came with Visual Studio (the Developer Command Prompt for VS2012).

Next, change directory by using the “cd” command to the first item in the list above. (Click on the pic for a bigger image, should you have poor eyesight).


Using the DIR command, we can see one directory with a version number followed by what appears to be a hash value of some type. Issue another CD into that folder.


Using the DIR command again you will find two files in that folder:


Use notepad to edit the one with the .config extenstion.


When it appears, you will see something like:


Simply change the number in the newVersion from 12, to 11.


Repeat the steps for all four of the folders in the list above.

Next, and this is important kids, you need to stop and restart your SQL Server 2012 Reporting Services service, or simply reboot the computer. After that, your SSRS 2012 Report Manager should start to behave normally again. I’ve also tested the 2014 Report Manager, and it seems to work fine after the changes were applied. (In theory it shouldn’t have been affected, but you can never be too careful).

If you found this post useful, do us a favor. Go to the Microsoft Connect article linked at the top and give it an up vote, so Microsoft will begin to take notice. Also thanks again to Brian Judge (whom I do not know but hope to meet) for filing the original bug and giving the clue to fixing it.

So You Think MDX is Hard? Presented at SQL Saturday Nashville Jan 17 2015

At SQL Saturday Nashville, on January 17 2015, I presented “So You Think MDX is Hard?”. Unfortunately the SQL Saturday website is having issues with code samples, so I have uploaded the presentation to my Technet Code site. You will find it at http://bit.ly/acmdx

Inside are three files, a PDF of the slide deck, the MDX script I ran, and the analysis services database as a backup file (abf) that you can restore to a server on which you have administrative rights. This sample was created in SQL Server 2012, although should work on 2014 and (although I haven’t tested) should work on 2008R2.

SQL Server SSIS SSDT Error – Method Not Found: Microsoft.SqlServer.Dts.Design.VisualStudio2012Utils.IsVisualStudio2012ProInstalled

Every so often, especially when setting up a new virtual machine, at some point I get this error when working in SSIS:




It happens often enough that, to be honest, I mostly wanted to make this as a record to myself and friends, but I am hopeful that you will be helped as well. And to give credit where it is due, I found the original answer at Stack Exchange:


The steps are pretty straight forward.

1.  First, if you have Visual Studio open, close it.

2. In the classic Windows menu go to Start, All Programs, Microsoft Visual Studio 2012, Visual Studio Tools, and right click on the “Developer Command Prompt for VS2012” and pick “Run as administrator”. If you are in Windows 8 or Server 2012 or later, probably easiest to just do a search for “Developer Command Prompt for VS2012”.

3. Navigate to Visual Studio’s Private Assemblies folder by entering “CD C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies”  (If you have installed VS to another drive or folder, adjust the path accordingly).

4. Enter the command “gacutil /if Microsoft.SqlServer.Dts.Design.dll” into the command prompt.




Now you should be able to open your SSIS projects.

The dll is the main set of libraries for SQL Server Integration Services. For some reason, after certain Windows updates, this becomes deregistered and you have to manually add it back. Not a huge deal, but annoying if it happens often enough. As I just had it happen today after a Windows update to the Hyper-V VM I use for some of my SQL Server development, I wanted to post this as a reminder on how to correctly fix the issue.

Get Your “Gangsta Geek” On, and Help A Kid!

GangstaGeek At the 2014 PASS Summit, Pragmatic Works Gangsta Geek shirts were incredibly popular. Looking at the number of boxes we brought, I would have thought there was enough for the entire week. Yet, by the end of day 1 they were gone.

If you missed out at the summit, or just think it’s a really cool shirt (because it is!) then now is your chance to get one and help a kid in the process.

Until December 5th, 2014 Pragmatic Works is donating 100% of the proceeds from sales of the shirts (minus shipping) to Seamark Ranch, an organization which provides secure homes from children that come families in crisis. You can go here to find out more and order your shirt! Don’t wait to get your Gangsta on, you only have a few days left!

Goodbye Pragmatic Works. Hello Pragmatic Works!

I wanted to share a new phase in my life. After a little over three and a half awesome years, October 10th will be my last day as a consult with Pragmatic Works. Beginning October 13th I will be going to work for… Pragmatic Works! (Confused? Hey you should be me.)

Most people don’t realize this, but Pragmatic Works is technically two companies in one. We have a consulting division, where I have worked for the last three and a half wonderful years. Beginning October 13th I will transition to working for the software division in the role of Product Evangelist.

In this role I will be spreading the word about our tools, as well as supplying additional training on our entire suite. You can begin to expect more posts from me that focus on our various tools, which include BIxPress, Task Factory, DOCxPress, and DBAxPress. I’ll still be in the community, perhaps even more so, giving people the opportunity to learn more about SQL Server in general, our tools and services in particular.

If you are going to the PASS Summit, be sure to look me up. I’m copresenting a precon titled Zero to Hero with PowerShell and SQL Server, as well as doing a regular session Make SQL Server Pop with PowerShell. I’ll also be spending a lot of time at the Pragmatic Works booth doing demos and the like. Would love to meet you, talk about your challenges around BI development, and how we could work to get many of them resolved.

So, Goodbye Pragmatic Works. Hello Pragmatic Works!