Feb 22, 2011

PowerActivity 2010 (Advanced SharePoint Designer 2010 Workflow Actions)

I’m glad to announce that I’ve just added the “Execute PowerShell Script” action (aka PowerActivity 2010) to the latest build of Advanced SharePoint Designer 2010 Workflow Actions.

Add “Execute PowerShell Script” action to a SharePoint Designer Workflow

image

Configuration

image

Script

The PowerShell script to execute. You can use the following predefined variables:

  • $site = the current Microft.SharePoint.SPSite
  • $web = the current Microft.SharePoint.SPWeb
  • $list = the current Microft.SharePoint.SPList (null in site workflows)
  • $item= the current Microft.SharePoint.SPListItem (null in site workflows)
  • $ctx = the current workflow context Microsoft.SharePoint.WorkflowActions.WorkflowContext
  • $sharePointService = the current Microsoft.SharePoint.Workflow.ISharePointService service
  • $listItemService = the curent Microsoft.SharePoint.Workflow.IListItemService service

The process will run as system account. However, by default $site and $web will run as the current workflow initiator or author (impersonation step). When you want to impersonate the site to the system account create a site like this: $impersonatedSite = new-object Microsoft.SharePoint.SPSite($site.Id).

Avoid the usage of the SharePoint Designer Text Editor tokens in the script, because of the potential risk for script injection attacks. Instead use variable binding ($var1, $var2…)

Signature

By default every script you that want to execute must be digitally signed. However, you can change the setting to not require a digital signature (dev system). The corresponding PowerShell script is included in package.

Disable Script Signing

The script must run on a SharePoint machine

PS> & .\Set-PowerActivityScriptSigning.ps1 $false

Sign Scripts

In order to sign scripts you need the private key that has been generated during the installation of solution.

1. Export Private Key

The script must run on a SharePoint machine. Keep the private key secure.

PS> & .\Export-Key.ps1 –path “C:\private.key” –includePrivateKey $true

2. Sign the Script

After you have exported the private key you can sign the script. The signing must not be done on a SharePoint machine, you only need the private key and the script. To sign the script you have to save it temporarily to a file.

PS> & .\Sign-PowerActivityScript.ps1 –keyPath “C:\private.key” –scriptPath “C:\script.ps1”

The signature will look like this:


YoVKP2lCKF1B8Q9ZIBvy98+T2haNayr81aGTfXEPyRmFxLNII3R6pagaHVZfZ7j5L2zTga53SqetVRLgTUebiOan2WUnCR2HualqvvdXYX1LhwAZ/Kda
OibqpE5aGgZTesJJln8+81Qwy4wOqSxCv3iQjIgVbJz7+D3kIm9dP7Q=

Simply copy & paste the script and the signature to the script and signature field of the “Execute PowerShell Script” action.

White spaces in the script will be ignored in the signing process.

$var1, $var2, $var3, $var4, $var5

You can bind PowerShell variables ($var1, $var2…) to workflow variables.

$web.Title = $var1

$web.Update()

The binding is two way, this means you can change the workflow variables in the script.

$var2 = “my value”

$secure

$secure is a special variable that can contain an encrypted string that will be decrypted during runtime. You could use this variable to securely store a password. The binding ot this variable is one way, means you can not set the value in the script during runtime. For how to encrypt strings read this.

Secure Store AppId

The Secure Store App have to define 2 Fields. One of type “User Name” and one of type “Password”. The Field Name doesn’t matter, important is the Field Type! You have to map the credentials of the SharePoint Service Accounts (AppPool/owstimer.exe). The secure store option will only works with SharePoint Server, not with Foundation! You can access the credentials during runtime with the $credential variable (System.Net.NetworkCredentials).

Wiki Documentation

Feb 21, 2011

Execute SQL Action (Advanced SharePoint Designer 2010 Workflow Actions)

Execute a SQL statement from within a SharePoint Designer 2010 Workflow. The action and its source can be downloaded here.

