BI xPress Server Features

Auditing Framework

BI xPress Server's Auditing Framework allows you to add, remove, or edit the BI xPress package auditing feature for SSIS 2012 and higher packages stored within Project Deployment Files (ISPACs). The Auditing Framework also provides you with an easy way to obtain rich auditing information from package executions. 

The BI xPress Server Auditing Framework tracks the activity of any SSIS packages where it's applied by capturing useful information such as errors, warnings, runtime details, and more. This detailed information is then stored within the BI xPress repository and saved for analysis using the Monitoring Console.

Feature Highlights

• Record critical performance data about SSIS packages
• Consists of native SSIS components
• Auditing is self-contained in each the package

The Auditing Framework makes the following changes to your selected SSIS Package(s):

• A new BI xPress Connection Manager will be added that points to the BI xPress Database.  
• New configuration(s) or Package / Project Parameter(s) may be created.
• Package level Event Handlers will be created to capture information within the OnError, OnWarning, OnPreExecute and OnPostExecute Events.
• Row Counts will be added after the source adapter and before the target adapter within every Data Flow task. These row counts will be used to track information regarding the extracted and loaded rows.
• Variables are added at package level scope to store row count for each Data Flow.
• Variables are added in the OnPostExecute event handler scope to store certain information regarding each Data Flow's source and target ( Query, TableName, etc.).
• Package layout and task lists are updated whenever the package is saved or closed within BIDS, Visual Studio, or SSDT. This helps to track executions within the Monitoring Console. Layout changes are only tracked if the BI xPress add-in is loaded.

Navigating the Auditing Framework

BI xPress Server's Auditing Framework is divided between up to seven pages. These pages change depending on whether you select to add or remove the Auditing Framework. The following pages are available:

Adding the Auditing Framework

If you select to Add the Auditing Framework on the Actions page, the following pages are available:

Removing the Auditing Framework

If you select to Remove the Auditing Framework on the Actions page, the following pages are available:

Navigate between the available Auditing Framework pages with the Navigation panel, or the page buttons.

Uploading Packages

The Upload page is the starting point for the Auditing Framework. Use the Upload page to upload packages, configuration files, or ISPACs.

Button	Description
	Select Start Over to reset the Auditing Framework, and restart the process of adding or removing the Auditing Framework.
	Select Clean Up to remove erroneous activities and files from the server. Note:  The Clean Up button is only availalbe to users that have the administrators role.

To Upload a Package(s), complete the following steps:

1. Select your package SSIS version from the drop-down list. Note:  It may take a few minutes for BI xPress Server to detect the versions of SSIS that your server connection has installed.

2. Select Select files... to open the directory browser window. Navigate to the desired file, and then select  Open to add the file to the list of files to upload.

3. Set the password for any files that need a password. Repeat this step for each applicable file.

4. Once you've finished selecting the files to upload, select Upload files to begin the upload process.

5. Select the packages where you want to add / remove the Auditing Framework. Select Next or select Actions from the Navigation panel to continue to the Actions page.

Selecting your Action

Use the Action page to add or remove the Auditing Framework from the uploaded ISPACs. Depending on the action you choose, the available pages may change.

Option	Description	Image
Adding the Auditing Framework	Adding the Auditing Framework applies BI xPress Server's Auditing Framework to each selected Package contained within the uploaded ISPACs. You can access the Database, Connection, and Configure page to configure more advanced features present within applying the Auditing Framework.
Removing the Auditing Framework	Removing the Auditing Framework (if able) removes BI xPress Server's Auditing Framework from each selected Package contained within the uploaded ISPACs. You can access the Connection page to configure the Auditing Framework's behavior for the Auditing Framework's connection manager.

Database Connection

Use the Database page to configure the server information that's used by the Auditing Framework to connect to the BI xPress Database. 

The following options are available:

Connection Manager

The Connection page's behavior changes based on your selection from the Action page.

Adding Auditing Framework

When adding the Auditing Framework, the Connection page allows you to select the scope (project or package) and name for the connection manager.

Removing the Auditing Framework

When removing the Auditing Framework, use the Connection page to enter the details regarding the scope (project or package) and name of the BI xPress connection manager that will be removed. If the packages use a project connection manager and parameters, you can select not remove the project connection manager and parameters.

Configure Options

Use the Configure page to fully customize the behavior of the Auditing Framework. 

The following options are available:

Use Configuration

Package Groups

The package grouping helps create and associate packages to groups. Once associated with a group, the package execution panel can filter packages based on groups. Packages can belong to one or more groups. Select add package group to add new groups. Select an existing group's checkbox to include preexisting groups.

Switch	Description	Image
Script Language	Specify which programming language you want the Auditing Framework to use within Script Tasks.
Max Loop Iterations	Specify the number of loops within a For or For Each Loop you want to document. Setting this value to 0 logs all iterations.
Log Warnings	Specify whether to log warnings that are encountered during executions and store them on the BI xPress database.
Real-time Data Flow Monitoring	Specify whether to monitor your package's Data Flows in real-time.

