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!

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:

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!!

sample PowerShell Script

Using the PowerShell script in SSIS Script Task

SQL Server 2012 SSIS package

SSIS Script Task section executing the PowerShell code

This session is full sample: XML files, PowerShell scripts, VB/C# code for both Visual Studio and SSIS Script Task showing some basic techniques.

For those attending this event, I hope to see you at my session.

PowerShell is much easier, and fun work once you start using it.  Try to ignore using cmd.exe, and start using PowerShell.   Try it!!   For example try to execute the BTStask.exe command.

The following sample will save the output console results to a PowerShell variable:

$myBTStask = BTStask.exe ListApps
$myBTStask

Notice I’ve create my variable “$MyBTStask”, and now I can  extract some information stored in it.  For example, I only want to list all application information:

$MyBTStask | where{$_ -match '-Appl'}

Executing "BTStask.exe" from the PowerShell Console

Now, you can evolved in this simple script into something more sophisticated.

I’m always looking for ways to help me be productive in my  work environment.  So, I found a couple following CodePlex projects, and PowerShell was included:

BizTalk 2010 Web Console: http://abdulrafaysbiztalk.wordpress.com/
PowerShell BizTalk provider: http://psbiztalk.codeplex.com/

And, there’s more codeplex BizTalk project out there.  Also, there’s some very thorough BizTalk Training Kits available from Microsoft, include Videos, Labs, and Virtual machines.  But you will to use  Microsoft Hyper-V Virtualization Technology:

BizTalk Server 2010 Developer Training Kit: http://www.microsoft.com/download/en/details.aspx?id=14865
BizTalk Server 2010 Administrator Training Kit: http://www.microsoft.com/download/en/details.aspx?id=27148

BizTalk Server 2010 ESB Training Kit: http://www.microsoft.com/download/en/details.aspx?id=27151

So, in my case I use my laptop with Windows 8 Server w/Hyper-V 3.0 and it works GREAT!!

Now, I’m used to work with SQL Server so it makes sanse to me to  explore the possibilities of using PowerShell to help with my BizTalk  Administration.  So, I can check for my Biztalk table in my SQL Server  local instance in the following way:

## - Listing your local Databases:
[System.Reflection.Assembly]::loadwithPartialName("Microsoft.SQLServer.SMO")
$MySQL = New-Object('Microsoft.SqlServer.Management.SMO.Server')
$MySQL.Databases | Select Name, owner, Size | Ft -auto

Also, I could list all my SQL Agent jobs with the following generic PowerShell script:

## - Listing your SQL Agent jobs:
[System.Reflection.Assembly]::loadwithPartialName("Microsoft.SQLServer.SMO")
$MySQL = New-Object('Microsoft.SqlServer.Management.SMO.Server')
$MySQL.JobServer.Jobs | Select name, isenabled, lastrundate, lastRunOutcome | ft -auto

As you can see, these little simple oneliners you can give lots of  information about your Biztalk box.  This is all executed on your box  which might bring the question: Can I do execute PowerShell script in  another BizTalk box? And the answer is: YES!! But it all depends if your  IT organization will allow it.

Keep in mind, you need to have the proper permissions to allow you to  access these SQL server box.  In the meantime, if you have your on  Virtual Machine, at least you can practice show management how they can  benefit from using PowerShell.

Happy PowerShelling!!

Click link to download presentation:

Thanks to everyone for attending my session!

PowerShell Query vs T-SQL Comparison

So, first we need to create a new db snapshot of my AdventureWorks database using T-SQL Script:

CREATE DATABASE AdventureWorks_dbSnapShot_0001 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSQLDENALICTP3\MSSQL\DATA\AdventureWorks_Data_0001.ss' )
AS SNAPSHOT OF AdventureWorks;
GO

Now, I go to SSMS and verify my new database snapshot exist by going into the ‘Object Explorer’ and looking under ‘Database Shashots’ folder.

Using T-SQL

If I use the following T-SQL command to list all my databases:

Select * from [sys].[sysdatabases]

I will get all of them listed including the snapshots. So, here’s another way to use T-SQL to identify all database snapshots. For that, I’m going to do a select and use from the Master db the view named “[sys].[databases]“:

SELECT [name]
      ,[database_id]
      ,[source_database_id]
      ,[owner_sid]
      ,[create_date]
  FROM [master].[sys].[databases]

In this result, pay attention to the “[source_database_id]” column. Notice that this column is mostly “null” except when if it has a value. This value will match the “[Database_ID]” column. So, you can use this to identify database snapshots.

Using PowerShell

Now, let’s take a look on how to use PowerShell to identify database snapshots using SMO. In the following code I’m listing all databases but snapshots are also included.

Import-Module SQLPS -DisableNameChecking
$MySQL = New-Object Microsoft.SqlServer.management.Smo.Server 'YourServername\InstanceName'
$MySQL.Databases | Select name

So, in order to identify the database snapshot I need to look deeper into our .NET database object. Using the “Get-Member” command I can see what’s object are available.

($MySQL.Databases) | Get-Member | Out-Gridview

Using the “Out-Gridview” command to view my results a separate popup window, I found two properties of interest:

1. ‘IsDatabaseSnapshot‘ – which show “true” us if is a snapshot.
2. ‘DatabaseSnapshotBaseName‘ – which give us the origne of database name of the snapshot.

So, now I can use the following PowerShell commands to show all my databases and identify the snapshots:

$MySQL.Databases | `
   Select name, Owner, RecoveryModel, `
   IsDatabaseSnapshot, DatabaseSnapshotBaseName `
   FT -AutoSize;

Conclusion

Using both T-SQL and PowerShell examples, now you have a startup point to spot and take some control over your database snapshots.

Happy PowerShelling!

Here’s my “Windows 8 PowerShell and Hyper-V 3.0 Preview”presentation and demo scripts use during the session:

Please, don’t hesitate to contact me if you have any questions.

Thanks You!

$str = "Redrum";
[System.Array]::Reverse(([Array]$RevStr = $str.ToCharArray()));
foreach($chr in $RevStr){ $mistery += $chr }
$mistery;

That’s all for now…. Have fun with PowerShell!

In this example, I’m passing my credential to the “Start-Transfer” command, so I can do my file download to my destination folder.  Here’s the code snippet to accomplish the automation:

## - Automate to create your credential:
$MyUserName = "Domain\Username";
$MyPassword =  ConvertTo-SecureString 'MyPwd001!' -asplaintext -force;
$MyCredentials = new-object `
-typename System.Management.Automation.PSCredential `
-argumentlist $MyUserName,$MyPassword;

## - Import the module and start the download process of one file:
Import-Module BitsTransfer;
Start-BitsTransfer `
-Credential $MyCredentials `
-Source 'http://YoufilesSite/Files/Demo01.zip' `
-Destination '\\YourServer\NetworkSharedFolder\Demo01.zip';

Ha!  I know what you’re think!  I’m hardcoding my password in the scripts.  So, our possible options would be: 1) you’re the only one running this script (don’t tell your boss),  2) You trust your Network security (humm!), or 3) find the way to encrypt the script so no one can guess the password.

There are products like SAPIEN’s PrimalScript and PrimalForms, that will let you create an executable out of your script, and then you can deploy it to your server.  But, then again, only you can make that decision.  I’m just showing that’s possible in case you need it.

Have fun with PowerShell!