PowerShell V3.0 Object difference vs V2.0…?

I was unconsciously doing something while coding in PowerShell V3.0 Beta, and it caught me off guard.  As I’m getting more comfortable working with PowerShell Objects, I didn’t realized there is an enhancement in place.  There’s a quick way to access with your objects values. I’m trying to scripting for PowerShell V2.0 but I started see errors, then I realize I was accessing my items differently in PowerShell V3.0.

Let me show some PowerShell V3.0 code that access a SQL Server SMO collection of type “Microsoft.SqlServer.Management.Smo.Information”.  I want to access the values of the property “Properties” in the SQL Server Information SMO Object.

*hint*: Remember to use the command “Get-Member” to expose the content (method & properties) in your PowerShell variable.


## - Load Assembly and create your SQL object:
[System.Reflection.Assembly]::loadwithPartialName("Microsoft.SQLServer.SMO");
$MySQL = New-Object('Microsoft.SqlServer.Management.SMO.Server');

## - Getting to your Information Properties"
$MySQL.Information;
$MySQL.Information.Properties;

The last two lines will give you some results back. Now, try the next line:


## - Only valid in PowerShell V3.0:
$MySQL.Information.Properties.name;

This line will display all values in the property “Name”, and that’s great.

Go to another machine with PowerShell V2.0, and execute the lines:


## - Load Assembly and create your SQL object:
[System.Reflection.Assembly]::loadwithPartialName("Microsoft.SQLServer.SMO");
$MySQL = New-Object('Microsoft.SqlServer.Management.SMO.Server');

## - Only valid in PowerShell V3.0:
$MySQL.Information.Properties.name;

Notice you will not get any results back.

Now, change the line below and the execute:


## - Valid in both PowerShell V3.0 and V2.0:
$MySQL.Information.Properties | Select name;

And, it will display all values from this property.

As you notice, using PowerShell V3.0 can list items from the collection without forcing you to pipe “|” the value to another command (“.. | Select Name ..“). Just be careful and make sure the code stay compatible with PowerShell V2.0.

I like this enhancement!

PragmaticWorks Webinar: Basic Intro to PowerShell for the DBA – agenda

Yes! Today, Tuesday April 17th at 11:00am EST on PragmaticWorks Webinar, I will be speaking doing a session on “Basic Intro to PowerShell for the DBA“. This is an introduction to what you need in order to start using PowerShell in your SQL Server environment.

Here’s the agenda for this session:

Hopefully, this material will help you get you started to learn PowerShell all your automation needs.
And, YES!! Here’s some few scripts to get you started:

Getting BizTalk information with PowerShell

As I keep venturing into Biztalk, here’s how you can start using PowerShell to access your BizTalk objects with just a few lines of code and (for now) using one of the BizTalk .NET assemblies that’s loaded during the installation.

In the following example, I will create a PowerShell .NET object containing information about my BizTalk Application Artifacts. Please, pay attention to these sample code. There’s lots of information in it.

Open a PowerShell Console prompt, and type (or copy/paste) the following code:


## - Load the following Assembly:
[System.Reflection.Assembly]::loadwithPartialName("Microsoft.BizTalk.ExplorerOM");

## - Create a new empty variable to stored the .NET information:
$BTSexp = New-Object Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer;

## - Now, this line will connect to your Biztalk Local instance:
$BTSexp.ConnectionString = "Server=.;Initial Catalog=BizTalkMgmtDb;Integrated Security=SSPI;";

Now, keep in mind, you need to execute this block of code in your BizTalk server box, or use PowerShell Remoting feature. To look into the newly created object ‘$BTSexp’, use the “Get-Member” command to expose all the .NET object stored in it. You’ll notice both Properties, and Methods:


$BTSexp | Get-member;

As you can see, few line of code,  you got a lot of information about you BizTalk box. Try the following lines to list all our send box:


$BTSexp.Sendports

This line will display all properties and its content on the screen. Now, using the “Get-Member” (or “GM”) command, you can select from these list some of the properties you want to display on screen:

## - List all Biztalk .NET object properties and methods:
$BTSexp.SendPorts | gm;

## - Display some of the selected properties:
$BTSexp.SendPorts | Select Application, name, Status | format-table -auto;

## - The End

