How to debug PowerShell scripts in SSIS

I will skip the basics as this Microsoft blog did a good job explaining how to Run PowerShell scripts in SSIS .

Ok, now you have a powershell script running inside SSIS and soon enough you will need to deal with errors. By default you don’t get a lot from SSIS as the process popup window specified by WindowStyle normally gives you a glimpse of the error messages milliseconds before it terminates.

The first reaction I usually see people do is trying to make the popup window to stay/wait/hold, but instead of going down the fruitless path, here is what you can do:

1. Setup variables for StandardOutputVariable and StandardErrorVariable

2. Set a breakpoint on the condition Break when the container receives the OnPostExecute event 

3. Start Debugging (Note: Don’t right-click and Execute Task) and wait for the breakpoint to be hit.

4 .Inspect the variables in the Locals window

Powershell’s Write-Host sometimes is better than Write-Output

Despite what’s being said about the danger of Write-Host, I have found a scenario where it actually is the desired cmdlet to use over Write-Output.

Consider the code snippet below:

function Get-Array
{
    "I am not supposed to be in the array."

    $a = @("I am the only item in the array.")
    return $a
}

$array = Get-Array

cls

"`$array.count: [$($array.count)]"
$array

When I first encountered this piece of code (the simplified version shown above), I was quite frustrated with the output:

$array.count: [2]
I am not supposed to be in the array.
I am the only item in the array.

Why the hell was my print statement showing up in the array? Well, after hours of debugging and googling now I know it has everything to do with the behaviour of Write-Output:

The Write-Output cmdlet sends the specified object down the pipeline to the next command. If the command is the last command in the pipeline, the object is displayed in the console.
Write-Output sends objects down the primary pipeline, also known as the “output stream” or the “success pipeline.” To send error objects down the error pipeline, use Write-Error.
This cmdlet is typically used in scripts to display strings and other objects on the console. However, because the default behavior is to display the objects at the end of a pipeline, it is generally not necessary to use the cmdlet. For instance, Get-Process | Write-Output is equivalent to Get-Process.

The Get-Array function was explicitly returning an array object, but the assignment later on was also passing on whatever was in the primary pipeline. Gotcha!

A simple solution to this gotcha is by replacing the implicit Write-Output to Write-Host:

function Get-Array
{
    Write-Host "I am not supposed to be in the array."

    $a = @("I am the only item in the array.")
    return $a
}

$array = Get-Array

cls

"`$array.count: [$($array.count)]"
$array

As expected, you’d get the output below:

$array.count: [1]
I am the only item in the array.

This works because Write-Host behaves quite differently to Write-Output:

Write-Host sends the objects to the host. It does not return any objects. However, the host might display the objects that Write-Host sends to it.

Understanding the differences between the seemingly simple commands can save loads of wasted time in debugging, especially in a scripting world.

Watch out for square brackets when using Select-String to search for SQL objects

Powershell’s Select-String cmdlet is powerful, well, sometimes too powershell you’d want to dumb it down a notch. That’s what happened to me today.

Here is the scenario. I have the name of a column in the form of [schema_name].[table_name].[column_name] and I want to see if [schema_name].[table_name] part of the name is found amongst a list of tables. I have set up two test scenarios below for demonstration purpose:

Table List
[sys].[objects]
[sys].[columns]
[sys].[types]

Column
1. For [sys].[objects].[type_desc], Select-String should return something as the table name [sys].[objects] forms part of the column name.
2. For [sys].[tables].[type_desc], Select-String should return nothing as no tables from the list matches [sys].[tables].

Expressed in powershell:

$table_array = "[sys].[columns]", "[sys].[types]", "[sys].[objects]"
$sys_objects_name = "[sys].[objects].[type_desc]"
$sys_tables_name = "[sys].[tables].[type_desc]"

"`nLooking for a table that is part of $sys_objects_name..."
Select-String -InputObject $sys_objects_name -Pattern $table_array

"`nLooking for a table that is part of $sys_tables_name..."
Select-String -InputObject $sys_tables_name -Pattern $table_array

In this instance, -Pattern takes an array and for each one of the array items it is matched against -InputObject. In other words, we are trying to find a -Pattern that is a subset of -InputObject.

Running the above script yields the output below:

Looking for a table that is part of [sys].[objects].[type_desc]...
[sys].[objects].[type_desc]

Looking for a table that is part of [sys].[tables].[type_desc]...
[sys].[tables].[type_desc]