Row Count Logging

The Row Count Logging switch allows you to capture row counts flowing inside your Data Flows. When you enable row count logging, each data flow task is modified to track row count data.

Switch	Description	Image
Connection Logging	Allows you to log every connection manager connection string value during run-time.

Variable Logging

The Variable Logging switch logs variable values during an SSIS execution. If no other options are enabled, variable values are logged two times during package execution: when the package execution starts and when the package execution completes.

Custom Package Variable Logging

The Custom Package Variable Logging switch allows you to explicitly specify the logging of specific variables within the Auditing Framework.

After selecting an action, setting up the database, connection manager, and configuration options, select the add or remove Auditing Framework button to continue to the Progress page. 

Progress Page

The Progress page contains an in-depth list of steps being taken to apply or remove the Auditing Framework from the selected packages. Once Auditing Framework has completed, select View Completed Items to open the Download Packages page.

Download Packages Page

The Download Packages page displays a historical list of each activity the Auditing Framework encountered and allows you to download the ISPAC that was generated as a result of that activity.

If the list of activities spans multiple pages, you can navigate from one page to the next until you find the desired activity. Each historical record contains details on the action taken, progress log, date last updated, status, and the ability to download the ISPAC associated with the activity. 

After downloading the desired ISPAC(s), you can deploy the ISPACs using the SSIS built in Project Deployment Tool.

Performance optimizing N-Tier SSIS Packages to use with the Auditing Framework

When applying the Auditing Framework to packages that are a part of a Parent-Child relationship (one package uses an Execute Package task to execute the other package), you may notice a decrease in performance. Performance decreases worsen when using an N-Tier pattern for SSIS Packages (Master-Child0-Child1-...-ChildN) where all tiers contain the Auditing Framework. The performance decrease is due to the way the SSIS Runtime handles events, and the way they move up to their respected parent package(s).

Another issue with the SSIS Runtime is that when events move up the chain, each parent copies the event and adds it to the list of events that will move up. This issue causes an exponential increase in the number of events needing to be handled. For example, a single event at the lowest level generates eight events at the Master level when executing with four levels.

Follow these steps to design your parent package(s) for optimum performance when using the Auditing Framework:

1. Before applying or re-applying the Auditing Framework, wrap each Execute Package task inside of a Sequence Container.

2.  For each Execute Package Task, go to the task's Event Handlers tab (not the Package Level Event Handlers) and create an empty event handler for OnPreExecute, OnPostExecute, OnWarning, and OnVariableValueChanged.

3. Within each of the empty Execute Package Task's event handlers that were just created in step 2, set the System::Propagate variable to False. This will prevent the event from being broadcast to all parents and logging the event as if the parent package actually generated the event.

4. Save the packages as well as the project, if applicable.

5. Apply the Auditing Framework normally to each package.

Command Line

BI xPress Server also comes with the option to install the Auditing Framework command line tool, which utilizes the AuditingFramework.CommandLine.exe located within the Auditing Framework command line directory of the Workbench Server installation. The Auditing Framework command line tool can be found in the following location by default: 

C:\Program Files (x86)\Pragmatic Works\Workbench Server\Auditing Framework Command Line\AuditingFramework.CommandLine.exe

Any framework (such as Scheduled Tasks, TFS Build Server, Microsoft Systems Center) that is capable of starting an application and passing parameters through a response file or command line arguments will be capable of utilizing the Auditing Framework command line to apply/re-apply or remove the Auditing Framework.

When you use AuditingFramework.CommandLine.exe to apply the Auditing Framework to packages, you can include several switches to specify various options to be included within Auditing Framework.

AuditingFramework.CommandLine.exe [<Action>] {<Items>} <Item Information> [<Options>] [<Configuration Information>] 
[<Connection Information>] [<Custom Variable Logging>]

The Auditing Framework can be used in the traditional sense by directly inputting each desired switch into the Command Prompt, or it can be used to reference a response file that contains all desired switches.

Response File

Switch Arguments

To introduce a parameter argument to the AuditingFramework.CommandLine.exe, use a / (slash) followed immediately by either the parameter name or the parameter abbreviation. If the Parameter requires a value, a : (colon) or an = (equal) will be used to separate the parameter argument with the parameter value and must be surrounded by " (double-quotes) if the value contains a space or special character(s).

Below is a list of all Parameter Arguments grouped by their specific purpose:

Action

Items

Item Information

Add Only Arguments

The following arguments are used alongside the add switch and are ignored if used with the remove switch:

Configuration Information

Connection Information

Custom Variable Logging

Examples

The following example uses a response file that contains all of the necessary switches to apply the auditing framework.

