PowerShell and T-SQL converting XML UTC DateTime value to DateTime string

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:

[sourcecode language=”text”]
:
<OrderDate>2001-07-01T00:00:00-04:00</OrderDate>
:
[/sourcecode]

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:

[sourcecode language=”powershell”]
$utc = "2001-07-01T00:00:00-04:00";
[datetime] $converte_DateTime = $utc;

## – 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:

[sourcecode language=”text”]
:
<OrderDate>7/1/2001 12:00:00 AM</OrderDate>
:
[/sourcecode]

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.

Don’t be afraid!

PowerShell – SQL Server SMO has XML and XSD Schema method.

As I continue to work with Microsoft BizTalk Server it still amazed me when I can find something useful.  As I was building a BizTalk solution that involves getting data from our SQL Server table, I found an SMO method that allow me to create both the XML and the XML Schema (XSD) so I can use them in my BizTalk solution.

Here’s the quick route using PowerShell and SQL Server SMO (SQL Server Management Objects) to create your XML Schema from a SQL table.  Keep in mind, in order to use SQL Server SMO you must have SQL Server 2005 or greater.

1. Here’s a simple T-SQL script to query a table:

[sourcecode language=”powershell”]
## – Prepare you SQL Script in ‘PowerShell’ Here-String:
## (for more Info on ‘Here-String’use the command: get-help about_Quoting_Rules
$Sqlqry1 = @’
Use Northwind
SELECT TOP 3 [OrderID]
,[CustomerID]
,[EmployeeID]
,[OrderDate]
,[RequiredDate]
,[ShippedDate]
,[ShipVia]
,[Freight]
,[ShipName]
,[ShipAddress]
,[ShipCity]
,[ShipRegion]
,[ShipPostalCode]
,[ShipCountry]
FROM [Northwind].[dbo].[Orders]
‘@;

[/sourcecode]

2. Then, the next three SMO lines we get the information and store it in our variable.  Make sure to define the variable as an array so you can get the right format:

[sourcecode language=”powershell”]
## – Load the SQL Server SMO Assembly:
[System.Reflection.Assembly]::loadwithPartialName(‘Microsoft.SQLServer.SMO’)

## – Connect to your SQL Server Instance and execute your SQL Query:
$MySQL = New-Object(‘Microsoft.SqlServer.Management.SMO.Server’) ‘BIZTALK2K10DEV’;
$tbldata = $MySQL.Databases[‘Northwind’].ExecuteWithResults($Sqlqry1);

[/sourcecode]

3. The following line just check the first 10 records in your PowerShell object:

[sourcecode language=”powershell”]
## – Display the first 10 lines of your data:
($tbldata.Tables) | Select -First 10 $_ | Format-Table -AutoSize;

[/sourcecode]

4. Finally, we can use the “.WriteXML()” and “.WriteXmlSchema()” methods to create our XML and XSD files:

[sourcecode language=”powershell”]
## – Create you XML file with your data:
($tbldata).WriteXml(‘C:\PowerShellScripts\tblServiceFacts.xml’);

## – To create the XML Schema (.xsd file) use the following method:
($tbldata).WriteXmlSchema(‘C:\PowerShellScripts\tblServiceFacts.xsd’);

[/sourcecode]

Sapien XML Editor - looking at teh XSD file created with SMO
Sapien XML Editor - looking at the XML file generated with SMO

We are ready to incorporate the XSD file in your BizTalk solution(s).

There’s a few things to keep in mind. The above version will work with SQL Server 2008 but will behave different with SQL Server 2005 and PowerShell V2.0 it might giving you an “overload” error.

So, if you encounter the following error, then here’s a possible how-to fix it: (it work for me but further testing in other environment gave me a different results.  It’s all just about testing and understanding your tools.

[sourcecode language=”powershell”]
## – Create you XML file with your data:
$tbldata.WriteXml([string] ‘C:\PowerShellScripts\tblOrders.xml’);

## – To create the XML Schema (.xsd file) use the following method:
$tbldata.WriteXmlSchema([string] ‘C:\PowerShellScripts\tblOrders.xsd’);

[/sourcecode]

Don’t forget to test your code to make sure you’re getting what you want.   The main code will work in both PowerShell v2.0/v3.0, and SQL Server 2008 (or greater).

Awesome Technology!   I will come back to this issue later and provide the PowerShell V2 solution.

PowerShell – Quick sample of getting a BizTalk Performance Counter information

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:

 [System.Reflection.Assembly]::LoadWithPartialName(“System.Diagnostics”)

2. Then, we can create the PowerShell variable that will hold our results:

$bpc = new-object system.diagnostics.PerformanceCounter(“BizTalk:Message Agent“, “Total messages published“, “YourHostInstanceName”, “BTSserver”);

3. To display the information just type: $bpc.

Sample output:

PS D:\> $pbc

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.

PowerShell – Adding a Sequence column to your result

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;