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:

:
<OrderDate>2001-07-01T00:00:00-04:00</OrderDate>
:

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:

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

## - Resulting in:
#->  Sunday, July 1, 2001 12:00:00 AM

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:

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

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:

## - the following routine will correct UTC datetime as string:
 $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');

The results after convert the DateTime to the new XML DateTime values fix will look like this:

:
<OrderDate>7/1/2001 12:00:00 AM</OrderDate>
:

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:

## - 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]
'@;

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:

## - 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);

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

## - Display the first 10 lines of your data:
($tbldata.Tables) | Select -First 10 $_ | Format-Table -AutoSize;

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

## - 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');

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.

## - 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');

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.

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