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.

Exporting Data from SQL Server to CSV Files for Import to MongoDB Using PowerShell

I’ve been exploring other database systems, in order to determine how to import data from them using SQL Server Integration Services (SSIS). My first step though was to create some test data. I wanted something familiar, so I decided to export the Adventure Works Data Warehouse sample database and import into MongoDB. While I had many options I decided the simplest way was to first export the data to CSV files, then use the MongoDB utility mongimport. Naturally I turned to PowerShell to create an automated, reusable process.

First, if you need the Adventure Works DW database, you’ll find it at http://msftdbprodsamples.codeplex.com/. Second, I did my export from a special version of Adventure Works DW I created called AdventureWorksDW2014. This is optional, but if you want to have a version of Adventure Works DW updated with current dates, see my post at http://arcanecode.com/2013/12/08/updating-adventureworksdw2012-for-2014/. Third, I assume you are familiar with MongoDB, but if you want to learn more go to http://www.mongodb.org/.

Below is the PowerShell 3 script I created. The script is broken into four regions. The first, User Settings, contains the variables that you the user might need to change to get the script to run. It has things like the name of the SQL Server database, the path to MongoDB, etc.

The second region, Common, establishes variables that are used by the remaining two regions. You shouldn’t need to change or alter these. The third region accesses SQL Server and exports each table in the selected database to a CSV format file.

The final region, “Generate MongoDB import commands”, creates a batch (.BAT) file which has all the commands needed to run mongoimport for each CSV file. I decided not to have the PowerShell script execute the .BAT file so it could be reviewed before it is run. There might be some tables you don’t want to import, etc.

It is also quite easy to adapt this script to just create CSV files from SQL Server without using the MongoDB piece. Simply remove the fourth and final region, then in the Common and User Settings regions remove any variables what begin with the prefix “mongo”.

As the comments do a good job of explaining what happens I’ll let you review the included documentation for step by step instructions.

#==================================================================================================
# SQLtoCSVtoMongoDb.ps1
# Robert C. Cain | @ArcaneCode |
http://arcanecode.com
#
# If you need a simple way to export data from SQL Server to MongoDb, here is one way to do it.
# The script starts by setting up some variables to the server environment (see the User Settings
# region)
#
# Next, it exports data from each table in the selected database to individual CSV files.
# Finally, it generates a batch file which executes mongoimport for each csv file to import
# into MongoDb.
#
# I broke this into four regions so if all that is desired is a simple export of data to CSVs,
# you can simply omit the final region along with any variables that begin with "mongo".
#
# While I could have gone ahead and run the batch file at the end, I chose not to in order to
# give you time to review the output prior to running the batch file.
#==================================================================================================

Clear-Host

#region User Settings

  # In this section, set the variables so they are appropriate for your project / environment
 
  # This is the spot where you want to store the generated CSVs.
  # Make sure it does NOT end in a \
  $csvPath = "C:\mongodb"

  # If you are running this on a computer other than the server, set the name of the server below
  $sqlServer = $env:COMPUTERNAME

  # If you have a named instance be sure replace "default" with the name of the instance
  $sqlInstance = "\default"

  # Enter the name of the database to export below
  $sqlDatabaseName = "AdventureWorksDW2014"

  # The settings below only apply to the MongoDB code generation
  # Assemble path to mongodb. This assumes utlities are stored in the default bin folder
  $mongoPath = "C:\mongodb"
  $mongoImport = "$mongoPath\bin\mongoimport"

  # Set the server name and port
  $mongoHost = "localhost"   # Leave blank to default to localhost
  $mongoPort = ""            # Leave blank to default to 27107
 
  # Set the user name and password, leave blank if it isn’t needed
  $mongoUser = ""
  $mongoPW = ""

  # Enter the name of the database to import to.
  $mongoDatabaseName = "AdventureWorksDW2014"

  # Upserts are REALLY slow, especially on large datasets. Setting this to $true will turn off
  # the upsert option. If set to true, you are responsible for either deleting all documents
  # in the collection before hand, or allowing the risk of duplicates.
  #
  # Setting to false will enable the upsert option for mongoimport, and attempt to determine the
  # keys and (if found) add them to the final mongoimport command.
  $mongoNoUpsert = $true

#endregion

