The official PASS Blog is where you’ll find the latest blog posts from PASS community members and the PASS Board. Contributors share their thoughts and discuss a wide variety of topics spanning PASS and the data community.

WorkFlows – What Are They? “The Book Definition”

WorkFlows – What Are They? – “The Book Definition”

Windows PowerShell Workflows are designed for scenarios where these attributes are required:

  • Long-running activities.
  • Repeatable activities.
  • Frequently executed activities.
  • Running activities in parallel across one or more machines.
  • Interruptible activities that can be stopped and re-started, which includes surviving a reboot of the system against which the workflow is executing.

In this short hands-on guide, I will cover this item:

  • Running activities in parallel across one or more machines.

The Hello World Workflow Program

Let’s follow convention and create a Hello World program that does some real (but simple) work.

This is the HellowWorld.ps1 script

The workflow looks like a function, but the syntax inside is not the same as usual PowerShell code. This is why it’s highly recommended that you use the InlineScript code blocks – inside the InlineScript you can use the PowerShell syntax. The first InlineScript just displays a message and the date.

The next one is the parallel section of the program. Any activities you place inside the parallel code block will be executed in parallel. In this demo, our activities are simple delays of 15, 10 and 5 seconds.

For good measure, we add another InlineScript at the end of the workflow, similar to the first one.

This simple workflow can be executed from the editor (I am using Visual Studio Code) and when executed, we get this output:

The total execution time is the longest of the three, so the workflow is working in parallel as expected.

I am introducing the InlineScript since the very first program, because they allow you to re-use existing PowerShell code in your workflow.

First Things First – Scope and Visibility

Green – PowerShell business as usual area.

Orange: Workflow rules apply – beware.

Get used to weird things.

Good to Know: You Can Pass Stuff to the InlineScript

FROM THE BOOK

InlineScript Variables

By default, the variables defined in a workflow aren’t visible to the commands in the InlineScript script block. To make workflow variables visible to the InlineScript, use the $Using scope modifier. The $Using scope modifier is required only once for each variable in the InlineScript.

Good to Know: You Can Return Stuff from the InlineScript

Returning Variables in InlineScript

InlineScript commands can change the value of the variable that was imported from workflow scope, but the changes aren’t visible in workflow scope. To make them visible, return the changes value to the workflow scope.

Let’s see an example of the passing/retrieving values to and from InlineScripts.

Save the code below in PowerShell script. Appropriately name it HelloWorld_InlineScript_parameters.ps1

When executing the above program, we get this output. We can see the values being passed in and out of the InlineScript.

Now that we can make better use of the InlineScript, let’s continue with the only workflow program you will ever need…

The Swiss Army Knife of Workflows

What’s New Here?

You can pass parameters to the workflows, and the most common case is to pass a list to it.

The list will be processed in one shot using the foreach-parallel loop – when you pass a list to it, every item on the list will be processed in parallel.

When actions must be executed in sequence for a given loop item, you can use the sequence feature. For the current list item, the first InlineScript inside the sequence will be executed. Then, for the same item, the next InlineScript within the sequence will be executed.

Let’s use this template for a full program that will do the following:
•    Connect to a list of SQL Servers and databases.
For ease of testing, we will use a local installation of SQL Server with 4 databases on it – copies of the AdventureWorks database named AdventureWorks2008R2, AdventureWorks2008R2_A, AdventureWorks2008R2_B, and AdventureWorks2008R2_C.
Since we are using a single SQL server, each list item will be of the form sqlserver|database.
•    Select data from a table and save it in a CSV file.
•    The CSV file will be compressed.
•    An email report will be sent.

Let’s be organized and get our support function ready. Save this code into a file, name it Basic_WorkFlow_CodeBlocks.ps1

.

Invoke_Sqlcmd3 will bring data from the SQL Server|database current item, GetParams gets the current pair of sqlserver and database, and SendMail … well it just sends emails.

Now let’s see the main program. Save this code as Basic_workFlow.ps1

Here is the output we get when executing:

Then, we get the adequate and not fancy email:

Yes, the files were compressed, see them in blue:

This is all there is, word count restrictions. 😊

Thanks for reading, stay put for a second part on stopping and resuming workflows.

Jorge Besada
About the author

Jorge Besada is a SQL Server DBA with more than 15 years’ experience as a DBA. An electronic engineer in a previous life with an MSEE degree, Jorge morphed into an Information Technology professional and is enjoying every minute of it. His main interest is in automation of all tasks using PowerShell and Python, switching between them as needed.

Please login or register to post comments.

Theme picker

Back to Top
cage-aids
cage-aids
cage-aids
cage-aids