Add “Execute SQL” Action to a SharePoint Designer 2010 Workflow

image

Configure the Action

image
  • SQL: The SQL statement that you want to execute. You could also use stored procedures e.g. “EXEC [test_Proc] @var1=N’test’ ”-You can use two special tokens in the statement that can not be inserted via the UI: [WebUrl] and [WorkflowInstanceId].
  • Provider: The .NET ADO Provider. Default is MS SQL Server (System.Data.SqlClient). You can use other providers like ODBC, Oracle…
  • Connection: The connection string e.g. “Server=localhost\SQLExpress;Database=test;User ID=sa;Password=P@ssw0rd”. Integrated Security is for security reasons not supported! You could either specify the password  in clear text or encrypted like described in [Encrypt Passwords for SharePoint Designer Workflow Actions].
  • Result: The first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
  • AppId: The The Secure Store Application ID. The Secure Store App have to define 2 Fields. One of type “User Name” and one of type “Password”. The Field Name doesn’t matter, important is the Field Type! You have to map the credentials of the SharePoint Service Accounts (AppPool/owstimer.exe). The secure store option will only works with SharePoint Server not with Foundation! In order to use the credentials in the Connection String you can use the token {0} for the User Name and {1} for the password. E.g. “Server=localhost;Database=test;User ID={0};Password={1}”

Example

Simple insert statement.
image
Configuration of “Execute SQL”
  • SQL: INSERT INTO MyTable (Column1, Column2) VALUES ([%Current Items:ID%],’[%Current Items:Title%]’)
  • Provider: System.Data.SqlClient
  • Connection String: Server=localhost; Database=MyDatabase;User ID={0}; Password={1}
  • Secure Store AppId: DBCred ({0} in the connection string will be replaced with User Name and {1} with Password defined in the Secure Store App)
  • Output: Workflow Variable “Result” (will return 0)
Wiki Documentation

Feb 19, 2011

Create Site Action (Advanced SharePoint Designer 2010 Workflow Actions)

The first action I would like to introduce is the “Create a Site” workflow action. With this action you can create SharePoint sites via SharePoint Designer Workflows. The action and the source can be downloaded here.

Add the “Create a Site” action to a SharePoint 2010 Designer Workflow

image

Configuration

image
URL: The url of the web site. You could either use an relative url to create the site direct under the current site or an absolute url to create the site under any site. The workflow initiator needs appropriate permissions. Or you can use an impersonation step to run the action under the permissions of the workflow author.
Title: Title of the site
Description: Description of the site
Template: Template name of the new site. Default is Team Site (STS#0).
You can get a list of all farm templates with the PowerShell command “Get-SPWebTemplates”.
To get a list of templates from a specific site’s template catalog use the following PowerShell command:
Get-SPWeb http://contoso/sites/spd | %{$_.GetAvailableWebTemplates($_.Language)}
image
Use the name of the template e.g. WIKI#0
Language Code: The language code for the new site e.g. 1033 for English or 1031 for German. 0 means inherit the language from the parent site.
Inherit Permission: Inherit permissions from the parent site.
Inherit Top Navigation: Use the  same top navigation as the parent site.
Output: Returns the absolute url of the new site

image

Publish and run the workflow…

Wiki Documentaion

Feb 17, 2011

Just Released: Advanced Workflow Actions for SharePoint Designer 2010

I’m glad to announce that I’ve just published my Advanced Workflow Actions for SharePoint Designer 2010. The solution contains the following Workflow Actions:
  • Create Site Action
  • Get Parent Folder Action
  • Create XML File Action
  • HTTP-Request Action
  • Query XML Action
  • Query XML-File Action
  • Set XML Action
  • Update XML Document Action
  • Call a Web Service Action
  • Execute SQL Action
  • Wait for External Data
  • Regular Expression Actions
download | documentation
The source code is available too :-)
In further posts I will introduce each of the actions…
PS: I’m not a fun of SharePoint Designer Workflows. In almost every customer project I use Nintex Workflow!