#region Common ————————————————————————————
 
  # This section sets variables used by both regions below. There is no need to alter anything
  # in this region.

  # Import the SQLPS provider (if it’s not already loaded)
  if (-not (Get-PSProvider SqlServer))
    { Import-Module SQLPS -DisableNameChecking }

  # Assemble the full servername \ instance
  $sqlServerInstance = "$sqlServer\$sqlInstance"

  # Assemble the full path for the SQL Provider to get to the database
  $sqlDatabaseLocation = "SQLSERVER:\sql\$sqlServerInstance\databases\$sqlDatabaseName"

  # Now tack on the Tables ‘folder’ to the SQL Provider path, the move there
  $sqlTablesLocation = $sqlDatabaseLocation + "\Tables"
  Set-Location $sqlTablesLocation

  # Get a list of tables in this database
  $sqlTables = Get-ChildItem

#endregion

#region Export SQL Data —————————————————————————
  # In this section we will export data from each table in the database to a CSV file.
  # WARNING: If the CSV file exists, it will be overwritten.

  # These are just used to display informational messages during processing
  $sqlTableIterator = 0
  $sqlTableCount = $sqlTables.Count

  # Iterate over each table in the database
  foreach($sqlTable in $sqlTables)
  {
    $sqlTableName = $sqlTable.Schema + "." + $sqlTable.Name   

    # I’ll grant you the next little bit of formatting for the progress messages is a bit
    # OCD on my part, but I like my output formatted and easy to read.
    $sqlTableIterator++
    $padCount = " " * (1 + $sqlTableCount.ToString().Length – $sqlTableIterator.ToString().Length)
    $sqlTableIteratorFormatted = $padCount + $sqlTableIterator

    if( $sqlTableName.Length -gt 50 )
      { $padTable = " " }
    else
      { $padTable = " " * (50 – $sqlTableName.Length) }

    Write-Host -ForegroundColor White -NoNewline "Processing Table $sqlTableIteratorFormatted of $sqlTableCount : $sqlTableName $padTable"
   
    # If the instance is "default", we have to exclude it when we use Invoke-SqlCmd
    if($sqlInstance.ToLower() -eq "\default")
      { $sqlSI = $sqlServer }
    else
      { $sqlSI = $sqlServerInstance }

    # Load an object with all the data in the table
    # Note if you have especially large tables you may need to modify this
    # section to break things into smaller chunks.
    $sqlCmd = "SELECT * FROM " + $sqlTableName
    $sqlData = Invoke-Sqlcmd -Query $sqlCmd `
                             -ServerInstance $sqlSI `
                             -SuppressProviderContextWarning `

    # Now write the data out.
    # Note utf8 encoding is important, as it is all mongoimport understands
    # Also need to omit the Type Info header PowerShell wants to write out
    Write-Host -ForegroundColor Yellow "    Writing to table $sqlTableName.csv"
    $sqlData | Export-Csv -NoTypeInformation -Encoding "utf8" -Path "$csvPath\$sqlTableName.csv"

  }

  # Just add a blank line after the processing ends
  Write-Host

#endregion

#region Generate MongoDB import commands ———————————————————-

  # In this region we will generage the commands to import our newly exported data
  # into an existing database in MongoDB. This is an example of our desired output (wrapped
  # onto multiple lines for readability, in the output it will be a single line):

  #  C:\mongodb>bin\mongoimport –host localhost -port 27107
  #                             –db AdventureWorksDW2014 –collection DimSalesReason
  #                             –username Me –password mySuperSecureP@ssW0rd!
  #                             –type csv –headerline –file DimSalesReason.csv
  #                             –upsert –upsertFields SalesReasonKey

  # Note several of these parameters are optional, and could use defaults, or be potentially
  # omitted from the final output, based on the choices at the very beginning of this script

  # Feel free to alter the $mongoCommand as needed for other circumstances

  # Final warning, the database must already exist in MongoDb in order to import the data. This
  # script will not generate the database for you.

  # Create the name for the batch file we will generate
  $mongoBat = $csvPath + "\Import_SQL_" + $sqlDatabaseName + "_to_MongoDb_" + $mongoDatabaseName + ".bat"

  # See if file exists, if so delete it
  if (Test-Path $mongoBat)
    { Remove-Item $mongoBat }

  # These are just used to display informational messages during processing
  $sqlTableIterator = 0
  $sqlTableCount = $sqlTables.Count

  # mongoimport allows us to do upserts, helping to eliminate duplicate rows on import.
  #
  # To make an upsert work there has to be a key column to match up on. Fortunately,
  # most tables in the SQL Server world have Primary Keys, so we can find out what
  # columns those are and add it to the command. Note if there is no PK in SQL Server,
  # no upsert will be attempted.
  #
  # Note though that upserts are REALLY slow, so the option to skip them is
  # built into the script and set at the top (mongoNoUpsert). The generated batch file
  # assumes that either a) you have deleted all data from the collection ahead of time,
  # or b) you are OK with the risk of duplicate data.

  # Iterate over each table in the database to build the mongoimport command
  foreach($sqlTable in $sqlTables)
  {
    $sqlTableName = $sqlTable.Schema + "." + $sqlTable.Name

    # A bit more OCD progress messages
    $sqlTableIterator++
    $padCount = " " * (1 + $sqlTableCount.ToString().Length – $sqlTableIterator.ToString().Length)
    $sqlTableIteratorFormatted = $padCount + $sqlTableIterator
    Write-Host -ForegroundColor Green "Building mongoimport command for table $sqlTableIteratorFormatted of $sqlTableCount : $sqlTableName"

    # Begin building the command
    $mongoCommand = "$mongoImport "
   
    if ($mongoHost.Length -ne 0)
      { $mongoCommand += "–host $mongoHost " }

    if ($mongoPort.Length -ne 0)
      { $mongoCommand += "–port $mongoPort " }

    $mongoCommand += "–db $mongoDatabaseName –collection $sqlTableName "

    if ($mongoUser.Length -ne 0)
      { $mongoCommand += " –username $mongoUser –password $mongoPW " }

    $mongoCommand += " –type csv –headerline –file $csvPath\$sqlTableName.csv "
       
    # Build the upsert clause, if the user has elected to use it.
    if ($mongoNoUpsert -eq $false)
    {
      $mongoPKs = ""
      foreach($sqlIndex in $sqlTable.Indexes)
      {
        if($sqlIndex.IndexKeyType -eq ‘DriPrimaryKey’)
        {
          foreach($sqlCol in $sqlIndex.IndexedColumns) #$sqlPKColumns)
          {
            if ($mongoPKs.Length -ne 0)
              { $mongoPKs += "," }
            # Note column names are returned with [ ] around them, and must be removed
            # Have to use -replace instead of .Replace() because $sqlCol is an column not a string
            $mongoPKs += ($sqlCol -replace "\[", "") -replace "\]", ""
          }
               
          $mongoCommand += " –upsert –upsertFields $mongoPKs"
        }           
      }
    }

    # Append the command to the batch file
    $mongoCommand | Out-File -FilePath $mongoBat -Encoding utf8 -Append

  }

  # Just add a blank line after the processing ends
  Write-Host

#endregion

 

Updating AdventureWorksDW2012 for 2014

A while back I did a post that contained a script to update the AdventureWorksDW2012 database to have dates for the 2013 time period. This will allow folks to demo date related queries and be able to simply use things like GETDATE or NOW without having to do funky math tricks to take into account the pitifully out of date offering.

I’ve now updated the script for 2014, thought I’d pass along the updated version. Note some browsers don’t seem to render the script using the mono-spaced font I intend, but just ignore. Copy and paste into SQL Server Management Studio and it should work fine.

/*-----------------------------------------------------------------------------------------------*/
/* Updating AdventureWorks2012 for Today                                                         */
/*                                                                                               */
/* Robert C. Cain, http://arcanecode.com @ArcaneCode                                             */
/*                                                                                               */
/* Script Copyright (c) 2013 by Robert C. Cain                                                   */
/* AdventureWorks database Copyright (c) Microsoft.                                              */
/*                                                                                               */
/* This script will make a backup of the AdventureWorks2012DW database, then copy and restore it */
/* as AdventureWorksDW2014. It will then update it for current dates. 2008 now becomes 2014,     */
/* 2007 is now 2012, and so forth. This script is dependent on the AdventureWorks2012DW sample   */
/* database already being installed. It won't change AdventureWorksDW2012 in anyway.             */
/*                                                                                               */
/* Be warned, if AdventureWorksDW2014 exists, it will be deleted as part of this process.        */
/*                                                                                               */
/*-----------------------------------------------------------------------------------------------*/

PRINT 'Updating AdventureWorks2012 for Today - Starting'
GO

/*-----------------------------------------------------------------------------------------------*/
/* Step 1 - Make a copy of AdventureWorksDW2012 and restore as AdventureWorksDW2014              */
/*-----------------------------------------------------------------------------------------------*/
SET NOCOUNT ON

USE [master]

-- Step 1.1. Make a backup of AdventureWorksDW2012 ----------------------------------------------
PRINT 'Backing up AdventureWorksDW2012'
GO

BACKUP DATABASE [AdventureWorksDW2012] 
    TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012.bak' 
  WITH NOFORMAT, 
       INIT,  
       NAME = N'AdventureWorksDW2012-Full Database Backup', 
       SKIP, 
       NOREWIND, 
       NOUNLOAD,  
       STATS = 10
GO


-- Step 1.2. Delete the database AdventureWorksDW2014 if it exists ------------------------------
PRINT 'Deleting AdventureWorksDW2014, if it exists'
GO

IF (EXISTS (SELECT 1 
              FROM master.dbo.sysdatabases 
             WHERE name = 'AdventureWorksDW2014' )
   )
   EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AdventureWorksDW2014'
GO

IF (EXISTS (SELECT 1 
              FROM master.dbo.sysdatabases 
             WHERE name = 'AdventureWorksDW2014' )
   )
   DROP DATABASE [AdventureWorksDW2014]
GO

-- Step 1.3. Restore the database to a new copy -------------------------------------------------
PRINT 'Restoring AdventureWorksDW2012 to AdventureWorksDW2014'
GO

RESTORE DATABASE [AdventureWorksDW2014] 
   FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012.bak' 
   WITH  FILE = 1,  
   MOVE N'AdventureWorksDW2012_Data' 
     TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2014_Data.mdf',  
   MOVE N'AdventureWorksDW2012_Log' 
     TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2014_log.ldf',  
        NOUNLOAD,  STATS = 5

GO

PRINT 'Done Creating AdventureWorksDW2014'
GO



/*-----------------------------------------------------------------------------------------------*/
/* Step 2. Create a helper function to convert dates to a YYYYMMDD format Date Id.               */
/*-----------------------------------------------------------------------------------------------*/

USE [AdventureWorksDW2014]
GO

IF EXISTS (SELECT [name] FROM [sys].[all_objects] WHERE [name] = 'DateToDateId')
  DROP FUNCTION [dbo].[DateToDateId];
GO

CREATE FUNCTION [dbo].[DateToDateId]
(
  @Date DATETIME
)
RETURNS INT
AS
BEGIN

  DECLARE @DateId  AS INT
  DECLARE @TodayId AS INT

  SET @TodayId = YEAR(GETDATE()) * 10000
               + MONTH(GETDATE()) * 100
               + DAY(GETDATE())         

  -- If the date is missing, or a placeholder for a missing date, set to the Id for missing dates
  -- Else convert the date to an integer
  IF @Date IS NULL OR @Date = '1900-01-01' OR @Date = -1
    SET @DateId = -1  
  ELSE
    BEGIN
      SET @DateId = YEAR(@Date) * 10000
                  + MONTH(@Date) * 100
                  + DAY(@Date)         
    END  
  
  -- If there's any data prior to 2000 it was incorrectly entered, mark it as missing
  IF @DateId BETWEEN 0 AND 19991231 
    SET @DateId = -1

  -- Commented out for this project as future dates are OK
  -- If the date is in the future, don't allow it, change to missing
  -- IF @DateId > @TodayId 
  --   SET @DateId = -1

  RETURN @DateId

END

GO




/*-----------------------------------------------------------------------------------------------*/
/* Step 3. Add new dates to the dbo.DimDate table.                                               */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Adding new dates to dbo.DimDate'
GO

SET NOCOUNT ON

-- Later we will be writing an INSERT INTO... SELECT FROM to insert the new record. I want to 
-- join the day and month name memory variable tables, but need to have something to join to. 
-- Since everything is calculated, we'll just create this little bogus table to have something
-- to select from.
DECLARE @BogusTable TABLE
  ( PK TINYINT)

INSERT INTO @BogusTable SELECT 1;


-- Create a table variable to hold the days of the week with their various language versions
DECLARE @DayNameTable TABLE
  ( [DayNumberOFWeek]      TINYINT
  , [EnglishDayNameOfWeek] NVARCHAR(10)
  , [SpanishDayNameOfWeek] NVARCHAR(10)
  , [FrenchDayNameOfWeek]  NVARCHAR(10)
  )

INSERT INTO @DayNameTable
SELECT DISTINCT 
       [DayNumberOFWeek]      
         , [EnglishDayNameOfWeek] 
         , [SpanishDayNameOfWeek] 
         , [FrenchDayNameOfWeek]  
  FROM dbo.DimDate

-- Create a month table to hold the months and their language versions.
DECLARE @MonthNameTable TABLE
  ( [MonthNumberOfYear] TINYINT
  , [EnglishMonthName]  NVARCHAR(10)
  , [SpanishMonthName]  NVARCHAR(10)
  , [FrenchMonthName]   NVARCHAR(10)
  )

INSERT INTO @MonthNameTable
SELECT DISTINCT
       [MonthNumberOfYear] 
     , [EnglishMonthName]  
     , [SpanishMonthName]  
     , [FrenchMonthName]   
  FROM dbo.DimDate

-- This is the start and end date ranges to use to populate the 
-- dbo.DimDate dimension. Change if it's 2014 and you run across this script.
DECLARE @FromDate AS DATE = '2011-01-01'
DECLARE @ThruDate AS DATE = '2015-12-31'

-- CurrentDate will be incremented each time through the loop below.
DECLARE @CurrentDate AS DATE
SET @CurrentDate = @FromDate

-- FiscalDate will be set six months into the future from the CurrentDate
DECLARE @FiscalDate  AS DATE

-- Now we simply loop over every date between the From and Thru, inserting the
-- calculated values into DimDate.
WHILE @CurrentDate <= @ThruDate
BEGIN

  SET @FiscalDate = DATEADD(m, 6, @CurrentDate)

  INSERT INTO dbo.DimDate
  SELECT [dbo].[DateToDateId](@CurrentDate)
       , @CurrentDate
       , DATEPART(dw, @CurrentDate) AS DayNumberOFWeek
       , d.EnglishDayNameOfWeek
       , d.SpanishDayNameOfWeek
       , d.FrenchDayNameOfWeek
       , DAY(@CurrentDate) AS DayNumberOfMonth
       , DATEPART(dy, @CurrentDate) AS DayNumberOfYear
       , DATEPART(wk, @CurrentDate) AS WeekNumberOfYear
       , m.EnglishMonthName
       , m.SpanishMonthName
       , m.FrenchMonthName
       , MONTH(@CurrentDate) AS MonthNumberOfYear
       , DATEPART(q, @CurrentDate) AS CalendarQuarter
       , YEAR(@CurrentDate) AS CalendarYear
       , IIF(MONTH(@CurrentDate) < 7, 1, 2) AS CalendarSemester
       , DATEPART(q, @FiscalDate) AS FiscalQuarter
       , YEAR(@FiscalDate) AS FiscalYear
       , IIF(MONTH(@FiscalDate) < 7, 1, 2) AS FiscalSemester
    FROM @BogusTable
    JOIN @DayNameTable d
      ON DATEPART(dw, @CurrentDate) = d.[DayNumberOFWeek]
    JOIN @MonthNameTable m
      ON MONTH(@CurrentDate) = m.MonthNumberOfYear

  SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
END
GO

-- If you want to verify you can uncomment this line.
-- SELECT * FROM dbo.DimDate WHERE DateKey > 20110000

PRINT 'Done adding new dates to dbo.DimDate'
GO





/*-----------------------------------------------------------------------------------------------*/
/* Step 4. Update the Fact Tables with the new dates.                                            */
/*-----------------------------------------------------------------------------------------------*/


PRINT 'Update Fact Tables'
GO

SET NOCOUNT ON

-- To move forward five years, we simply add 50,000 to the date key

-- 4.1 FactFinance ------------------------------------------------------------------------------
PRINT '  FactFinance'
GO

UPDATE [dbo].[FactFinance]
   SET [DateKey] = [DateKey] + 60000;


-- 4.2 FactInternetSales ------------------------------------------------------------------------
PRINT '  FactInternetSales'
GO

-- There are a few rows where the due date is on leap year. Update these to back off a day 
-- so the date add works OK
UPDATE [dbo].[FactInternetSales]
   SET [OrderDateKey] = 20080228
     , [OrderDate] = '2008-02-28'
 WHERE [OrderDateKey] = 20080229

UPDATE [dbo].[FactInternetSales]
   SET [DueDateKey] = 20080228
     , [DueDate] = '2008-02-28'
 WHERE [DueDateKey] = 20080229

UPDATE [dbo].[FactInternetSales]
   SET [ShipDateKey] = 20080228
     , [ShipDate] = '2008-02-28'
 WHERE [ShipDateKey] = 20080229

-- Now update the rest of the days. 
UPDATE [dbo].[FactInternetSales]
   SET [OrderDateKey] = [OrderDateKey] + 60000
     , [DueDateKey] = [DueDateKey] + 60000
     , [ShipDateKey] = [ShipDateKey] + 60000
     , [OrderDate] = DATEADD(yy, 6, [OrderDate])
     , [DueDate] = DATEADD(yy, 6, [DueDate])
     , [ShipDate] = DATEADD(yy, 6, [ShipDate])


-- 4.3 FactResellerSales ------------------------------------------------------------------------
PRINT '  FactResellerSales'
GO

-- As with Internet Sales, there are rows where the due date is on leap year. 
-- Update these to back off a day so the date add works OK
UPDATE [dbo].[FactResellerSales]
   SET [OrderDateKey] = 20080228
     , [OrderDate] = '2008-02-28'
 WHERE [OrderDateKey] = 20080229

UPDATE [dbo].[FactResellerSales]
   SET [DueDateKey] = 20080228
     , [DueDate] = '2008-02-28'
 WHERE [DueDateKey] = 20080229

UPDATE [dbo].[FactResellerSales]
   SET [ShipDateKey] = 20080228
     , [ShipDate] = '2008-02-28'
 WHERE [ShipDateKey] = 20080229

-- Now update the table
UPDATE [dbo].[FactResellerSales]
   SET [OrderDateKey] = [OrderDateKey] + 60000
     , [DueDateKey] = [DueDateKey] + 60000
     , [ShipDateKey] = [ShipDateKey] + 60000
     , [OrderDate] = DATEADD(yy, 6, [OrderDate])
     , [DueDate] = DATEADD(yy, 6, [DueDate])
     , [ShipDate] = DATEADD(yy, 6, [ShipDate])

-- 4.4 FactSalesQuota ---------------------------------------------------------------------------
PRINT '  FactSalesQuota'
GO

UPDATE [dbo].[FactSalesQuota] 
   SET [DateKey] = [DateKey] + 60000

-- 4.5 FactSurveyResponse -----------------------------------------------------------------------
PRINT '  FactSurveyResponse'
GO

UPDATE [dbo].[FactSurveyResponse]
   SET [DateKey] = [DateKey] + 60000

-- 4.6 FactCallCenter ---------------------------------------------------------------------------
PRINT '  FactCallCenter'
GO

-- All the rows in call center have a 2010 date, just add 3 years to make these 2014
UPDATE [dbo].[FactCallCenter]
   SET [DateKey] = [DateKey] + 40000


-- 4.7 FactCurrencyRate -------------------------------------------------------------------------
PRINT '  FactCurrencyRate'
GO

-- Because the DateKey is part of the PK, we have to drop the key before we can update it
ALTER TABLE [dbo].[FactCurrencyRate] DROP CONSTRAINT [PK_FactCurrencyRate_CurrencyKey_DateKey]
GO

-- Shift the 2008 Leap Year days to 2012 Leap Year
UPDATE [dbo].[FactCurrencyRate]
   SET [DateKey] = 20120229
 WHERE [DateKey] = 20080229

-- Update everything except the leap year we fixed already
UPDATE [dbo].[FactCurrencyRate]
   SET [DateKey] = [DateKey] + 60000
 WHERE [DateKey] <> 20120229

-- Add the PK back
ALTER TABLE [dbo].[FactCurrencyRate] 
  ADD CONSTRAINT [PK_FactCurrencyRate_CurrencyKey_DateKey] PRIMARY KEY CLUSTERED 
      ( [CurrencyKey] ASC,
          [DateKey] ASC
      )
 WITH ( PAD_INDEX = OFF
      , STATISTICS_NORECOMPUTE = OFF
      , SORT_IN_TEMPDB = OFF
      , IGNORE_DUP_KEY = OFF
      , ONLINE = OFF
      , ALLOW_ROW_LOCKS = ON
      , ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]
GO


-- 4.8 FactProductInventory ---------------------------------------------------------------------
PRINT '  FactProductInventory'
GO

-- As with the previous step, the date is part of the primary key, so we need to drop it first.
ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [PK_FactProductInventory]
GO

-- Shift the 2008 Leap Year days to 2012 Leap Year
UPDATE [dbo].[FactProductInventory]
   SET [DateKey] = 20120229
 WHERE [DateKey] = 20080229

-- Update everything except the leap year we fixed already
UPDATE [dbo].[FactProductInventory]
   SET [DateKey] = [DateKey] + 60000
 WHERE [DateKey] <> 20120229
 
-- Add the PK back
ALTER TABLE [dbo].[FactProductInventory] 
  ADD CONSTRAINT [PK_FactProductInventory] PRIMARY KEY CLUSTERED 
      (    [ProductKey] ASC
      , [DateKey] ASC
      )
 WITH ( PAD_INDEX = OFF
      , STATISTICS_NORECOMPUTE = OFF
      , SORT_IN_TEMPDB = OFF
      , IGNORE_DUP_KEY = OFF
      , ONLINE = OFF
      , ALLOW_ROW_LOCKS = ON
      , ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]
GO

PRINT 'Done updating the Fact tables'
GO



/*-----------------------------------------------------------------------------------------------*/
/* Step 5. Cleanup, remove the helper function we added earlier.                                 */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Removing Helper Function'
GO

IF EXISTS (SELECT 1 FROM [sys].[all_objects] WHERE [name] = 'DateToDateId')
  DROP FUNCTION [dbo].[DateToDateId];
GO

/*-----------------------------------------------------------------------------------------------*/
/* All done!                                                                                     */
/*-----------------------------------------------------------------------------------------------*/
PRINT 'Updating AdventureWorks2012 for Today - Completed'
GO

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

CISCO VPN Error 442 and Windows 8.1

After upgrading to Windows 8.1 I had issues running Cisco VPN software. When attempting to run I got an error 442. (Note this applies to Cisco VPN, not Cisco AnyConnect.) As a first step in troubleshooting I ensured that I was on the latest version, 5.0.07.0440.

As I was already on the latest version, I began to do some web searching. Likely you, as I did, found many blog posts referring to a fix for the registry. In case you haven’t seen it, the basic instructions are:

1. Open RegEdit.

2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CVirtA

3. If the DisplayName does not already read:

Cisco Systems VPN Adapter for 64 bit Windows

Change it so matches what you see above. Some installs have some “gibberish” on the front, this should be removed. In my case it was already set to what you see above, so it was on to the next step. After some more searching and experimentation, I finally came upon a solution that worked for me.

Open Windows Explorer, and navigate to C:\Program Files (x86)\Cisco Systems\VPN Client. Right click on the first .exe you find, in my case cisco_cert_mgr.exe and pick Properties from the menu. Switch to the Compatibility page.

Check on “Run this program in compatibility mode for:” and pick Windows 7. Then at the bottom, check on “Run this program as an administrator. Then click on OK.

image

 

Repeat this for every exe in the folder.

image

Unfortunately you can’t apply these in mass, you have to do these one at a time. There’s only a handful though so it shouldn’t take long.

After you are done, reboot. Before you run the VPN software, verify the Cisco network connector is not active by going to Control Panel, Network and Internet, Network Connections and validate the Cisco Systems VPN Adapter for 64 bit Windows is Disabled.

Note after you connect this will become Enabled, you just want to be sure it is disabled before you connect. If you see multiple connections for the Cisco adapter, you will need to delete the excess ones, I’ve also seen suggestions to uninstall the Cisco VPN, make sure all the Cisco connections are deleted, then reinstall. I didn’t have to go through this though, so your mileage may vary.

Also note that after you reboot, you may see a prompt asking if you want to run the Cisco service as Administrator, naturally you’ll say Yes. Then go run the Cisco VPN client again, confirming you want to run as an Admin, and you should be good to go!

Follow

Get every new post delivered to your Inbox.

Join 100 other followers