PowerShell – Trap cmdlet errors.

Sometimes working with data can be challenging.  As a SQL Developer, creating ETL solutions, is our responsibility all this data makes it to our users.  Sometime data manipulation might be limited in SSIS, we are force to look for other alternatives.  In my case, I use PowerShell, and I need a way to trap some import processing errors during the execution of the “Invoke-SQLcmd” cmdlet.  Then, I will be able to investigate why some on my data wasn’t making it into my SQL tables.

I try to use PowerShell the “Try-Catch” to trap the error but it was wrong implementation to trap the error in the SQL Server PowerShell “Invoke-SQLcmd” command.  So, How can I trap the command errors?  Well, you can use “CommonParameters“, which are an additional set of parameters automatically provided by Windows PowerShell.  In these additional set of parameters you will find the “-ErrorVariable” parameter which allow you to stores the error information in a given variable.  The error variable you provide in any cmdlet will be overridden each time the cmdlet execute, unless you add an “+” in front of it so it can append to it and collect error information.

For more
Information about all “CommondParameters“, execute at the PS prompt:


Help About_CommonParameters -full

Now, my implementation was to use the “-ErrorVariable” parameter to trap and save the error message with the SQLquery script that failed. Also, I make sure to the variable to a null value after I saved the results to disk. In my scenario, the data imported could break my SQL query and I want to make sure I can identify these records.

So, I’m using the ErrorVariable parameter in my “Invoke-SQLcmd” command.  Here’s a code snippet sample of it:

$reccount = 1; $sqlerr = $null;

:

Foreach($item in $Data)
{
:

  $outfile = "C:\ImportError\TSQLQUERY_$($reccount).sql";
  $SqlQuery =  "Insert into $tablename ($tblfields) values($fldsvalue);";
  Invoke-Sqlcmd -Database $dbName -ServerInstance $ServerName `
    -Query $SqlQuery -ErrorVariable sqlerr;

  if($sqlerr -ne $null)
  {
     "/*Error Found - on record# [ $reccount ]:> `r`n"+$Error[0] + "*/ `r`n" `
       |  Out-File $outfile -Encoding ASCII;
     $SqlQuery | Out-File $outfile -Encoding ASCII -Append;
     "`r`n /* "+"".padleft(45,"=")+" */`r`n" `
       | Out-File $outfile -Encoding ASCII -Append;
     $sqlerr = $null;
  }
   :
  $reccount++;
}

Basically, I want to make sure my “$sqlerr” Error Variable is initialized as $null in both the beginning and after the error have been saved to the output file. Then, we can view later the Error message and the SQL script that error out to later view the output file displaying it in your SSMS or your favorite editor.

Keep in mind, this will not stop the errors from been displayed on screen.  But, you could use another Common Parameter: ” -ErrorAction SilentlyContinue” to prevent displaying the errors.

Sample output  file image:

I hope you find this code snippet useful!

PowerShell – Sum items in a CSV file.

The fact that PowerShell gives you the ability to a build tasks in many different ways, it’s sometimes intimidating.  But, you’ll find the way to improve your coding as time goes.  I try, the best I can, to keep things simple.

So, here’s another approach on How-To “Sum” items in a CSV file:

My Data

## sbData.csv:
# Date,Item,ItemType,Amount,Company
# 12/12/11,Ben Aipa 8'10,Longboard,1500,BenAipa
# 12/12/11,Walden 9'6",Longboard,1100,Walden
# 12/13/11,Channel Island 6',Shortboard,800,ChannelIsland
# 12/13/11,Mark Richards 6'8",Shortboard,750,Surftech
# 12/14/11,Walden 7'6",Funboard,800,Walden
# - End of CSV file -

The request is to sum the amount of the items group by Date and ItemType.  And here’s the solution:

## - Working process to Sum amount in a CSV file by grouping columns:
$gdata = Import-csv sbData.csv | Group-Object -Property Date,ItemType;

## - BUild your new object with the sum items:
[Array] $newsbData += foreach($gitem in $gdata)
{
$gitem.group | Select -Unique Date,ItemType, `
@{Name = 'SumAmount';Expression = {(($gitem.group) | measure -Property Amount -sum).sum}}
};

## - Build back the CSV file with the Sum items
$newsbData | Export-Csv NewsbData.csv -NoTypeInformation;
ii NewsbData.csv;

So, you can substitute the group items in ” -property ” parameter with the ones you want to be grouped on, then use  the “Select -unique …” to include the grouped items you want to list.  The magic happens when including the  “Measure” command against the already grouped items so you can sum the ‘Amount’ field.  Now, this code could become a template to sum up items when needed.

The Result:

