Jim Britt's [MSFT] Blog

This blog will contain lessons learned as it relates to automation specific to Opalis / System Center Orchestrator.

Using an Excel XML File for Input to a Runbook

Using an Excel XML File for Input to a Runbook

  • Comments 2
  • Likes

Hello readers.  I thought it would be a good idea to quickly discuss a topic that came up during a recent Runbook development effort.  I was faced with the challenge of how to do the following:

  1. Provide an easy way to request data from an end user
  2. Make it familiar and intuitive
  3. Provide the data in a format that could be easily consumed by Orchestrator via PowerShell.

Solution?

I opted for a quick and dirty Excel XML file.  Looks exactly like an EXCEL XLS file but based on XML data format.  The benefits from this approach were two fold.  I didn’t have to program a fancy front end interface (more on this in a future blog post however Smile), and PowerShell could consume this data and immediately turn it into Published Data for the Runbook processing within Orchestrator.

STEP 1

So where do you start?  Start with a basic Excel XML file.  The easiest thing to do is put columns together as shown below that frame up what you are looking to gather.  Highlights can be leveraged as shown to call attention to required data.  In addition, you can even put in some data validation (three letters required, etc.) as well as “hover text” to provide your end user the ability to review helpful tips as they are filling out the required data.

image

STEP 2

Now that you have a basic template file put together, you can either have the user drop it into a pre-determined (monitored by Orchestrator) input directory and allow automation to pick up this file and appropriately process it.  Or what we did was attach it to a service ticket (in our case Team Foundation Server) and allow Orchestrator to collect up that file from the ticket, process the data inside, and act accordingly (updating the ticket along the way of progress).

STEP 3

So far so good right?  Next, once you have an idea of where you are going to place the file for processing, leverage a simple Run.NET object within Orchestrator to process the file according to cell and row analysis.  The code snippit below basically sets variables in PowerShell according to text values located within the called out row and cell. Then you set returned data within your Runbook to the PowerShell variables.

Example: line 9 below evaluates $table.Row[1].Cell[1].Data."#text"

The value shown in row(1) and cell(1) is “SEC”.  In contract row(0) cell(1) is “Values”.  So for each row and cell combination you have with data you need, you will set to an appropriate variable that you can leverage within your Runbook inside Orchestrator.

 1: #Setup the file and initialize Excel COM object
 2: $file1 = "{Published Data}"
 3:  
 4: #Read in XML Data
 5: $template  = [xml](Get-Content "$File1")
 6: $table = $template.Workbook.Worksheet[0].Table
 7:  
 8: #Read values and set variables
 9: $DeployType        = $table.Row[1].Cell[1].Data."#text"
 10: $DecomSvr           = $table.Row[2].Cell[1].Data."#text"
 11: $DecomVar           = $table.Row[3].Cell[1].Data."#text"
 12: $NewSvr               = $table.Row[4].Cell[1].Data."#text"
 13: $NewSite              = $table.Row[5].Cell[1].Data."#text"
 14: $ParentSvr           = $table.Row[6].Cell[1].Data."#text"
 15: $CNTSVR              = $table.Row[7].Cell[1].Data."#text"
 16: $NetworkBin         = $table.Row[8].Cell[1].Data."#text"
 17: $LocalBin              = $table.Row[9].Cell[1].Data."#text"
 18: $dpGroupOSD      = $table.Row[10].Cell[1].Data."#text"
 19: $Install_directory = $table.Row[11].Cell[1].Data."#text"
 20: $PKGID                 = $table.Row[12].Cell[1].Data."#text"
 21: $DP_Drive            = $table.Row[13].Cell[1].Data."#text"
 22: $emailAddress      = $table.Row[14].Cell[1].Data."#text"

STEP 4

Last and final step is to set Published Data within your Runbook to the variable data you have set in your Run.NET object.

image

That’s it!  I’ve provided an example input file and PowerShell script for you to review and play around with.  If you have any questions – as always please don’t hesitate to ask!  Thanks for stopping by and till next time, Happy Automating!

Process XML Example Files Process-XML.zip

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • If you have a requirement to use an XLS file you can also directly read XLS files from PowerShell without needing Excel installed by using an ADO connection.  

    www.codeproject.com/.../PowerShell_Guideline_p3.aspx

  • Vaughn, thanks for the comment and URL.  You are absolutely right.  We did head down that path initially but ran into some issues initially with Server 2008 and security restrictions with server side automation.  Rather than spending a ton of time on it, we opted to move in the direction of XML.  This had to do with some interactivity limitations I believe.  This is where I started with this one but decided to go a different route when we ran into issues with this working in Opalis at the beginning.

    #######################

    #Setup the file and initialize Excel COM object

    $file1 = "c:\temp\book2.xlsx"

    $excel = New-Object -COM Excel.Application

    $excel.Visible = $False

    $book = $excel.WorkBooks.Open("$file1")

    #$book = $excel.Workbooks.PSBase.GetType().InvokeMember('Open', [Reflection.BindingFlags]::InvokeMethod,$null,$excel.Workbooks,"c:\temp\book2.xlsx")

    $objWorksheet = $book.Worksheets.Item(1)

    #Read values and set variables

    #removed this area because it is not relevant for the error

    #Quit the worksheet and destroy variable

    $objWorksheet = ""

    #Close workbook

    $excel.workbooks.Close

    $excel.quit()

    #Perform Garbabe Collection and release COM object from memory

    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)

    [gc]::Collect()

    ###########################

    Future development efforts for newer workflows leveraged a PowerShell front end with WPF creating an standard XML file that was processed by Orchestrator.  Thanks again for the post and feedback.