AuditingFramework.CommandLine.exe @"C:\SSIS\AfResponseFile.txt"

The following example adds the Auditing Framework to two SSIS packages located on the File System and uses the BI xPress database named "ProdBIxPress" located on "ProductionServer". In addition, the logging of warnings is turned off.

AuditingFramework.CommandLine.exe /add "C:\SSIS\MasterPackage.dtsx" "C:\SSIS\Staging.dtsx" 
/location:FileSystem /bixDataSource:"ProductionServer" /bixDatabase:"ProdBIxPress" /logWarnings:False

The following example adds the Auditing Framework to one SSIS package located on the File System and uses the BI xPress database named "ProdBIxPress" located on "ProductionServer". In addition, the custom variable "User::MyDateVar1" will be included in the log, however, logging of the custom variable during the OnPreExecute event has been disabled.

AuditingFramework.CommandLine.exe /a "C:\SSIS\MasterPackage.dtsx" /l:FileSystem /bxds:"ProductionServer"
 /bxdb:"ProdBIxPress" /lcv:True /cvdt1:"User::MyDateVar1" /cvope:False

The following example removes the Auditing Framework from two SSIS 2012 packages located on the SQL Server  named "ProductionServer".

AuditingFramework.CommandLine.exe /remove "\SSIS\MasterPackage.dtsx" "\MSDB\SSIS\Staging" /location:SqlServer 
/locationVersion:Sql2012 /dataSource:"ProductionServer"

Notification Engine

The Notification Engine allows you to connect and manage your packages' notification settings directly from within BI xPress Server. Use the Notification Engine to send rich, dynamic messages through E-mail, SMS, SQL, Text Files, Event Logs, and Twitter.

Feature Highlights

• Create highly customizable message templates
• Quickly develop complex notification events to handle a variety of scenarios

The Notification Engine harnesses the power of HTTP Requests to manage the notification events that trigger. Once BI xPress Server receives an HTTP Request, BI xPress Server handles the notification, sending all valid events using the event's corresponding provider(s). 

Configuring the Notification Engine to be ready to receive HTTP Requests and send out notifications is simple. The following options are available:

Templates

Message Templates lend form and reason to the HTTP requests that are received by the Notification Engine. Templates are fully customizable and control what information is included within the notification.

Events

Notification Events manage the HTTP requests that are received by the Notification Engine and handle which Message Template(s) and Provider(s) send the notification based on the parameters from the HTTP request.

Notification Requests

Once the Notification Engine has been configured, you can request notifications through the BI xPress Notification Framework, Command Line, or HTTP Web Request.

Notification Template Advanced Syntax

Notification templates and events use a unique syntax for their notification structure. The syntax is case sensitive, and allows you to send data to the notification engine. Parameters sent alongside the event help change the structure of the notification.

There are two types of markup properties available for use within a message template:

Both the placeholder and block properties provide you with complete control over your notifications.

Placeholders

Most notifications need parameters sent alongside the request to function. Template and events use these parameters within placeholders. These placeholders provide substance to a message.

Syntax

{{ <Placeholder>[ | <Filter>[ : arguments,...n]...n] }}

Arguments

The following Placeholder filters are available:

Append

Adds the parameter to the beginning of the argument.

{{ <Parameter> | append: <String Value> }}

Arguments

Example One

{{ 'First' | append: 'Second'}}

Example Two

{{ InputParameter | append: 'Second'}}

Capitalize

Capitalizes words within the parameter.

Syntax

{{ <Parameter> | capitalize }}

Arguments

Example One

{{ 'one two three four' | capitalize}}

Example Two

{{ InputParameter | capitalize}}

Ceil

Rounds a numerical parameter to the nearest integer.

Syntax

{{ <Parameter> | ceil }}

Arguments

Example One

{{ 3.14 | ceil}}

Example Two

{{ InputParameter | ceil}}

Date

Reformats a date parameter using specified format.

Syntax

{{ <Parameter> | date: <String Value> }}

Arguments

The following is a list of accepted flags:

Example One

{{ '2015-06-25' | date: '%A, %B %d, %Y'}}

Example Two

{{ InputParameter | date: 'The year was %Y, the month was %b, the day was %d 
and the time was %I''}}

Default

Returns the provided value. If the parameter value is empty or not provided, then it will return the given default value.

Syntax

{{ <Parameter> | default: <String Value> }}

Arguments

Example One

{{ InputParameter | default: 'This is a default value'}}

Example Two

{{ InputParameter | default: 'This is a default value'}}

Divided_by

Integer division. Divides the first integer parameter by the second value.

Syntax

{{ <Parameter> | divided_by: <Integer Value> }}

Arguments

Example One

{{ 13 | divided_by: 5}}

Example Two

{{ InputParameter | divided_by: 8}}

Downcase

Converts all letters within the parameter to lowercase

Syntax

{{ <Parameter> | downcase }}

