ESB Toolkit Powershell Script

hi,

 

recently I have to deploy Itinerary on diffferent BizTalk platform which use the ESB Toolkit and I decide to create some functions to Get, Set and Update Itinerary in the EsbItineraryDb.

 

So here is the differents functions :

 
# function GetItinerary
# String ConnectionString //to the EsbItineraryDb
# String IniteraryName
# String FileName // Path+Filename to save the Itinerary
function GetItinerary {

    param([string]$ConnectionString,[string]$ItineraryName,[string]$FileName)
    $returnedLines=0
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString  = $ConnectionString
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "EXEC [dbo].[Itinerary_getitinerary]             
@name = '$ItineraryName',             
@major = NULL,             
@minor = NULL"
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object  System.Data.DataSet
    $returnedLines = $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()

    $xml = [xml]$DataSet.Tables[0].Rows[0]["imITML"]
    $xml.save($FileName)
    return $returnedLines
}

# function UpdateItinerary
# String ConnectionString //to the EsbItineraryDb
# String FileName // Path+Filename of the Itinerary to Load
# The name, version etc are directly extracted from the Xml

function UpdateItinerary{
    param([string]$ConnectionString,[string]$FileName )

    $xml = [xml] (Get-Content $FileName) 
    $name = $xml.Itinerary.name
    $version_xml = $xml.Itinerary.version
    $version_split = $version_xml.split('.' )
    $major = $version_split[0]
    $minor = $version_split[1]
    $itml = $xml.innerxml
    $itml = $itml.Replace("'","''")
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString  = $ConnectionString
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "EXEC    [dbo].[Itinerary_updateitinerary]             
@name = '$name',             
@major = '$major',             
@minor = '$minor',             
@itml = N'$itml',             
@status = 1"
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object  System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
}

# function UpdateItinerary
# String ConnectionString //to the EsbItineraryDb
# String FileName // Path+Filename of the Itinerary to Load
# The name, version etc are directly extracted from the Xml
function SetItinerary{
    param([string]$ConnectionString,[string]$FileName )


    $xml = [xml] (Get-Content $FileName) 

    $name = $xml.Itinerary.name
    $version_xml = $xml.Itinerary.version
    $version_split = $version_xml.split('.' )
    $major = $version_split[0]
    $minor = $version_split[1]
    $itml = $xml.innerxml
    $itml = $itml.Replace("'","''")
    $modifiedTime = [datetime]::Now.Date.ToString("yyyy-MM-dd hh:mm:ss")

    $modifiedBy = [Environment]::UserDomainName+""+[Environment]::UserName
    $machine = [Environment]::MachineName

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString  = $ConnectionString
    $SqlCmd = New-Object& nbsp;System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "EXEC [dbo].[Itinerary_saveitinerary]             
@name = '$name',             
@major = '$major',             
@minor = '$minor',             
@description = '$name',             
@modifiedby = '$modifiedBy',             
@modifiedtime = '$modifiedTime',             
@itml = N'$itml',             
@user = NULL,             
@altuser = NULL,             
@machine = '$machine',             
@status = 1"
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand  = $SqlCmd
    $DataSet = New-Object  System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
}

# Script to GetItinerary and do a Update IF EXIST and Set If NOT

$conString = "ConnectionStringToItineraryDb"
$fileBackup = "PathItinerarybackup.xml"
$fileNew = "PathItinerary.xml"
$nbLines = 0

$nbLines = GetItinerary $conString $itinerary $fileBackup
If ($nbLines >0) {

    UpdateItinerary $conString $fileNew
}
else {
    SetItinerary $conString $fileNew
}

I will try to see with Maxime Labelle to insert these function in the PowerShell Provider for BizTalk

Advertisement