Exporting attachments to a ZIP file on the network share using a PowerShell action

Applies to version: 2022 R1 and above; author: Konrad Keppert

Related documentation

A detailed description of the functionalities mentioned herein and their configuration can be found on the following pages:

• Run a PowerShell script | WEBCON BPS

• Install Azure PowerShell on Windows | Microsoft Learn

Introduction

Users of WEBCON BPS often face the need to export attachments stored in workflows to ZIP archives. Examples for such scenarios are:

• reducing the size of attachment databases by exporting files to a network share and then removing them from the workflow,
• starting new workflow instances (using HotFolder) based on ZIP files.

In most cases, SDK plugins are used in these scenarios, as ZIP file creation is not available through standard BPS actions. However, this is possible using the PowerShell solution where scripts can be invoked in WEBCON BPS.

This article presents an example of how to use the "Run a PowerShell script" action (hereinafter referred to as "PowerShell action") to compress all attachments of the current workflow instance into a ZIP archive and save the resulting archive to disk.

Note: For the action to work properly, you may need to prepare the server on which the PowerShell scripts are to be run. For more information, see the documentation: Run a PowerShell script | WEBCON BPS.

At the end of this article, you will find some tips and best practices to help you customize or extend this solution.

Action configuration

Configuring the PowerShell action is very simple and requires only writing a script and optionally overriding the credentials of the user in whose context the script will run (by default, this is the account on which the Workflow Service runs). The latter will be important for privileges, both to the network share and to the databases (if integrated login is used in the SQL connection parameters).

The PowerShell action can only be executed in the context of a workflow instance, so it is not possible to add such an action in cyclical automation. This simplifies configuration because it always provides access to {WFD_ID}, so you can easily retrieve information about attachments to the current workflow instance from the database. To execute such an action cyclically, you can use the approach described at the end of this article.

A sample action is placed in the automation under the Menu button in one of the workflow steps:

Fig. 1. Action context (menu button)

No credentials have been entered in the action configuration, so the script is run in the context of the account that runs the Workflow Service. This is possible if this account has access to the network share on which the file is to be saved. If the service account has privileges to read the environment databases, it is also possible to use the login to SQL Server included in the connection parameters:

Fig 2. Action configuration

The full script used in the example, along with a description of the variables, can be found in the next section of this article.

To test the action, a workflow instance was created and two attachments were added to it. The action is available under the menu button, according to the configuration:

Fig. 3. Example of action triggering

When the action is executed successfully, a ZIP archive is created in the location specified in the script:

Fig. 4 Action results

Sample script

The following is the PowerShell script that was used in the example above.

The main parameters that need to be changed before implementation are:

$serverName – name of the SQL Server to connect to

$contentDbName – name of the content database from which to retrieve the attachment data

$attachmentDbName – name of the database where the attachment files are stored (attachment or content database)

$tempFolderPath – location of the technical folder for temporary files (will be removed after the script execution)

$zipFilePath – the full path, including the name of the ZIP archive where the resulting file will be saved

$query – SQL query that will select the filename column ($nameColumnName) and the binary content of the attachment ($valueColumnName).

Special attention should also be paid to the $connectionString variable, which represents the connection parameters to SQL Server that should allow reading data from the content database. In the example, the "Integrated Security=True" parameter is set because the service account in the context of which the PowerShell script is being run has read privileges to the content database.  In other cases, special SQL credentials may be required.

Script content:

## Database connection parameters
$serverName = "SQL01" 
$contentDbName = "BPS_Content" 
$attachmentDbName = "BPS_Content_Att" 
$valueColumnName = "ATF_Value" ## name of a column that stores att. binary data
$nameColumnName = "ATT_Name" ## name of a column that stores attachment name

## Temporary folder and result ZIP file saving location
$tempFolderPath = "C:zip_exportsql_files"
$zipFilePath = "C:zip_export{WFD_ID}.zip"

## Temporary folder creation, if not exists
if (-Not (Test-Path -Path $tempFolderPath)) {
    New-Item -ItemType Directory -Path $tempFolderPath
}

## Connect to a database and fetch files
$connectionString = "Server=$serverName;Database=$contentDbName;Integrated Security=True;"
$query = "SELECT $nameColumnName, $valueColumnName
FROM [$attachmentDbName].[dbo].[WFAttachmentFiles] 
	JOIN [$contentDbName].[dbo].[WFDataAttachmets] 
		ON ATF_ATTID = ATT_ID 
		AND ATF_Version = ATT_FileVersion 
		AND ATT_IsDeleted = 0
		AND ATT_WFDID = {WFD_ID}" 

try {
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $connection.Open()

    $command = $connection.CreateCommand()
    $command.CommandText = $query

    $reader = $command.ExecuteReader()
    while ($reader.Read()) {
        $fileName = $reader[$nameColumnName]
        $fileData = $reader[$valueColumnName]

        $filePath = Join-Path -Path $tempFolderPath -ChildPath "$fileName"
        [System.IO.File]::WriteAllBytes($filePath, $fileData)
    }

    $connection.Close()
} catch {
    Write-Error "Error occurred while fetching files from a database: $_"
    exit 1
}

## ZIP file creation
try {
    Add-Type -AssemblyName "System.IO.Compression.FileSystem"
    [System.IO.Compression.ZipFile]::CreateFromDirectory($tempFolderPath, $zipFilePath)
} catch {
    Write-Error "Error occurred while creating a ZIP file: $_"
    exit 1
}

## Delete temporary files
Remove-Item -Path $tempFolderPath -Recurse

Write-Output "Files were fetched from a database, compressed to a ZIP file and saved on disk: $zipFilePath"

Practical tips

User context

By default, the PowerShell action is executed in the context of the user who is running the Workflow Service, even if it is triggered from the form (BPS Portal). To change the context, specify the login and password of a given user in the action configuration. This is important for privileges to network shares and databases (if integrated login is selected in the connection parameters).

Cyclical action execution

As mentioned earlier, the PowerShell action cannot be defined in cyclical (global) automation, where the context of the workflow instance is missing. To enable its cyclical execution, the following approach is used:

• a technical workflow is created for the sole purpose of exporting data,
• the action "Start a subworkflow" is executed in a cyclical automation,
• the action configuration specifies the path through which the workflow instance will go after it is started,
• the PowerShell action is executed on the specified path,
• after successful saving, the workflow instance is archived (deleted).

This approach is recommended wherever there is a need for cyclical execution of the action that cannot be triggered globally in a standard way.

The archiving of the technical workflow instance can be delayed so that the history (logs) can be accessed if the action fails.

Extensibility (+ example of sending to Azure Blob Storage)

The are many applications of PowerShell in WEBCON BPS, including cyclical export of XLSX/CSV files with data extracted from the content database.

A scenario very similar to the one presented in the article is sending files to Azure Blob Storage. It can be implemented by editing the sample script by:

a. replacing $zipFilePath with the connection parameters to Blob Storage:

#Blob connection params
$storageAccountName = "123store321"
$storageAccountKey = "asdf+r/asdfasdS/asdfasasdfasdqVMC2aasdIasdfasdJ6glPAgerqQ+ASt/Pfafwe=="
$containerName = "somefiles"
$blobName = "file.pdf"

b. adding the commands that perform the sending to the end of the script:

##Send file do desired Blob storage
$context = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey
Set-AzStorageBlobContent -File $filePath -Container $containerName -Blob $blobName -Context $context

To work properly, the script modified in this way requires PowerShell Azure to be installed on the application server.