Oops!! Notice our result is not exactly correct. Our ‘Application’ values are showing with a long .NET Namespace, and our status column got truncated from the list. The ‘Application’ property is another .NET object that contains more information, and is only accessible if you use the ‘Foreach’ loop condition to get to it.

To fix this oneliner, we need to create a loop block so we can go deep into the .NET object of ‘Application’ and extract that information. The example below I added an “if” statement to search for all SendPorts containing the ‘TransportType’ for FTP’s:


## - Load the following Assembly:
[System.Reflection.Assembly]::loadwithPartialName("Microsoft.BizTalk.ExplorerOM");

## - Create a new empty variable to stored the .NET information:
$BTSexp = New-Object Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer;

## - Now, this line will connect to your Biztalk Local instance:
$BTSexp.ConnectionString = `
  "Server=.;Initial Catalog=BizTalkMgmtDb;Integrated Security=SSPI;";
## - Looping through each item in the SendPorts object and display
## - it in the PowerShell console:
$y = 0;
foreach($item in ($BTSexp.SendPorts))
{
    if($item.PrimaryTransport -ne $null)
    {
        if($item.PrimaryTransport.TransportType.Name -match "FTP|Nsoftware SFTP")
        {
            Write-host "[$($y.ToString("000"))]`t$($item.Application.name)`t$($item.PrimaryTransport.TransportType.Name)`t$($item.Name)";
            $y++;
        }
    }
};


Well, we are getting better now but we can make it look nicer. Adding a few more lines we change the code to store the values generated into a PowerShell PSObject. Finally, we can generate a better formatted result:


## - Load the following Assembly:
[System.Reflection.Assembly]::loadwithPartialName("Microsoft.BizTalk.ExplorerOM");

## - Create a new empty variable to stored the .NET information:
$BTSexp = New-Object Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer;

## - Now, this line will connect to your Biztalk Local instance:
$BTSexp.ConnectionString = `
  "Server=.;Initial Catalog=BizTalkMgmtDb;Integrated Security=SSPI;";

## - Initialize variables:
$y = 0; [Array] $MyNewObject = $null;

#Add each result into our New object:
$MyNewObject = foreach($item in ($BTSexp.SendPorts))
{
    if($item.PrimaryTransport -ne $null)
    {
        if($item.PrimaryTransport.TransportType.Name -match "FTP")
        {
           #Building your PowerShell PSObject item:
    $newPSitem = New-Object PSObject -Property @{
         seq = $y.ToString("000");
  Application = $item.Application.name;
                PortType = $item.PrimaryTransport.TransportType.Name;
                Port = $item.Name;
           };
           #To display PSObject item values while processing:
           $newPSitem;
           $y++;
        }
    }
};
$MyNewObject | Select seq,Application,PortType,Port | ft -auto;


Now, we’ve created a new PowerShell object with our selected properties, and with a new output that looks Great!. Just Try It in your box!!

The above script will list all FTP related ports. Here’s another the same code PowerShell script for listing all SendPorts on your BizTalk Server:


## - Load the following Assembly:
[System.Reflection.Assembly]::loadwithPartialName("Microsoft.BizTalk.ExplorerOM");

## - Create a new empty variable to stored the .NET information:
$BTSexp = New-Object Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer;

## - Now, this line will connect to your Biztalk Local instance:
$BTSexp.ConnectionString = `
  "Server=.;Initial Catalog=BizTalkMgmtDb;Integrated Security=SSPI;";

## - Initialize variables:
$y = 0; [Array] $MyNewObject = $null;

## - Initialize variables:
$y = 0; [Array] $MyNewObject = $null;

#Add each result into our New object:
$MyNewObject = foreach($item in ($BTSexp.SendPorts))
{
 #Building your PowerShell PSObject item:
    $newPSitem = New-Object PSObject -Property @{
  seq = $y.ToString("000");
  Application = $item.Application.name;
  PortType = $item.PrimaryTransport.TransportType.Name;
  Port = $item.Name;
 };
 #To display PSObject item values while processing:
 $newPSitem;
 $y++;
};
$MyNewObject | Select seq,Application,PortType,Port | ft -auto;


Now, you can get a list of all your sendPorts. Don’t be afraid to experiment. You are only querying BizTalk objects using PowerShell.

My next blog will show this query use to list all ReceivePorts.

Happy PowerShelling!!