PowerShell: Extracting Persisted Workflow Instances to XML Files

Sometimes, it is useful to extract variables from a persisted workflow to debug an issue or to verify your persistence for various reasons. Workflows are persisted in binary XML format by default, so it’s possible to extract data into an XML file. Creating a project in Visual Studio to do this is somewhat tedious so I created a PowerShell script to help me out a little.

The script uses ADO.NET to connect to the workflow persistence store, which in my case is a SQL Server database. It reads the relevant table and then exports the data to a file which is given the name of the workflow instance ID.

I am assuming you have used PowerShell before. If not, then check out PowerGUI to get you started. If you have not installed PowerShell yet, you will need to do two things to run this script in PowerShell…

Firstly, you will need to run the Set-ExecutionPolicy cmdlet so that you can run scripts. I use RemoteSigned which ensures that any script downloaded onto my machine needs to be signed before they can run. This will allow you to run scripts created on your machine freely.

Secondly, I am using .NET 4.0 classes in my script so I need to make sure that PowerShell is running in a 4.0 runtime or above.

To check which version you have running, type “[System.Environment]::Version” into your PowerShell console. If the version is less than 4.0, then you will need to add the following Registry key to your Registry:

32-bit apps on a 32-bit operating system
64-bit apps on a 64-bit operating system
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework]
"OnlyUseLatestCLR"=dword:00000001

32-bit apps on a 64-bit operating system
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework]
"OnlyUseLatestCLR"=dword:00000001

So now, you can paste the following script into your PowerShell script file, replacing the connection string value and the query with your required settings. In my case, I am extracting data from the ReadWriteComplexDataProperties column, so you may also want to change this.

$connectionstring = 
  "Data Source=localhost\SQLExpress;Initial Catalog=SQLPersistenceStore;
  Integrated Security=True;"
$connection = New-Object System.Data.SqlClient.SqlConnection $connectionstring
$command = $connection.CreateCommand()
$command.CommandText = "select * from [system.activities.durableinstancing].instances"
$connection.Open()
$reader = $command.ExecuteReader()
[System.Reflection.Assembly]::LoadWithPartialName("System.Runtime.Serialization")
while($reader.Read())
{
    $report = $reader["ReadWriteComplexDataProperties"]
    if (!$report.Equals([System.DBNull]::Value))
    {
        $stream = New-Object System.IO.MemoryStream (,$report) 
        $xmlReader = [System.Xml.XmlDictionaryReader]::CreateBinaryReader(
                         $stream,[System.Xml.XmlDictionaryReaderQuotas]::Max)
        $xmlReader.Read();
        if($xmlReader)
        {
            $xml = $xmlReader.ReadOuterXml()
            $instanceid = $reader["instanceid"].ToString()
            $xml | Out-File c:\temp\reports\$instanceid.xml
        }
    }
}
$connection.Close()
trap [Exception]
{
    $connection.Close()
}

I think the script is fairly self-explanatory. The only thing I think worth mentioning here is the trap exception block at the end which will ensure that the connection is closed in the event of an error.

You can download the script and Registry file from here.