Arcane Fun Fridays–Going Retro with PowerShell

In terms of the tech world I’m an old geezer. I actually remember the days of monochrome screens, both the amber and green kind. I even have my old Commodore 64 with its light blue font on dark blue background. So I was feeling a bit nostalgic, and decided to create some shortcuts to PowerShell windows with a retro look.

For pre Windows 8, in the start menu navigate to Start then go to your Accessories and/or Administrative tools (varies with which version of Windows you have). Find the icon for Windows PowerShell (not the ISE, just the regular window). (I figure if you are using PowerShell, you probably know where it is without me having to tell you.)

Right click on it, pick “Send To”, then “Desktop (create shortcut)”.

If you are on Windows 8, the simplest thing is to go to your start menu and locate Windows PowerShell. Pin it to your task bar. Now, desktop taskbar shift + right click (the shift is very important when you right click) and do the same Send To / Desktop I mention above.

OK, you now have a short cut, let’s start our retroization. First, give it a decent name. For my example I’ll pick “PS Amber”. Next, right click on it and pick Properties. In the dialog click on the Colors tab. Click on Screen Text, and set the Red / Green / Blue to 255, 185, 0 respectively.

image

Now, this is the part that will bring back the cool “retro” effect. If you are an old geezer like me, you’ll recall the amber monitors also had a big of an orangey background, they weren’t entirely black. Go to the Screen Background, and set its Red value to 32, then use 0 for Green and Blue. That will give it just the hint of orangeness to make it authentic. Of course based on your monitor calibration you may need to tweak it up or down just a tad, adjust until you are happy.

For the Pop-up text, I used the same settings as the Screen Background, and likewise for the Pop-up Background I used the same colors as the Screen Text. And here’s what I wound up with (click on it for a bigger image):

image

Following the same steps above, I created a second short cut called PS Green. I set its Screen Text to an RGB of 0, 255, 0, and the Screen Background to 0, 32, 0. Like with the previous section, I used the reverse colors for the pop ups, 0,255,0 for the background and 0, 32, 0 for the popup text.

image

And finally, the setting that made me wax eloquently over my beloved old Commodore 64:

Screen Text and Popup Background: 44 / 136 / 255

Screen Background and Popup Text: 0 / 0 / 34

image