# - My CSV new Output data:
# "Date","ItemType","SumAmount"
# "12/13/11","Longboard","2600"
# "12/13/11","Shortboard","1550"
# "12/14/11","Funboard","800"
# - end of file -

Happy PowerShelling!!

QuickBlog – Finding a value in a PowerShell Hash table – Simple!!

For this quick blog, let’s create a month hash object table containing the number and name of the month.  Then, we are going to query this Hash Table object for a specific month.

1. Creating our Month Hash Table:

$MonthHashTable = @{ `
[int] "1" = "January"; `
[int] "2" = "February"; `
[int] "3" = "March"; `
[int] "4" = "April"; `
[int] "5" = "May"; `
[int] "6" = "June"; `
[int] "7" = "July"; `
[int] "8" = "August"; `
[int] "9" = "September"; `
"10" = "October"; `
"11" = "November"; `
"12" = "December"; `
};

Notice in this example, we’ve assigned ‘Strong-typed’ values to most of the keys (month number) as Integer, and leaving the last three to default to a string data “data type“.

Keep in mind, in a Hash Table you have Keys, and Values.  So, we are going to search for a ‘Key’ in order to get the ‘Value’ so we can extract the Month name.

Here’s the Hash Table Syntax for finding the value using the key:
$yourHashTable.Item( [#key#] )

2. Lets go and Looking for the fourth and eleventh month name:

## ----------------------------------------
## - Getting "April":
## - Value stored as Integer for "April":
$mth = 4;

## - Bad, passing a string value when expecting an Integer
## -> no key found, nothing to return:
$MonthHashTable.Item("4");

## - Good, getting month name using Integer value
## -> keys found, a values are returned:
$MonthHashTable.Item(4);
April

$MonthHashTable.Item($mth);
April

## ----------------------------------------
## - Getting "November":
## - Value stored as Integer for "November":
$mth = 11;

## - Bad, passing an integer value when expecting a string
## -> no key found, nothing to return:
$MonthHashTable.Item(11);

## - Bad, passing an integer value when expecting a string
## -> no key found, nothing to return:
$MonthHashTable.Item($mth);

## - Value stored as String for "November":
$mth = "11";

## - Good, getting month name using String value:
## -> key found, a value is returned:
$MonthHashTable.Item($mth);
November

As the sample show, we need to make sure the variable value must have the proper “data type” so it can find the key.

Happy PowerShelling!!

SQLPASS PowerShell Virtual Chapter – “Extending T-SQL with PowerShell” Posted Slides & Scripts..

For all whom attended the SQLPASS PowerShell Virtual Chapter – “Extending Your T-SQL Scripting with PowerShell” session on Wednesday November 16th, I finally got it posted here.

My sincere Apologies for the long delay in posting my session slides and demo scripts.  I thought I lost the material after having a disk drive corruption but I was able to recorver it.

Please, click on the link :< download now>: from SkyDrive. (rename file *.zip.txt to *.zip before extracting)

Don’t hesitate to contact me if you have any issues downloading it.

YES!! Windows Management Framework (WMF/PowerShell) 3.0 CTP2 is here.

YES!!  It’s another great accomplishment from the Microsoft PowerShell Team.  Just released on December 2nd 2011 this CTP (Community Technology Preview) come with fixes and more enhancements.  Please CHECK out PowerShell ISE, is really showing some progress.

PowerShell ISE V3 CTP2 Customizable Themes

Get it herehttp://www.microsoft.com/download/en/details.aspx?id=27548

First thing!!!  READ:> IMPORTANT!!  YOU NEED TO UNINSTALL WMF 3.0 CTP1!  Or, suffer the consequences.

But, it you’re one of the few experiencing Windows 8 Preview and using Hyper-V 3.0 then you can create another VM for a clean CTP2 installation experience.  Or, just use the VM shapshot to to back and forth between version.  So, take a pick.

This version comes with some documentation, so make sure to ready it.  Also, don’t be afraid to submit bug reports at: https://connect.microsoft.com/PowerShell/. (you need to have a Live ID)

For example, I just put in a suggestion to include the ability to “Comment-In” and “Comment-Out” block of code: https://connect.microsoft.com/PowerShell/feedback/details/711231/ise-v3-need-to-be-able-to-comment-a-series-of-lines-in-a-block-of-code

*Hint*Hint*:  

  1. When running scripts, I notice there has been changes to how the “Set-ExecutionPolicy” behaves.  I’m used to set my execution policy to “RemoteSigned“, but for this version now I had to changed it to “Unrestricted”. (more test to be done)
  2. Also, don’t forget to execute the “Update-Help” as soon as you open PowerShell.  This is working for most part.

There will be more to come soon.