Arguments

Example One

Example Two

{{ 'LOUD NOISES' | downcase: 'This is a default value'}}

Floor

Rounds a numerical parameter down to the nearest integer.

Syntax

{{ <Parameter> | floor }}

Arguments

Example One

{{ 3.14 | floor}}

Example Two

{{ InputParameter | floor}}

Istrip

Removes all whitespace from the beginning of the parameter

Syntax

{{ <Parameter> | lstrip }}

Arguments

Example One

{{ InputParameter | lstrip}}

Example Two

{{ '   These are the voyages of the starship Enterprise.'  | lstrip}}

Minus

Subtraction. Subtracts a numerical parameter with a second value.

Syntax

{{ <Parameter> | minus: <Numerical Value> }}

Arguments

Example One

{{ 13 | minus: 5}}

Example Two

{{ InputParameter | minus: 8}}

Modulo

Remainder. Returns the remainder after dividing the integer parameter by the filter parameter

Syntax

{{ <Parameter> | modulo: <Numerical Value> }}

Arguments

Example One

{{ 13 | modulo: 5}}

Example Two

{{ InputParameter | modulo: 8}}

Pluralize

Returns the second string if the parameter is greater than one. Otherwise returns the first string.

Syntax

{{ <Parameter> | pluralize: <Singular Value>, <Plural Value> }}

Arguments

Example One

{{ InputParameter | pluralize: 'student', 'students'}}

Example Two

{{ 1 | default: 'sale', 'sales'}}

Plus

Addition. Adds a numerical parameter with a second value.

Syntax

{{ <Parameter> | plus: <Numerical Value> }}

Arguments

Example One

{{ 13 | plus: 5}}

Example Two

{{ InputParameter | plus: 8}}

Prepend

Adds the parameter to the end of the argument.

Syntax

{{ <Parameter> | prepend: <String Value> }}

Arguments

Example One

{{ 'First' | prepend: 'Second'}}

Example Two

{{ InputParameter | prepend: 'Second'}}

Remove_first

Removes the first occurrence of the string value found within the parameter.

Syntax

{{ <Parameter> | remove_first: <Search> }}

Arguments

Example One

{{ InputParameter | remove_first: 'REDACTED'}}

Remove

Replaces all occurrence of the string value found within the parameter.

Syntax

{{ <Parameter> | remove: <Search> }}

Arguments

Example One

{{ InputParameter | remove: 'REDACTED'}}

Replace_first

Replaces the first occurrence of the string value found within the parameter with the replacement value.

Syntax

{{ <Parameter> | replace_first: <Search>, <Replacement> }}

Arguments

Example One

{{ InputParameter | replace_first: 'REDACTED', 'censored'}}

Replace

Replaces all occurrence of the string value found within the parameter with the replacement value

Syntax

{{ <Parameter> | replace: <Search>, <Replacement> }}

Arguments

Example One

{{ InputParameter | replace: 'REDACTED', 'censored'}}

Round

Rounds the numerical parameter to either the nearest integer or a specified number of decimals

Syntax

{{ <Parameter> | round: <Integer Value> }}

Arguments

Example One

{{ 3.14 | round: 1}}

Example Two

{{ InputParameter | round: 5}}

Rstrip

Removes all trailing whitespace from the parameter.

Syntax

{{ <Parameter> | rstrip }}

Arguments

Example One

{{ InputParameter | rstrip}}

Example Two

{{ 'These are the voyages of the starship Enterprise.    '  | rstrip}}

Size

Returns the size of an array or string.

Syntax

{{ <Parameter> | size }}

Arguments

Example One

{{ InputParameter | size}}

Example Two

{{ InputParameter | size}}

Slice

Returns a subset of the string parameter based on offset and length.

Syntax

{{ <Parameter> | slice: <Offset>, <Length> }}

Arguments

Example One

{{ InputParameter | slice: 5, 15}}

Example Two

{{ 'This is fifteen' | slice: -7, 7}}

Times

Multiplication. Multiplies two numerical parameters.

Syntax

{{ <Parameter> | times: <Numerical Value> }}

Arguments

Example One

{{ 13 | times: 5}}

Example Two

{{ InputParameter | times: 8}}

Truncate

Returns a specific number of characters from a string.

Syntax

{{ <Parameter> | truncate: <Truncate>[, <Append>] }}

Arguments

Example One

{{ InputParameter | truncate: 5}}

Example Two

{{ 'This is MADNESS!' | truncate: 8, 'SPARTA!'}}

Truncatewords

Returns a specific number of words from a string.

Syntax

{{ <Parameter> | truncatewords: <Truncate>}}

Arguments

Example One

{{ InputParameter | truncatewords: 3}}

Example Two

{{ 'This is fifteen' | truncatewords: 2}}

Upcase

Converts all letters within the parameter to uppercase.

Syntax