(If you are too young to remember the Commodore 64, you can read more about it on Wikipedia http://en.wikipedia.org/wiki/Commodore_64 or see it in action, there’s a slew of videos about it on youtube http://www.youtube.com/results?search_query=commodore%2064&sm=3)

Setting up different shortcuts to different color themes can not only make you feel nostalgic, but it can also be quite beneficial when you have multiple PowerShell windows open at the same time. The drastically different colors can make it easy to know which window you have doing what task.

Have fun with it, adjust the colors until they look just right on your own monitor, then create your own crazy color combos. If the are really good post them in the comments area. If I get enough I’ll create a follow up post, giving appropriate credit for each submission.

SAPIEN PowerShell Studio 2014–Customization Redux

Earlier in the week (post) I started this series on SAPIEN PowerShell Studio 2014 discussing customization. How appropriate to wrap up returning to that same topic.

The original version I downloaded was 4.1.47. Late last night I found out a new version has already been released, 4.1.49. I downloaded it and to my pleasure found two more options for color themes, Visual Studio 2013 Dark and Visual Studio 2013 Light.

image

If you read my earlier post you’ll already know what I picked, the new VS dark theme is much darker and richer than the Office 2013 black theme. Take a look:

image

Note that the colors of the editor are not set with this selection, only the backgrounds for things like the menus, title bar, borders, etc. If you want the nifty dark settings in the editor you’ll still need to set them yourself, or use the XML data I listed at the bottom of the already mentioned first post in the series.

You may be wondering how I found out about the update with these new features. Well, SAPIEN has a particularly nice set of forums, located at http://sapien.com/forums/. As you might expect there are forums for all of their products, but you will also find forums for the various scripting languages. In here you can ask generic questions not directly related to any of their products.

After my initial download I had placed a post asking some questions, specifically this post: http://sapien.com/forums/viewtopic.php?f=12&t=7435

Within an hour a support person named David had answered my questions, one of which as you’ll see was on the color themes.

Now, what particularly impressed me was four days later David remembered my forum post, and placed a follow up post letting me know about the update with the new dark color theme. Now that is some outstanding customer service, big kudos to David (whoever you are) for the follow up.

SAPIEN PowerShell Studio 2014–Tools

As I’ve been working with the SAPIEN PowerShell Studio (website), there are a few things in the Tools menu I have found useful. I wanted to call these out, as some may overlook what could be a set of very beneficial items.

If you navigate to the tools menu, over to the left you’ll see these tools:

image

Find in Files is pretty obvious, but don’t over look it. How many times have you been working on something and thought “OK, I wrote something like this once, but just which file was it in?” This will allow you to search for a string you enter. You can specify the folder, and can add a list of file types to look through. By default it will limit the search to PowerShell oriented files, but you could change this easily if, for example, you wanted to search through a CSV file. Especially nice is the ability to use regular expressions or wild cards.

image

Compare Files does just what it says. You select two files, and it will compare them and produce a report of the differences.

Custom Tool is interesting, essentially it becomes a menu you can customize and use to launch executables, parameterized as you want it. Rather than repeating what is already published, I’ll point you to a blog post on the SAPIEN website which explains how to customize this for your needs.

http://www.sapien.com/blog/2013/01/08/powershell-studio-2012-with-git-subversion-and-mercurial-oh-my/

Check Syntax is great, it will quickly look through the current script and identify any syntax errors. I’ve gotten to the point where I always use this after making major edits to a script. Much nicer to find out this way rather than after you start running the script.

Verify Script is similar, but instead of syntax it checks to see if all the required “pieces parts” are present. For example, it looks at any Import-Module statements and validates that those modules are indeed available, and that the functions you reference are there.

Sign Script I haven’t needed to use yet, but if you are in an environment where this is required than this will be a great little shortcut for you.

Restore Points are the last item on the list. When you create a restore point, you can then go make changes to your code, then if you don’t like them you can Rewind to the previous restore point, or use the Restore button to revert back to the point at which you created the original restore point. Once you are happy with your code you can then Delete the restore point.

This is ideal for those situations where you think “hmm, I wonder if this would work…” and want to try out something. But, if it doesn’t you want to be able to roll back to the point prior to your editing. Previously you would have needed to make a copy of the PS1 (or whatever you are editing) file, make changes, then either copy the file back or copy parts of it back into your code. Yuck. Restore Points make this much easier. Even better, the persist between sessions. You can close the entire PowerShell Studio, and when you return and reopen the file those restore points are still active.

I am amazed how little many developers know about the tools at their disposal. Often people look no further than the main tab and never explore the rest of their environment.

Even though individually these tools may seem like small things, together they provide quite a toolbox to solve a lot of common, everyday issues developers face. I hope it encourages you to more fully explore the SAPIEN PowerShell Studio 2014.

SAPIEN PowerShell Studio 2014–Navigation

Continuing my series on SAPIEN PowerShell Studio (website), the next feature I want to focus on is Navigation, both of your files and inside the code.

From a file perspective, PowerShell Studio allows you to organize into projects. Right now I am working on two modules plus one set of scripts which accomplish a common task. Each module I have organized into a project. Here is the project window for one of the modules I’m developing:

image

This allows me to quickly navigate between the various files I have in the project. I can easily open and close these files as I work on them without having to go through the laborious “File, Open…” mechanism in the PowerShell ISE. My other project is the collection of files which consume these modules. Each file performs a specific task, but ultimately get us to a completed solution. Using the Project feature in PowerShell Studio allows me to organize these into one solution, quickly jumping back and forth between them.

Even better, I can have these three items open in separate PowerShell Studio windows at the same time. Note that I did need to go into the Options window, and check on “Allow multiple instances” option.

image

This is a far, far easier way to work than inside the PowerShell IDE included with the native Microsoft PowerShell installation. Speaking of easy, let’s look at code navigation.

One of the first features is the functions window. This simply lists all the functions in the currently open code editor window. Simply double click on one to jump to that function.

image

This is awesome in an large script with many functions.

Next is the ability to easily expand / contract code.

image

You can quickly expand and collapse sections of code to make scrolling through it easier, allowing you to look at only the sections you need to see. I have one script of nearly 1,500 lines, and have placed regions throughout. This feature allows me to focus only on the code I want, and neatly hide what I am not currently working on.

The next thing I’d like to show is the split code window. This allows me to take a single code window, and look at two different parts at the same time. While many editors do have this feature, it is especially great for PowerShell.

image

The final feature I want to mention is Bookmarks. We’ve all had big scripts where we are working on a section of code, need to go look at something elsewhere in the code, then need to jump back to the previous line we were working on. It is useful, then, to drop a bookmark, scroll down to the code you want to look at, then quickly jump back.

image

Using the Bookmark menu you can place a marker in your code. Over to the left of the editor you’ll see the marker.

image

Now you can scroll down and find the other piece of code you want to look at. If it is something you’ll want to come back to continually, you can place a second bookmark. Then using the Next/Previous bookmark buttons you can easily jump back and forth between the two locations in your code.

image

While these are features that come with some editors, such as Visual Studio, they are certainly lacking in the PowerShell ISE. These are basic but critical features that make you much more productive when developing your own PowerShell scripts.

SAPIEN PowerShell Studio 2014–Customization

SAPIEN Technologies (website) released their PowerShell tool, PowerShell Studio 2014 this month. They give a 45 day trial, so I’ve downloaded it and am truly impressed. In the first day it already helped my productivity. I thought I’d spend a few blog posts looking at some of the features.

The focus for this first post is customization. Out of the box, here is the look and feel (click on image for bigger view):

image

And here is what my environment looks like:

image

Yes, I am one of those oddballs who likes dark color themes. And it was pretty easy to set this up. First, in the upper right there’s a drop down. From it you can pick from one of the standard “Office” themes.

image

As you can see, I selected Office 2012 Black. This gives the darker colors that surround the environment. Next to tackle the colors within the editor. In the Home toolbar there is an Options button…

image

which brings up an Options window.

image

Through the Font Style button you can set the values for each

image

Once you have everything set the way you wish, you can save your settings and move them from machine to machine. Just go back to the General tab.

image

Through it you can Save All Settings, which saves everything, from the colors to the layout of all the panels on the screen. Alternately, you can save just the editor settings by clicking the Save Editor Settings. To make it easy should you like this theme I’ve pasted the XML for the dark editor at the end of this blog. All you’ll have to do is copy it to notepad, save it as an XML file, then use the “Load Settings” feature to load it.

PowerShell Studio also makes it easy to alter the layout of the various panels that surround the editor. At the bottom left is a “Layouts” button. Clicking it shows the layouts optimized for the task you are doing.

image

Once you get a layout you like, you can save your custom layout for later. You’ll note my customized layout that I previously saved has been added to the list (ArcaneCode Layout). Now you can quickly jump back and forth between layouts to work on a specific task.

I love the fact that PowerShell Studio 2014 allows me to customize the environment to work the way I want to. You too can customize to your favorite settings.

Finally, as promised, here is the XML for my editor settings.

 

<registry name="SOFTWARE\SAPIEN Technologies, Inc.\PowerShell Studio 2014">
  <k name="Editor">
    <v name="ShowLineNumbers" value="1" kind="DWord" />
    <v name="EnableOutlining" value="1" kind="DWord" />
    <v name="EnableCurrentLineHighlighting" value="0" kind="DWord" />
    <v name="EnableAutoComplete" value="1" kind="DWord" />
    <v name="EnableObjectDescriptions" value="1" kind="DWord" />
    <v name="ShowColumnGuide" value="0" kind="DWord" />
    <v name="ColumnGuide" value="80" kind="DWord" />
    <v name="EnableTrackChanges" value="1" kind="DWord" />
    <v name="EnableAutomaticSyntaxCheck" value="1" kind="DWord" />
    <v name="EnableAliasTabExpansion" value="1" kind="DWord" />
    <v name="EnableCmdletAutoSelect" value="1" kind="DWord" />
    <v name="ConvertTabsIntoSpaces" value="1" kind="DWord" />
    <v name="TabSize" value="2" kind="DWord" />
    <v name="ShowModuleCmdlets" value="1" kind="DWord" />
    <v name="AutoCompleteRequiresExactMatch" value="0" kind="DWord" />
    <v name="AutoInsertModules" value="1" kind="DWord" />
    <v name="ShowExternalTools" value="1" kind="DWord" />
    <v name="EnableDotSourcePrimalSense" value="1" kind="DWord" />
    <k name="Code Formatting">
      <v name="EnableSmartIndent" value="1" kind="DWord" />
      <v name="AutomaticallyFormatOnNewLine" value="1" kind="DWord" />
      <v name="AutomaticallyFormatOnOpenBraces" value="1" kind="DWord" />
      <v name="AutomaticallyFormatOnSemicolon" value="1" kind="DWord" />
      <v name="CurlyBracketsNewLine" value="1" kind="DWord" />
      <v name="IndentParamBlock" value="1" kind="DWord" />
      <v name="IndentAttributeParameters" value="2" kind="DWord" />
      <v name="AlignParameters" value="1" kind="DWord" />
      <v name="AlignAttributeParameters" value="1" kind="DWord" />
    </k>
    <k name="Default Assemblies">
      <v name="mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" value="" kind="String" />
      <v name="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" value="" kind="String" />
      <v name="System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" value="" kind="String" />
      <v name="System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" value="" kind="String" />
      <v name="System.Drawing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" value="" kind="String" />
      <v name="System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" value="" kind="String" />
      <v name="System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" value="" kind="String" />
      <v name="System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" value="" kind="String" />
      <v name="System.ServiceProcess, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" value="" kind="String" />
    </k>
    <k name="Style">
      <v name="FontName" value="Consolas" kind="String" />
      <v name="FontSize" value="11" kind="String" />
      <v name="BackColor" value="-16777216" kind="DWord" />
      <k name="Alias">
        <v name="Bold" value="True" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-1" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Cmdlet">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-160" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Code Snippet Field">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-16711681" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Command As Parameter">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="True" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-256" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Comment">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-5329234" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="External Tool">
        <v name="Bold" value="True" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-65536" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Function">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-256" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Highlighted Reference">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-65281" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Number">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-6750690" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Operator">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-1" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Parameter">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-256" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Parameter Attribute">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-16722899" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Reserved Word">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-256" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="String">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-16722899" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Text">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-1" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Type">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-9144343" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Unknown Command">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-1" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Variable">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-16722899" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
    </k>
  </k>
</registry>

Installing Windows PowerShell Modules on Multiple User’s Computers–Updated

A few years ago (2010, wow 4 years now) Ed Wilson, aka the Scripting Guy, wrote a blog post on how to copy PowerShell modules onto multiple user’s computers. You’ll find the original version of his script at:

http://blogs.technet.com/b/heyscriptingguy/archive/2010/01/19/hey-scripting-guy-january-19-2010.aspx

Although I got my copy from the new version of his excellent book, Windows PowerShell 4.0 Best Practices.

I have used this for a while, but it turned out there was a drawback. Let me explain.

I’m currently developing what is becoming a rather large module. For management, I wanted to break it down into smaller pieces. The solution is to put logical groupings of functions into individual PS1 files, then dot source those from the module.

. $PSScriptRoot\MyPiecesPartsScript.ps1

Then I could break it up as much as I wanted. The problem with the original script was it only copied PSM1 and PSD1 files. In his script he has a Get-ChildItem which feeds his Copy-Module function. When I added *.PS1 to the list of things to include, the script created a folder for each script. Not what I wanted.

The solution for me was to write another function which would get all the folders in my source and copy the PS1s to the appropriate module folder. I had one other criteria though. In each of my module folders I have a test script where I can test out my module. I name these with the same name as the module but with a –Test on the end. Naturally I don’t want to copy these to the users module folder.

Here then is the function I created. You could copy and paste this right below the functions in Ed’s script:

function Copy-SupportingScripts ()
{
  [CmdletBinding()]
  param ([Parameter( Mandatory = $true,
                     ValueFromPipeline = $true,
                     ValueFromPipelineByPropertyName = $true,
                     HelpMessage = ‘Please pass a Directoy object.’
                     )]
         [System.IO.DirectoryInfo] $Folder,
         [Parameter( Mandatory = $false,
                     ValueFromPipeline = $true,
                     ValueFromPipelineByPropertyName = $true,
                     HelpMessage = ‘Enter files to exclude.’
                     )]
         [string] $Exclude
        )

  foreach($dir in $Folder)
  {
    $UserPath = $env:PSModulePath.split(";")[0]
    $targetPath = "$UserPath\$($dir.Name)"
    $sourcePath = "$($dir.FullName)\*.ps1"
    Write-Verbose "Copy $sourcePath to $targetPath"
    if ($Exclude.Length -gt 0)
    {
      Write-Verbose "    Excluding $Exclude in the copy."
      Copy-Item -Path $sourcePath `
                -Destination $targetPath `
                -Exclude *-Test.ps1 `
                -Force | Out-Null
    }
    else
    {
      Copy-Item -Path $sourcePath `
                -Destination $targetPath `
                -Force | Out-Null
    }
  }
}

To call it, at the bottom of Ed’s original script you can use:

Get-ChildItem -Path C:\PS\Arcane-Modules -Directory |
  ForEach-Object { Copy-SupportingScripts -Folder $_ -Exclude *-Test.ps1 -Verbose }

Here is the final result of my script merged with Ed’s. Make sure to give him plenty of kudo’s for the original.

# —————————————————————————–
# Script: Copy-Modules.ps1
# Author: ed wilson, msft
# Date: 09/07/2013 17:33:15
# Updated by: Robert C. Cain, @ArcaneCode, Pragmatic Works
# Updated Date: 02/20/2014
# Keywords: modules
# comments: installing
# Windows PowerShell 4.0 Best Practices, Microsoft Press, 2013
# Chapter 10
# —————————————————————————–
Function Get-OperatingSystemVersion
{
(Get-WmiObject -Class Win32_OperatingSystem).Version
} #end Get-OperatingSystemVersion

Function Test-ModulePath
{
$VistaPath = "$env:userProfile\documents\WindowsPowerShell\Modules"
$XPPath =  "$env:Userprofile\my documents\WindowsPowerShell\Modules"
if ([int](Get-OperatingSystemVersion).substring(0,1) -ge 6)
   {
     if(-not(Test-Path -path $VistaPath))
       {
         New-Item -Path $VistaPath -itemtype directory | Out-Null
       } #end if
   } #end if
Else
   { 
     if(-not(Test-Path -path $XPPath))
       {
         New-Item -path $XPPath -itemtype directory | Out-Null
       } #end if
   } #end else
} #end Test-ModulePath

Function Copy-Module([string]$name)
{
$UserPath = $env:PSModulePath.split(";")[0]
$ModulePath = Join-Path -path $userPath `
               -childpath (Get-Item -path $name).basename
if ( (Test-Path $modulePath) -eq $false)
   { New-Item -path $modulePath -itemtype directory | Out-Null }
Copy-Item -path $name -destination $ModulePath -force | Out-Null

}

function Copy-SupportingScripts ()
{
  [CmdletBinding()]
  param ([Parameter( Mandatory = $true,
                     ValueFromPipeline = $true,
                     ValueFromPipelineByPropertyName = $true,
                     HelpMessage = ‘Please pass a Directoy object.’
                     )]
         [System.IO.DirectoryInfo] $Folder,
         [Parameter( Mandatory = $false,
                     ValueFromPipeline = $true,
                     ValueFromPipelineByPropertyName = $true,
                     HelpMessage = ‘Enter files to exclude.’
                     )]
         [string] $Exclude
        )

  foreach($dir in $Folder)
  {
    $UserPath = $env:PSModulePath.split(";")[0]
    $targetPath = "$UserPath\$($dir.Name)"
    $sourcePath = "$($dir.FullName)\*.ps1"
    Write-Verbose "Copy $sourcePath to $targetPath"
    if ($Exclude.Length -gt 0)
    {
      Write-Verbose "    Excluding $Exclude in the copy."
      Copy-Item -Path $sourcePath `
                -Destination $targetPath `
                -Exclude *-Test.ps1 `
                -Force | Out-Null
    }
    else
    {
      Copy-Item -Path $sourcePath `
                -Destination $targetPath `
                -Force | Out-Null
    }
  }
}

# *** Entry Point to Script ***
$sourceFolder = "C:\PS\Arcane-Modules"

# Ensure the PowerShell folder exists in the users Documents folder
Test-ModulePath

# Copy the modules (psd1 and psm1) files
Get-ChildItem -Path $sourceFolder -Include *.psm1,*.psd1 -Recurse |
  ForEach-Object { Copy-Module -name $_.fullName }

# Copy any supporting ps1 files.
# Remove the -Exclude directive if you don’t want to exclude anything.
Get-ChildItem -Path $sourceFolder -Directory |
  ForEach-Object { Copy-SupportingScripts -Folder $_ `
                                          -Exclude *-Test.ps1 `
                                          -Verbose
                 }

One final and very important note. Ed’s original script was written in the PowerShell v2 days. My function uses the new –Directory switch introduced in PowerShell v3, so you will need at least v3 to make this work.

Importing MongoDB Data Using SSIS 2012

I have embarked on a little quest to learn other database platforms (especially NoSQL) as more and more of our clients at Pragmatic Works have them in their enterprise, and want to be able to import data from them into their SQL Server data warehouses using SQL Server Integration Services (SSIS). While I found several articles that showed how to do so, these were outdated due to changes in the MongDB C# driver. After quite a bit of effort figuring out how to get this working, I thought I’d pass along my hard fought knowledge.

First, I assume you are familiar with MongoDB (http://www.mongodb.org/) and SQL Server (https://www.microsoft.com/en-us/sqlserver/default.aspx). In my examples I am using SSIS 2012 and MongoDB 2.4.8, along with the C# driver version 1.7 for MongoDB available at http://docs.mongodb.org/ecosystem/drivers/csharp/ .

First, download and install the C# driver. This next step is important, as there was a change that occurred with version 1.5 of the driver: the DLLs are no longer installed in the GAC (Global Assembly Cache) automatically. They must be there, however, for SSIS to be able to use them.

By default, my drivers were installed to C:\Program Files (x86)\MongoDB\CSharpDriver 1.7. You’ll want to open a CMD window in Administrator mode, and navigate to this folder. Next you’ll need GACUTIL, on my computer I found the most recent version at:

C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\x64\

A simple trick to find yours: Since you are already in the CMD window, just move to the C:\Program Files (x86) folder, and do a “dir /s gacutil.exe”. It will list all occurrences of the program, just use the one with the most recent date. Register the dlls by entering these commands:

“C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\x64\gacutil” /i MongDB.Bson.dll

“C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\x64\gacutil” /i MongDB.Driver.dll

Note the “ quote marks around the path are important for the CMD window to correctly separate the gacutil program from the parameters.

Once that is done, create a new SQL Server Integration Services project in SQL Server Data Tools (SSDT), what used to be called BIDS in SQL Server 2008R2 (and previous). Put a Data Flow Task on the Control Flow design surface. Then open the Data Flow Task for editing.

Next, drag and drop a Script Component transformation onto the Data Flow design surface. When prompted, change the component type to Source.

image

Now edit the script transform by double clicking on it. Move to the Inputs and Outputs page. For my test, I am using the dbo.DimCurrency collection I created using the technique I documented in the previous post, Exporting Data from SQL Server to CSV Files for Import to MongoDB Using PowerShell ( http://arcanecode.com/2014/01/13/exporting-data-from-sql-server-to-csv-files-for-import-to-mongodb-using-powershell/ )

I renamed the output from “output” to “MongoDB_DimCurrency”. I then added four columns, CurrencyName, CurrencyAlternateKey, CurrencyKey, and ID.

image

Make sure to set CurrencyName, CurrencyAlternateKey, and ID to “Unicode string [DT_WSTR]” Data Type. Then change CurrencyKey to “four byte signed integer [DT_I4]”.

Now return to the Script page and click Edit Script. In the Solution Explorer pane, expand References, right click and pick Add Reference. Go to Browse, and navigate to the folder where the MongoDB C# drivers are installed. On my system it was in C:\Program Files (x86)\MongoDB\CSharpDriver 1.7\. Add both MongoDB.Driver.dll and MongoDB.Bson.dll.

image

Click OK when done, your Solution Explorer should now look something like:

image

Now in the script, expand the Namespaces region and add these lines:

using MongoDB.Bson;
using MongoDB.Driver;
using MongoDB.Bson.Serialization;

Now scroll down to the CreateNewOutputRows() procedure. Here is a sample of the code I used:

public override void CreateNewOutputRows()
{
  string connectionString = "mongodb://localhost";
  string databaseName = "AdventureWorksDW2014";

  var client = new MongoClient(connectionString);
  var server = client.GetServer();
  var database = server.GetDatabase(databaseName);
  string CurrencyKey = "";

  foreach (BsonDocument document in database.GetCollection<BsonDocument>("dbo.DimCurrency").FindAll())
  {
    MongoDBDimCurrencyBuffer.AddRow();
    MongoDBDimCurrencyBuffer.CurrencyName = document["CurrencyName"] == null ? "" : document["CurrencyName"].ToString();
    MongoDBDimCurrencyBuffer.CurrencyAlternateKey = document["CurrencyAlternateKey"] == null ? "" : document["CurrencyAlternateKey"].ToString();
    CurrencyKey = document["CurrencyKey"] == null ? "" : document["CurrencyKey"].ToString();

    MongoDBDimCurrencyBuffer.CurrencyKey = Convert.ToInt32(CurrencyKey);
    MongoDBDimCurrencyBuffer.ID = document["_id"] == null ? "" : document["_id"].ToString();
  }

}

I start by defining a connection string to the MongoDB server, followed by the database name. I then create a MongoClient object. Note the MongoClient is the new way of connecting to the MongoDB server. In earlier versions of the C# driver, you used MongoServer objects.

I then cycle through each document in the collection “dbo.DimCurrency”, using the FindAll() method. For each item I use the AddRow() method to add a row to the buffer. In order to find the proper name for the buffer I went to the Solution Explorer and expanded the BufferWrapper.cs file. This is a class created by the script transform with the name of the output buffer.

image

For each column in my outputs, I map a column from the document. Note the use of the ternary operator ? : to strip out nulls and replace them with empty strings. String columns you can map directly from the document object to the output buffers columns.

The CurrencyKey column, being an integer, had to be converted from a string to an integer. To make it simple I created a string variable to hold the return value from the document, then used the Convert class to convert it to an INT 32.

Once you’ve done all the above, validate the code by building the code. If that all checks out save your work, close the code window, then close the Script Transformation Editor by clicking OK.

Now place a destination of some kind on the Data Flow. Since I have my company’s Task Factory tools I used a TF Terminator Destination, but you could also use a Row Count destination. On the precedence constraint between the two, right click and Enable Data Viewer. Execute the package, if all goes well you should see:

image

A few final notes. This test was done using a MongoDB document schema that was flat, i.e. it didn’t have any documents embedded in the documents I was testing with. (Hopefully I’ll be able to test that in the future, but it will be the subject of a future post.) Second, the key was the registering of the DLLs in the GAC. Until I did that, I couldn’t get the package to execute. Finally, by using the newer API for the MongoDB objects I’ve ensured compatibility for the future.

Follow

Get every new post delivered to your Inbox.

Join 106 other followers