Following my previous blog (“PowerShell – SQL Server SMO has XML and XSD Schema method“) I continue to use SMO to generate my XML files, I encountered that my SQL datetime field is converted to a UTC DateTime string. This gave me some work to get it back to a DateTime type. I had the requirement to provide my source file in XML format.
I realized using SMO WriteXml() method will write the DateTime type to a UTC DateTime string format in the XML file resulting in something like this:
Unfortunate, trying to pass this value to a SQL DateTime field is tricky. I look around the internet and there’s plenty of examples of UTC but not in this format “2001-07-01T00:00:00-04:00″. So, I ended up using PowerShell to ease off the pain.
Here’s an example of how PowerShell can easly convert UTC DateTime string to a regular System.DateTime type:
## – Resulting in:
#-> Sunday, July 1, 2001 12:00:00 AM
[/sourcecode]
That was easy. Right! But the issue is when we want to pass this value into a SQL DateTime type. Then, here’s a possible solution now that I got PowerShell to convert my UTC string to string DateTime output.
Using the string generated from PowerShell, as a proof-of-concept, I can test this value in the following T-SQL script:
[sourcecode language=”sql”]
–now using t-SQL to convert a UTC string to a standard datetime type value:
Declare @stringFromPowerShellDateTime nvarchar(50)
Declare @fixDate nvarchar(50)
Declare @cleanup int
Declare @dtDateTime DateTime
Set @stringFromPowerShellDateTime = ‘Sunday, July 1, 2001 12:00:00 AM’
Set @cleanup = (charindex(‘,’, @stringFromPowerShellDateTime, 1))
Set @fixDate = (Substring(@stringFromPowerShellDateTime, (@cleanup+1), len(@stringFromPowerShellDateTime)))
Set @dtDateTime = (Convert(DateTime, @fixDate, 101))
Select @dtDateTime
— – Resulting in:
– -> 2001-07-01 00:00:00.000
[/sourcecode]
The end result of this SQL script is to be able to create my SQL DateTime type from the PowerShell converted UTC-to-DateTime-String.
Now I need go back to my first XML file, and update my DateTime elements values with the converted UTC to string format. Here’s where I go back PowerShell to fix my XML file:
[sourcecode language=”powershell”]
## – the following routine will correct UTC datetime as string:
[xml] $xmlfile = Get-Content ‘C:\PowerShellScripts\ADWtblOrders1.xml’;
foreach($xItem in ($xmlFile.newdataset.table))
{
[string] $dt = ([DateTime] $xItem.OrderDate).ToString();
$xItem.OrderDate = $dt;
};
$xmlfile.save(‘C:\PowerShellScripts\ADWtblOrders1_New.xml’);
[/sourcecode]
The results after convert the DateTime to the new XML DateTime values fix will look like this:
Now, this DateTime values can be consume in SQL Server without any issues. Notice that I’m using the “xml” Accelerator which has a *.Save() method so you can save your changes to file .
I think you got the pieces necessary to use PowerShell, XML, SMO, and T-SQL.
As you all probably know, you can use the “Reliability and Performance Monitor” in older servers, or the “Performance Monitor” in newer ones. This mean going thru the GUI to start setting up you user-defined custom counters.
Well, for a pointer, the GUI can help in fine-tuning what you want to accomplished in your script. Here’s a quick example of getting a “BizTalk:Message Agent” Performance counter to get the “Total messages published” for a giving application. But before you start going crazy with BizTalk, make sure the application(s) are “Started“.
Note: To get Microsoft BizTalk Server Performance Counter information, here’s some links:
I have ran the following script code against all version of PowerShell V1->V3:
1. First you need to load the Assembly responsible of giving you the Performance Counter class:
CategoryName : BizTalk:Message Agent CounterHelp : The total number of messages published by the service class CounterName : Total messages published CounterType : NumberOfItems32 InstanceLifetime : Global InstanceName : BizTalkServerApplication ReadOnly : True MachineName : XXXXXXX RawValue : 71191 Site : Container :
And, that’s it.
You’re looking for the “RawValue“. To keep updating the result I notice you can call the variable again without running the two previous script lines. Eventually, you can enhanced this sample to go thru each of BizTalk Server, each BizTalk host instances, and pull the information.
That’s Awesome!
I’m using BizTalk Performance Counter as an example but you can probably figures out how to use the other counters, and the rest I leave the rest to your imagination.
I just the little things that can help while collecting information using PowerShell. This is one thing that can be reuse in so many ways and is just a matter of using one simple concept.
Let say you want to collect a list of files and at the same time you want to create a sequence# column to be included with this list and at the same time you know the total number listed.
Here’s my way I’m doing it:
1. First, we are going to create a PowerShell “Global” variable:
$Global:sequence = 1;
2. Then, we add the command (or could be another PowerShell variable holding the results). In this example I’m using “dir” to get the list of all “name” and include a sequence column using custom Expression scriptblock:
$Global:sequence = 1; dir | Select@{label = “Seq”; Expression = {$Global:sequence; $Global:Sequence++;}}, Name | Format-Table -Autosize;
Now the trick is simple. You define a global variable which will retain the incremental value produce inside the scriptblock in the “.. Expresion = {..}”.
Basically, just reuse the scriptblock “@{Label = “seq”; Expression = {$Global:Sequence; $Global:Sequence++;}}” in your PowerShell “Select” command.
Here’s a similar result:
Full code:
$Global:sequence = 1; dir | ` Select ` @{Label = “Seq”; Expression = {$Global:seq; $Global:seq++;}}, ` Name | Format-Table -Autosize;
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:
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.
sample PowerShell ScriptUsing the PowerShell script in SSIS Script TaskSQL Server 2012 SSIS packageSSIS 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.
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:
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:
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:
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:
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:
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.
Date: Saturday, September 24th – Session starts at: 2:45PM
Session Level: Intermediate
I’ll be covering some of the different way we can use PowerShell to extend our T-SQL scripting. This session will include a mix of using SMO, .NET classes, and SQLPS to help you understand the power for new scripting technology. At the end, we’ll be creating a solution that put together all this techniques.
You must have a valid Live ID account to be able to proceed downloading the SQL Server Denali CTP3, and must allow to install the Activex control or use the Java Applet for the Download Manager to begin the process.
Don’t forget the read the Release Notes, and have fun with it!!