{{ <Parameter> | upcase }}

Arguments

Example One

{{ InputParameter | upcase: 'This is a default value'}}

Example Two

{{ 'loud noises' | upcase: 'This is a default value'}}

Tags

Most notifications need parameters sent alongside the request to function. Template and events use  these parameters within Tags. Tags help create blocks of logic within templates.

Syntax

{% <Tag>[ <Conditions>] %}
<Template Block>
{% <EndTag> %}

Arguments

<!-- {% <Tag>[ <Conditions>] %} -->
<Template Block>
<!-- {% <EndTag> %} -->

Case

Like if and unless tags, case tags controls the display of information by choosing between a list of candidates.

Syntax

{% case <Condition> %}
{% when <Match> %}
<Template Block>
{% endcase %}

Arguments

Example

My favorite animal is:{% case InputParameter %}
{% when 'Black' %}
Zebra
{% when 'Blue' %}
Regal Tang
{% when 'Green' %}
Iguana
{% when 'Orange' %}
Fox
{% when 'Yellow' %}
Duck
{% endcase %}

Comment

Comment tags hide the respected block from being outputted within a message.

Syntax

{% comment %}
<Template Block>
{% endcomment %}

Example

One... two... five!{% comment %}Three, sir.{% endcomment %} Three!

Cycle

Cycle tags alternate between a comma-separated list of items.

Syntax

{% cycle [<Group Name>: ] <Item>,...n %}

Arguments

Example One

{% cycle 'Badger', 'Badger', 'Badger', 'Mushroom', 'Mushroom', 'Snake' %}
{% cycle 'Badger', 'Badger', 'Badger', 'Mushroom', 'Mushroom', 'Snake' %}
{% cycle 'Badger', 'Badger', 'Badger', 'Mushroom', 'Mushroom', 'Snake' %}
{% cycle 'Badger', 'Badger', 'Badger', 'Mushroom', 'Mushroom', 'Snake' %}
{% cycle 'Badger', 'Badger', 'Badger', 'Mushroom', 'Mushroom', 'Snake' %}
{% cycle 'Badger', 'Badger', 'Badger', 'Mushroom', 'Mushroom', 'Snake' %}
{% cycle 'Badger', 'Badger', 'Badger', 'Mushroom', 'Mushroom', 'Snake' %}

Example Two

{% cycle 'Numbers': '1', '2', '3', '4', '5' %}) 
{% cycle 'Sports': 'Soccer', 'Football', 'Rugby', 'Baseball' %}
{% cycle 'Numbers': '1', '2', '3', '4', '5' %}) 
{% cycle 'Sports': 'Soccer', 'Football', 'Rugby', 'Baseball' %}
{% cycle 'Numbers': '1', '2', '3', '4', '5' %}) 
{% cycle 'Sports': 'Soccer', 'Football', 'Rugby', 'Baseball' %}
{% cycle 'Numbers': '1', '2', '3', '4', '5' %}) 
{% cycle 'Sports': 'Soccer', 'Football', 'Rugby', 'Baseball' %}
{% cycle 'Numbers': '1', '2', '3', '4', '5' %}) 
{% cycle 'Sports': 'Soccer', 'Football', 'Rugby', 'Baseball' %}
{% cycle 'Numbers': '1', '2', '3', '4', '5' %}) 
{% cycle 'Sports': 'Soccer', 'Football', 'Rugby', 'Baseball' %}

For

When using array parameters, for tags allow users the ability to loop through a collection.

Syntax

{% for <Item> in <Array>[ Options] %}
<Template Block>
{% endfor %}

Arguments

<Item>.<PropertyName>

<Item>['<Property Name>']

Helper Tags

Within a for template block, there are handful of optional tags available:

• break
• continue
• else

Helper Variables

Within a for template block, there are handful of optional arguments available:

Example

{% for error in InputParameters %}
Source - {{ error.Source }}
Time - {{ error.Time }}
Details - {{ error['Error Details'] }}
{% endfor %}

Break

Break tags immediately exits a for loop's template block.

Syntax

{% for <Item> in <Array>[ Options] %}
<Template Block>
{% break %}
<Template Block>
{% endfor %}

Example

{% for error in InputParameters %}
{% if forloop.index == 2 %}
{% break %}
{% endif %}
Source - {{ error.Source }}
Time - {{ error.Time }}
Details - {{ error['Error Details'] }}
{% endfor %}

Continue

Continue tags immediately ends the current iteration and moves to the next element within the array.

Syntax

{% for <Item> in <Array>[ Options] %}
<Template Block>
{% continue %}
<Template Block>
{% endfor %}

Example

{% for error in InputParameters %}
{% if forloop.index == 2 %}
{% continue %}
{% endif %}
Source - {{ error.Source }}
Time - {{ error.Time }}
Details - {{ error['Error Details'] }}
{% endfor %}

Else

