Month: March 2012

I took the moment posted the session I’ll be presenting at the Orlando Code Camp 2012 on Saturday March 31.  In this session I’ll be covering PowerShell, XML, Visual Studio Application, and integration these technologies in SQL Server Integration Services.  I’ll be reusing an existing Powershell script in a SSIS Script Task component.

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.

For many years, I’ve been a PowerShell enthusiast exposed to Network
Infrastructure and SQL Server technologies.  Now I’ve become a BizTalk
newbie.  And, with my responsibility as a BizTalk Developer, and I attended a BizTalk Server class online.  For my surprise, in one of the modules, instructor talked about a PowerShell Shell script .  I just hope the script hasn’t scare anyone attending the course.

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:

[sourcecode language=”powershell”]
$myBTStask = BTStask.exe ListApps
$myBTStask
[/sourcecode]

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:

[sourcecode language=”powershell”]
$MyBTStask | where{$_ -match ‘-Appl’}
[/sourcecode]

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:

[sourcecode language=”powershell”]
## – 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
[/sourcecode]

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

[sourcecode language=”powershell”]
## – 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
[/sourcecode]

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

Here’s my sample queries for both PowerShell and T-SQL scripts.  I also included a basic comparison chart created in excel.

Click link to download presentation:

Thanks to everyone for attending my session!