Notice how both Select-String returned something? That’s wrong as we don’t expect the second Select-String to return anything. After trying to debug with a few different scenarios, it dawned on me that Select-String uses regular expression matching by default. It was one of those “OF COURSE!” moments in hindsight. Most of the time it is ok if you are just trying to do some simple string matching but when working with SQL (square bracket) quoted names, it can wreak havoc.

The solution is to turn regular expression matching off and to do that you just need to specify the flag -SimpleMatch:

$table_array = "[sys].[columns]", "[sys].[types]", "[sys].[objects]"
$sys_objects_name = "[sys].[objects].[type_desc]"
$sys_tables_name = "[sys].[tables].[type_desc]"

"`nLooking for a table that is part of $sys_objects_name..."
Select-String -InputObject $sys_objects_name -Pattern $table_array -SimpleMatch

"`nLooking for a table that is part of $sys_tables_name..."
Select-String -InputObject $sys_tables_name -Pattern $table_array -SimpleMatch

Running the script again now yields the correct output:

Looking for a table that is part of [sys].[objects].[type_desc]...
[sys].[objects].[type_desc]

Looking for a table that is part of [sys].[tables].[type_desc]...

–oo00oo–
For more info, please refer to the links below:
Select-String

 

Use Powershell’s Select-String to find SQL update statements

I had a SQL script and I wanted to check if there are any update statements in the file. It was a seemingly easy task but after messing around with Powershell’s Select-String, I learnt a few gotchas that are worthy of a write-up.

Let’s start with something simple:

SQL:

update dbo.table set column = 1

Powershell:

Get-ChildItem script.sql | Select-String -Pattern "update"

Output:

script.sql:1:update dbo.table set column = 1

Now, let’s say I want to exclude print statements and comments that have a random “update” in them.

SQL:

-- start to update dbo.table...
update dbo.table set column = 1
print 'update complete.'

Without changing the powershell, I will get all three lines returned. It is reasonable to assume that a legit update statement would always come with a set clause, so maybe I can include that in the search pattern.

Powershell:

Get-ChildItem script.sql | Select-String -Pattern "update.*set.*="

Output:

script.sql:2:update dbo.table set column = 1

This looks good so far. However, it is also reasonable to assume that most update statements would have the set clause on the next line, let’s see how that’s handled.

SQL:

-- start to update dbo.table...
update dbo.table 
set column = 1
print 'update complete.'

Oops. Nothing returned this time. I learnt this in hindsight that Get-ChildItem is feeding into Select-String one line at a time so it will never find the update.*set pattern. In order to get around this problem, I just need to feed the entire script into Select-String as a string.

Powershell:

[string](Get-Content script.sql) | Select-String -Pattern "update.*set.*="

Output:

-- start to update dbo.table... update dbo.table  set column = 1 print 'update complete.'

It worked a treat!

–oo00oo–
For more info, please refer to the links below:
Select-String

How to setup GIT for Windows using Command Prompt and Posh-Git

On my local environment, I am using:

  • Git for Windows
  • Command Prompt (comes with Windows so no additional install required)
  • Posh-Git (for nice visual prompt)

I have had the above setup for quite some time now. After helping out a colleague with the setup today I have decided to put the instructions in a post so I can quickly reference it later.

Install Git

Everything starts from here. You can download Git for Windows from here if you haven’t already got it installed. Make sure you ticked “Use Git from the Windows Command Prompt”.

git_setup

Setup Command Prompt shortcut

The steps below will provide you with a MUCH more friendly command prompt.

1. Pin Command Prompt to the taskbar.

2. Right click on Command Prompt (the one in the taskbar) and click on Properties. Set the properties to:

Target: %windir%\system32\cmd.exe /K “cd C:\Dev & powershell”
/K = Carries out the command specified by stirng but remains
& = Used to separate multiple commands

This command will 1) Switch into C:\Dev (or any other working folders) and 2) Run powershell.

3. Click on the Options tab. Tick QuikEdit mode in Options. This makes copy and paste operations a lot easier.

Bonus Tip: In order to start cmd.exe in admin mode, you can add the following registry key:

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers]
"C:\\Windows\\System32\\cmd.exe"="~ RUNASADMIN"

Install PowerGUI (optional)

You can choose any IDE to work with powershell scripts but this is my personal preference. PowerGUI is a free IDE for powershell scripts that can be downloaded from here.

Install Posh-Git

1. Download the Posh-Git zip file from here. You can install Posh-Git using other methods like git clone or PsGet but my experience says manipulation of a zip file is the easiest way for beginners to understand and follow.

2. Unzip (using 7-Zip) to C:\Dev\posh-git-master. If you don’t have 7-Zip and relied on the Windows “Extract All…”, you might encounter issues with powershell’s execution policy. Instead of messing around with the execution policy, just go download and install 7-Zip from here. It will save you lots of headaches.

It doesn’t matter where you extract the files, I leave it in C:\Dev because that’s where I keep things.

3. In a command prompt, go into the posh-git-master folder and run .\install.ps1. You may or may not need to set the powershell’s execution policy but RemoteSigned at the LocalMachine level should be sufficient.

4. Inspect your newly created profile. If you don’t know where your profile is located. Just run $profile in the command prompt. You profile should contain two lines that read:

# Load posh-git example profile
. 'C:\Dev\posh-git-master\profile.example.ps1'

5. By now you should get the nice prompt that’s integrated with git. command_prompt_posh-git

6. Bonus step. I have talked about including the current date time in the command prompt in a previous post. After installing posh-git, the handy timestamp in the comand prompt would be lost. In order to get that back, I had to edit the file C:\Dev\posh-git-master\profile.example.ps1 by adding Write-Host($(Get-Date)) above the default Write-Host line:

...
Write-Host("")
Write-Host($(Get-Date))
Write-Host($pwd.ProviderPath) -nonewline
...

Setup SSH Key

1. Generate a new SSH key. Again, seemingly simple step but expect unpleasant surprises if your environment is not configured properly. Make sure:

  • C:\Program Files (x86)\Git\bin is in your PATH.
  • HOME = C:\Users\<user name> is added to the system environment variables.
  • C:\Users\<user name>\.ssh folder is manually created if it doesn’t exist already.

Once all the above checks passed, run the command below in the command prompt:

C:\Users\<user name>\.ssh> ssh-keygen -t rsa -C "<email address>"

Tip: If ssh-keygen.exe is not in the path already, you’ll need to look for it in one of the bin sub folders under the git folder.

2. Press <Enter> to accept the default location and file name.

3. Press <Enter> and <Enter> again when prompted to enter a passphrase.

4. Open the public key file (C:\Users\<user name>\.ssh\id_rsa.pub) and copy the content.

5. Add the public key to GitHub / BitBucket.

–oo00oo–
For more info, please refer to the links below:
Better Git with PowerShell

Powershell file is not digitally signed

A powershell profile which I have happily used for over a year suddenly turned on me today. Normally when I open up the command prompt, it would kick off powershell and which would then load posh-git using a powershell profile but instead of the super nice git prompt, I received the following error:

File cannot be loaded. The file \\somenetworkserver\myhomefolder\WindowsPowerShell\Microsoft.PowerShell_profile.ps1 is not digitally signed.

The file was created by me initially so digital signature has no relevance here. I tried to resolve this issue by tinkering with Set-ExecutionPolicy Unrestricted and Set-ExecutionPolicy ByPass, needless to say, they didn’t work. If it was that simple, I wouldn’t be writing this post.

Luckily I found this blog article which pointed me in the right direction: http://blogs.technet.com/b/heyscriptingguy/archive/2012/10/29/how-to-run-powershell-scripts-from-a-shared-directory.aspx

By default, computers running Windows Server include UNC paths in the Internet security zone. Windows PowerShell is actually responding to the security zone when it throws the error for the UNC paths.

Alright, we are getting somewhere, since the powershell profile sits in a network share, this must be it.

Fix Attempt No. 1
OK, let’s see if I can add the UNC to the intranet zone. Bad news! The setting was locked down by Group Policy. Basically any fixes that need to manipulate group policy is out of question.

 internet_options

OK, don’t panic, think. So I cannot place the profile file on the network share, what if I move it to a local drive? Bingo.

Fix Attempt No. 2
After wasting a good 10 minutes on Stack Overflow I conceded that it is not possible to move the profile files as they can only be created in their default locations. Wait a minute, they? files? How many profile files are there? I then fired up get-help about_profiles and like a compass it led me to the solution.

Fix Attempt No. 3
Apparently $profile.AllUsersAllHosts is located in $PsHome and thank god it is on the local drive: $PsHome = C:\Windows\System32\WindowsPowerShell\v1.0

At this point, I had reached enlightenment. I joyfully carried out the following steps and my profile was once again happily loading posh-git on start up:

  1. Create a new profile
    1.  new-item –type file –path $profile.AllUsersAllHosts –force
      1. Small hiccup here. I was looking for Microsoft.PowerShell_profile.ps1 after running the command but the file was actually named profile.ps1
  2. Move content from Microsoft.PowerShell_profile.ps1 to profile.ps1
  3. Remove Microsoft.PowerShell_profile.ps1 from the network share.