Located at the end of a for loop, else tags display a template block only if there are no items within the array.

Syntax

{% for <Item> in <Array>[ Options] %}
<Template Block>
{% else %}
<Template Block>
{% endfor %}

Example

{% for error in InputParameters %}
Source - {{ error.Source }}
Time - {{ error.Time }}
Details - {{ error['Error Details'] }}
{% else %}
No errors!
{% endfor %}

If

Like case and unless tags, if tags controls the display of information based on a condition

Syntax

{% if <Conditions> %}
<Template Block>
{% endif %}

Arguments

Boolean Operators

These operators combine conditions together:

Comparison Operators

These operators typically compare parameters with a set value:

Helper Tag

Within an if template block, there are handful of optional tags available:

• elseif
• else

Example

My favorite animal is:{% if InputParameter == 'Orange' %}
Fox
{% endif %}

Elseif

Located withinend of an if loop, else tags display a template block when the condition returns false. There can be multiple elseif tags within an if tag.

Syntax

{% if <Conditions> %}
<Template Block>
{% elseif <Conditions> %}
<Template Block>
{% endif %}

Example

I like {% if InputParameter < 1 %}
no
{% elseif InputParameter == 1 %}
one animal
{% elseif InputParameter > 1 and InputParameter <= 4 %}
a couple animals
{% elseif InputParameter > 4 and InputParameter <= 6 %}
a handful of different animals
{% elseif InputParameter > 6 and InputParameter <= 9 %}
several animals
{% else %}
a lot of animals
{% endif %}!

Else

Located at the end of an if loop, else tags display a template block when the condition returns false. There can be only one else tag within an if tag.

Syntax

{% if <Conditions> %}
<Template Block>
{% else %}
<Template Block>
{% endif %}

Example

My favorite animal is:{% if InputParameter == 'Orange' %}
Fox
{% else %}
Iguana
{% endif %}

Raw

Raw tags temporarily disables syntax processing. This allows users to generate content that would normally conflict with syntax.

Syntax

{% raw %}
<Template Block>
{% endraw %}

Example

{% raw %}
I really need to use the mustache handlebar for my marvelously mustached smiley faces: :-{
{% endraw %}

Unless

Like case and if tags, unless tags controls the display of information based on a condition. Unlike if tags, an unless tag displays the template block only if the condition returns false. Unless tags cannot use elseif and else tags within them.

Syntax

{% unless <Conditions> %}
<Template Block>
{% endunless %}

Arguments

Boolean Operators

These operators combine conditions together:

Comparison Operators

These operators typically compare parameters with a set value:

Example

My favorite animal is:{% unless InputParameter == 'Orange' %}
Not a fox
{% endunless %}

Templates

Templates lend form and reason to Web requests. Each template controls the content used within a notification event. Events supply templates with parameters used within placeholders and tags. In return, the template translates these parameters into readable messages. Templates includes a powerful syntax engine that provides further control on how messages appear.

<pre>{{ <Placeholder>[ | <Filter>[ : arguments,...n]...n] }}</pre>

Notification Events

Notification Events are used to manage the notification requests that are received by the Notification Engine. Notification Events handle which Message Template(s) and Provider(s) send the notification based on the parameters sent by the HTTP request.

Important:  Notification events need at least one template before becoming available.

New-EventLog -Source "BI xPress" -LogName "Application"

Each Provider has additional properties that need to be set for the Notification to be configured properly.

Notification Requests

Before sending notifications, at least one message template and notification event needs to be configured. After you've configured one event, there are three methods that allow you to send notifications:

• You can inject the notification engine into SSIS Packages using the BI xPress Framework.
• You can call the notification engine using the BI xPress Server's Command Line tool.
• You can send HTTP Web Requests to the server

Notification Request Command Line

The BI xPress Server's Notification Engine command line tool allows you to send rich notifications through their BI xPress Server. The BI xPress Server Notification Engine command line tool utilizes the HttpEngine.exe located within the Notification Engine Command Line directory. This tool is located at the following location by default: 

C:\Program Files (x86)\Pragmatic Works\Workbench Server\Notification Engine Command Line\HttpEngine.exe

Any framework (such as Scheduled Tasks, TFS Build Server, Microsoft Systems Center) that is capable of starting an application and passing parameters using command line arguments is capable of utilizing the the BI xPress Notification Engine command line tool to send rich notifications from the BI xPress Server.

HttpEngine.exe [Help] {<Operation>} {<Information>} [<Parameters>]

Switch Arguments

To introduce a parameter argument to the HttpEngine.exe, use either a -- followed immediately by the parameter switch or - followed by the short form version of the parameter switch. If the switch requires a value, an = (equal) will be used to separate the parameter argument with the parameter value and must be surrounded by " (double-quotes) if the value contains a space or special character(s).

The following Parameter Arguments are available:

Help

Operation

Information

Parameters

Examples

The following example sends all notifications with the event name "SSISNotification" and does not pass any parameters to that notification event:

HttpEngine.exe "sendNotifications" -eventName "SSISNotification"

The following example sends the notification with the Notification ID of "{edbc4104-126f-4646-8030-36e69aa3b892}" and passes the value "Completed" to the parameter named "Status":

HttpEngine.exe "sendNotification" -NotificationId "{edbc4104-126f-4646-8030-36e69aa3b892}" -Status "Completed"

The following example sends all notifications with the event name "SSISNotification" and and sets the parameter named Status to "Completed", the parameter named PackageName to "ETLPackage.dtsx" and the parameter named Framework to "2015.3.7":

HttpEngine.exe "sendNotifications" -eventName "SSISNotification" -Status "Completed" 
-PackageName "ETLPackage.dtsx" -Framework "2015.3.7"

Notification Web Request

The BI xPress Server's Notification Engine allows you to send rich notifications to your BI xPress Server using HTTP requests. 

SendNotifications Request

POST http[s]://<serverName>[:<port>]/api/BIxPress/NotificationEngine/SendNotifications

{
"eventName" : "event-name",
"notificationSeverity" : "severity",
"parameters" :
[
{"name" : "parameter-name", "value" : "parameter-value"}
]
}

SendNotification Request

POST http[s]://<serverName>[:<port>]/api/BIxPress/NotificationEngine/SendNotification

{
"notificationId" : "notification-id",
"notificationSeverity" : "severity",
"parameters" :
[
{"name" : "parameter-name", "value" : "parameter-value"}
]
}

Request Containing Array Parameters

POST http[s]://<serverName>[:<port>]/api/BIxPress/NotificationEngine/SendNotifications

{
"eventName" : "event-name",
"notificationSeverity" : "severity",
"parameters" :
[
{"name" : "parameter-name", "values" :
[
{"name" : "parameter-name", "value" : "parameter-value"}
]
}
]
}

Information

Parameters

{ "name" : 
"parameter-name", 
"value" : 
"parameter-value" }

Examples

The following example posts a SendNotifications request to the BI xPress Server located on localhost to all notifications with the event name "SSISNotification" and does not specify a severity or pass any parameters.

{
"eventName" : "SSISNotification"
}

The following example posts a SendNotifications request to the BI xPress Server located on DEV-BIX-SRV:9090 to all notifications with the event name "SSISNotification" and a priority less than "medium". It also sets the parameters named Status, PackageName and Framework to "Completed", "ETLPackage.dtsx" and "2015.3.7".

{
"eventName" : "SSISNotification",
"notificationSeverity" : "medium",
"parameters" :
[
{"name" : "Status", "value" : "Completed"},
{"name" : "PackageName", "value" : "ETLPackage.dtsx"},
{"name" : "Framework", "value" : "2015.3.7"}
]
}

The following example posts a SendNotification request to the BI xPress Server located on DEV-BIX-SRV:9090 to send the notification with the notification id of  "{edbc4104-126f-4646-8030-36e69aa3b892}" and a priority less than "catastrophic". It also sets the parameters named Status to "Failed".

{
"notificationId" : "{edbc4104-126f-4646-8030-36e69aa3b892}",
"notificationSeverity" : "catastrophic",
"parameters" :
[
{"name" : "Status", "value" : "Failed"}
]
}

The following example posts a SendNotifications request to the BI xPress Server located on DEV-BIX-SRV:9090 to all notifications with the event name "SSISNotification" and a priority less than "medium". It also sets the parameters named Status, PackageName and Framework to "Completed", "ETLPackage.dtsx" and "2015.3.7". It also includes a parameter array named "Errors" with each error containing several parameter.

{
"eventName" : "SSISNotification",
"notificationSeverity" : "medium",
"parameters" :
[
{"name" : "Status", "value" : "Completed"},
{"name" : "PackageName", "value" : "ETLPackage.dtsx"},
{"name" : "Framework", "value" : "2015.3.7"},
{ "name": "Errors", "values":
[
{ "name": "Error 0", "values":
[
{ "name": "Source", "value": "Error Test0" },
{ "name": "Time", "value": "2016/05/25 13:45:33"},
{ "name": "Details", "value": "Error Test0" }
]},
{ "name": "Error 1", "values":
[
{ "name": "Source", "value": "Error Test1" },
{ "name": "Time", "value": "2016/05/25 13:46:07"},
{ "name": "Details", "value": "Error Test1" }
]}
]}
]
}

SSIS Dashboards

SSIS Monitoring

BI xPress Server's Monitoring Console gives you web browser access to real time monitoring of all SSIS packages that have the Auditing Framework. Normally you can only get this information by debugging packages within the development environment.

Feature Highlights

• Watch packages as they execute in real time

BI xPress Server's Monitoring console is divided into five main panels: 

1. Package Execution: Provides a quick summary of the most recent executions and control over which execution currently displays.
2. Execution Diagram: Provides a visual representation of the control and data flow.
3. Errors and Warnings: See table below
4. Variables: See table below
5. Connection Panels: See table below

Panel	Description	Image
Errors and Warnings	The errors and warnings panel provides details of all errors and warnings thrown by the currently selected package, making it easier to troubleshoot problem packages.
Variables	The variables panel displays a time line of all changes to user variables within the currently selected package.
Connections	The connections panel provides details of all present connection managers within the currently selected package.

Package Execution Panel

The Monitoring Console's package execution panel displays a listed summary of the most recent executions within the BI xPress Database. The package execution panel provides you with three main functions: execution name, execution summary, and execution selection.

Option	Description
Package Executions Header	Contains several options for adjusting the displaying of package executions: Button Description Collapses all package groups when grouping is enabled. Toggles between organizing the package execution panel by groups or by package. Contains settings for filtering executions based on a specific set of rules.
Execution Name	Located in the upper left hand corner of each execution, this displays the name of the package that was executed.
Execution Summary	Each execution displays a visual and textual summary for the specific execution: Stat Description Status The background color of each execution visually describes the current status for the package: Red - Package completed with at least one error Green - Package completed successfully without errors Yellow - Package is still running Blue - Package has been stopped during execution Date and Runtime The date located on the left of the execution describes when this execution occurred, while the time located on the right represents how long the package executed before completing.
Execution Selection	Displays the name and status of executions, and controls which execution display within the other Execution Diagram.

Execution Diagram Panel

The Monitoring Console's execution diagram panel is the main view used to monitor the execution for a package. The execution diagram panel is divided up into three sections: Header, toolbar, and diagram.

Header

The header, located at the top, and spanning the entire diagram panel, displays a visual and textual summary for the specific execution. The following options are available:

Toolbar

The toolbar controls what currently displays within the diagram.

Diagram

Displays the execution details for the control or data flow in a way familiar with SSIS developers. By default, the execution diagram displays the control flow.

Navigation Buttons

Use the buttons in the upper right of the diagram to navigate the diagram panel.

Button	Description
	Zooms out to display more of the execution diagram.
	Zooms in to show less of the execution diagram.
	Resets the zoom to the default zoom level.

SSRS Monitoring

Use the Report Monitoring Console to monitor and troubleshoot your reports. The report console displays critical performance statistics, and helps to display performance issues and the overall health of your report server. Statistics such as memory usage, data and number of rows transferred display and have programmable thresholds.

Feature Highlights

• A high level overview of a report server
• Displays stats such as average runtime, average processing time, number of executions, amount of data transferred, and memory usage
• Shows the percentage of runtime that is consumed by processing a report

Before launching the Reporting Monitor Console, you need to enter the connection information for the SSRS database that you want to monitor. Open the BI xPress Server Feature settings page to configure this connection settings.

Open the Feature Settings page, and enter your connection information with SQL Server Authentication. Select Save to save the settings.

Report Averages

The Report Averages panel displays the status of all executing reports present within your Reporting Services instance. The number of reports can be adjusted from within the Feature Settings page using the SSRS Monitoring Console - Most Frequent Reports Count property. Select the maximize report button to expand the panel.

Frequent Reports

Use the Frequent Reports panel to determine which reports are executing the most often. Reports that execute more than expected can cause the entire Reporting Services instance to run slow, leading to performance and scalability issues. The number of reports can be adjusted from within the Feature Settings page using the SSRS Monitoring Console - Most Frequent Reports Count property. Select the maximize report button to expand the panel.

Active Users

Use the Active Users panel to view a list of all users that have connected to the report server and the number of times the user has executed reports. The number of users can be adjusted from within the Feature Settings page using the SSRS Monitoring Console - Most Active Users Count property. Select the maximize report button to expand the panel.

BI xPress Server System Dashboard

The System Dashboard is the home screen when accessing Workbench Server. This Dashboard can be customized to give you a quick glimpse of important information, all in one convenient spot. Select an action icon on the dashboard pane to perform any of the following actions:

Note:  You can rearrange the order of the tiles by dragging and dropping within the page.

System Dashboard Settings

Use the System Dashboard Setting page to create, edit, and delete tiles to be used in the System Dashboard. To add a tile, select the Select tile to add to your dashboard... drop down and then select from the following options:

• Successful Packages
• Warning Packages
• Failed Packages
• Execution Count

Select + Add Tile and to load the corresponding configuration page.

Successful Packages

In the Add New Tile - Successful Packages page, you can configure the following:

Warning Packages

In the Add New Tile - Warning Packages page, you can configure the following:

Failed Packages

In the Add New Tile - Failed Packages page, you can configure the following:

Execution Count

In the Add New Tile - Execution Count page, you can configure the following: