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

Important:  If you are attempting to apply or remove the Auditing Framework from an SSIS Package that contains Third-Party Component(s), you need to install the Third-Party components on the machine where the BI xPress Server Auditing Framework is installed.  

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

BI xPress Server Auditing Framework diagram example

Note:  These changes are based on the options you've selected during the application of the Auditing Framework.

  • 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.

Important:  Altering any of the task names or descriptions created by BI xPress causes the Auditing Framework to not recognize any previously created tasks.

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:

PageDescription
Upload Use the Upload page to upload Integration Services Project Deployment files (ISPACs) to the BI xPress Server and add or remove the Auditing Framework from the uploaded packages.
ActionUse 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.
ProgressThe Progress page displays information, warnings, and errors that may occur during the application or removal of the Auditing Framework.
DownloadUse the Download page to download a re-compiled ISPAC that contains all of the packages with the addition or removal of the Auditing Framework.

Adding the Auditing Framework

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

PageDescription
DatabaseUse the Database page allows to configure your server information that's used to connect to the BI xPress Database.
ConnectionWhen adding the Auditing Framework, use the Connection to choose the scope (project or package) and name for the connection manager.
ConfigureUse the Configure page to configure in-depth options to fully customize the Auditing Framework.

Removing the Auditing Framework

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

PageDescription
ConnectionWhen removing the Auditing Framework, use the Connection page to choose the scope (project or package) and enter the name of the BI xPress connection manager that will be removed. If the packages use a project connection manager and parameters, it also provides you with the ability to not remove the project connection manager and parameters.

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

BI xPress Server Auditing Framework Navigation panel
BI xPress Server Auditing Framework Navigation 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.

BI xPress Server Auditing Framework Upload

ButtonDescription
BI xPress Server Auditing Framework Upload start over buttonSelect Start Over to reset the Auditing Framework, and restart the process of adding or removing the Auditing Framework.
BI xPress Server Auditing Framework Upload clean up buttonSelect 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.

Note:  BI xPress Server considers erroneous activities to be activities associated with missing files. Erroneous files are files located within the upload directory that are not associated with an activity.

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.

BI xPress Server Auditing Framework Upload Detecting available SSIS versions

Important:  BI xPress Server requires all versions of SSIS that you want to use installed on the server. If you upload files that are from a version that's not on the server, the following error occurs:

BI xPress Server Auditing Framework Upload error

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.

BI xPress Server Auditing Framework Upload Select Files

Note:  BI xPress Server cannot access SSIS files set using the Encrypt all with user key protection level. Files set to Encrypt sensitive with User Key may experience unintended consequences. SSIS will associate these files to the user configured to run BI xPress Server's Application Pool's user key.

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

BI xPress Server Auditing Framework Upload Set file password

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

BI xPress Server Auditing Framework 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.

OptionDescriptionImage
Adding the Auditing FrameworkAdding 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.BI xPress Server Auditing Framework Add Auditing Framework
Removing the Auditing FrameworkRemoving 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.BI xPress Server Auditing Framework Remove Auditing Framework

Database Connection

BI xPress Server Auditing Framework Database Connection page

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

Note:  This page is only visible if the user is adding the Auditing Framework to packages.

The following options are available:

OptionDescription
ServerEnter the name of the Server where the BI xPress database is located.
DatabaseEnter the name of the database that contains the BI xPress Framework Architecture.
Database AuthenticationSelect between Windows Authentication or SQL Server Authentication.
UsernameIf you select SQL Server Authentication, enter your SQL Server username that's used to connect to the BI xPress database.
PasswordIf you selects SQL Server Authentication, enter your Password that's used to connect to the BI xPress database.
Offline ModeSelect the checkbox to create the Auditing Framework Connect Manager and set it to Work Offline allowing you to browse and continue developing your package when the BI xPress database is not yet accessible.
Continue package execution on Auditing Framework database connection failureSelect this checkbox to allow SSIS packages that contain the Auditing Framework to continue executing in the rare occurrence that the Auditing Framework fails.
Do not report failure if Auditing Framework failsSelect this checkbox to hide all errors that occur due to the Auditing Framework.

Connection Manager

BI xPress Server Auditing Framework Connection Manager page

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.

OptionDescription
Connection ScopeSelect whether you want the BI xPress Connection manager to be saved as a Project or Package Connection Manager.
Connection Manager NameSpecify a meaningful name for your BI xPress 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.

OptionDescription
Remove project level connection manager and parametersExplicitly specify not to remove the BI xPress Project Connection Manager. This is important if you are not removing the Auditing Framework from all of the packages within an ISPAC.
Project ScopeIf you specify to remove connection managers and parameters, the Project Scope switch specifies whether Project or Package Connection Managers were used when initially applying the Auditing Framework to these packages.
Connection Manager NameSpecify the name of the Connection Manager that will be removed from each package.

Configure Options

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

Note:  This page is only visible if you are adding the Auditing Framework to packages.

The following options are available:

Use Configuration

BI xPress Server Auditing Framework Use Configuration

OptionDescription
Use ConfigurationSelect the checkbox to begin selecting configuration options.
Configure ScopeSelect to save the parameters as Project or Package parameters. Project parameters control the behavior of the auditing framework for all packages within the ISPAC. Package parameters only control the behavior of the auditing framework for the packages on an individual basis.
Add parameter to enable or disable the Auditing FrameworkAllow users to manually turn off the Auditing Framework.
Add parameter to enable or disable variable, parameter, and connection loggingAllows users to manually turn off the logging of variable, parameter, and connection managers.
Add parameter to enable or disable real-time Data Flow monitoringAllows users to manually turn off the Auditing Framework's real-time Data Flow monitoring.
Add parameter to enable or disable logging of all warningsMinimizes the amount of logging that occurs by not logging warnings that occur during execution.
Add parameter to specify variable and connection log filteringAllows users to manually mask sensitive values contained within variables from displaying when executions are viewed within the Monitoring Console.

Package Groups

BI xPress Server Auditing Framework 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.

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

Note:  If real-time Data Flow monitoring is turned off, some of the features of Monitoring Console's Execution Diagram will be unavailable.

Row Count Logging

BI xPress Server Auditing Framework 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.

SwitchDescription
Source Row Count LoggingIf row count logging is enabled, the Source Row Count Logging switch tracks the number of rows coming from source components.
Destination Row Count LoggingIf row count logging is enabled, the Destination Row Count Logging switch tracks the number of rows going to destination components.
Source SQL Statement LoggingIf row count logging is enabled, the Source SQL Statement Logging switch logs SQL Statements used to extract data. This information can be viewed using BI xPress' Extract / Load Detail Report found within the SentryOne Workbench.
Connection Detail LoggingIf row count logging is enabled, the Connection Detail Logging switch logs details for each source and destination component connections.
SwitchDescriptionImage
Connection LoggingAllows you to log every connection manager connection string value during run-time.BI xPress Server Auditing Framework Connection Logging

Variable Logging

BI xPress Server Auditing Framework 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.

SwitchDescription
Object Variable LoggingIf variable logging is enabled, the Object Variable Logging switch logs object variables.
Initial Parameter Value LoggingIf variable logging is enabled, the Initial Parameter Value Logging option logs the initial values of parameters.
Variable Logging TypeIf variable logging is enabled, the Variable Logging Type logs all variables, or only variables used within For Each loops.

Custom Package Variable Logging

BI xPress Server Auditing Framework Custom Package Variable Logging

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

OptionDescription
Numeric VariablesIf custom package variable logging is enabled, the Numeric Variable text boxes allow you to log up to three custom variables of a numeric data type.
String VariablesIf custom package variable logging is enabled, the String Variable text boxes allow you to log up to three custom variables of the string data type.
Date VariablesIf custom package variable logging is enabled, the Date Variable text boxes allow you to log up to three custom variables of a date data type.
Events to LogIf custom package variable logging is enabled, the Event Logs check boxes allows you to specify the event(s) you want to log for custom variables:
OnPreExecute (Start) When enabled, all custom variables will be logged at the start of the selected logging scope(s).
OnPostExecute (End)When enabled, all custom variables will be logged at the end of the selected logging scope(s).
OnErrorWhen enabled, all custom variables will be logged when an error occurs.
 OnWarningWhen enabled, all custom variables will be logged when a warning occurs.
Custom Logging Scope(s)If custom package variable logging is enabled, the Custom Logging Scope(s) check boxes allows you to specify the scope of custom logging:
Package Level When enabled, custom variables will be logged at the Package Level for the events specified in the Events to Log check boxes.
Task LevelWhen enabled, custom variables will be logged for every task using for the events specified in the Events to Log check boxes.

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. 

Note:  The add or remove Auditing Framework button that's visible is dependent on the action you've selected.

Progress Page

BI xPress Server Auditing Framework 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

BI xPress Server Auditing Framework 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.

Important:  You can also elect to create an empty event handler for the OnError event if the user does not wish for the Parent Package(s) to be notified of an error within the child package.

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.

Warning:  The advantage of this approach is that each package logs exactly what it generates and does not include any Auditing Framework details that belong to one of its child packages. This also lets you see all the details within the Monitoring Console, even if the package(s) were executed as a subset of the entire chain of packages.

The disadvantage of this approach is that because System::Propagate is set to False, we are hiding the events of each Execute Package Tasks. This prevents the Monitoring Console from being able to view the Execute Package Task start and end time and displays them as though they were not executed at all. The Sequence Container displays its status normally. This may be a small price to pay for an exponential increase in package performance.

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

SwitchDescription
@fileInsert all command-line switches to be executed against the Auditing Framework command line tool from a text file. The response file location must be surrounded by " (double-quotes) if it contains a space or special characters.

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

SwitchShorthandDescription
/add/aThis action adds the Auditing Framework to the specified item(s). If no Action switch is specified, AuditingFramework.CommandLine.exe  uses the add switch by default.
/remove/rThis action removes the Auditing Framework from the specified item(s).

Items

SwitchShorthandDescription
[/item:]values[/i:]valuesThe fully qualified location(s) of the package(s), project(s), or solution(s). This must include the full package name including the file extension if the location is FileSystem.  If the location parameter is SqlServer or SsisStore, this location must include the full path to a package and start with a backslash "\".

If specifying multiple targets, all items must be from the same location (FileSystem, SqlServer, or SsisStore) and use spaces in between values, as the following example shows:

/item:"\SSIS\MasterPackage" "\SSIS\ETLPackage"

Note:  SQL 2005 packages must have the auditing framework applied or removed separate from SQL 2012 or SQL 2014 packages

/itemPassword:value/ip:valueThe password for the SSIS project or package(s) that are being modified. No password is applied to the item(s) by default. If a password is present, this password is applied to each package.

Item Information

SwitchShorthandDescription
/location:value/l:value

The location to all specified SSIS Item(s) referenced by the item switch. Users can specify the following values:

FileSystem. SqlServer. Setting the location switch to SqlServer requires the locationVersion switch to be set. SsisStore. Setting the location switch to SsisStore requires the locationVersion switch to be set. By default, only administrators have access to the SSIS Package Store and the command prompt used to execute this process must be running with administrative privileges.

/location Version:value/lver:valueRequired when setting the location switch to either SQL Server or SsisStore. The locationVersion switch directs the application to use a specific SSIS runtime to process the request. Users can specify the following values:
  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016
  • SQL Server 2017
/dataSource:value/ds:valueRequired when setting the location switch to either SQL Server or SsisStore. The dataSource switch directs the application to the server name (and instance name if using SQL ServerServer) where the SSIS items are located.
/userName:value/u:valueIf provided when setting the location switch to either SQL Server or SsisStore, the userName switch (alongside the password switch) sets Integrated Authentication to False and specifies the user name to be used in order to connect.
/password:value/p:valueIf provided when setting the location switch to either SQL Server or SsisStore, the password switch (alongside the userName switch) sets Integrated Authentication to False and specifies the password to be used in order to connect.

Add Only Arguments

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

SwitchShorthandDescription
/scriptLanguage:value/sl:valueSpecifies the script language that will be used when applying the Auditing Framework. The default value for SSIS 2008 and later items is CSharp. This switch will be ignored for SSIS 2005 items. Users can specify the following values:
  • CSharp
  • VB
/logRowCount:value/lrc:valueEnables row count logging within Data Flows. The default value is True. Users can specify the following values:
  • True
  • False
/logRow CountSource:value/lrcs:valueEnables row count logging for source components found within Data Flows. The logRowCountSource switch will be ignored if the logRowCount switch is set to False. The default value is True. Users can specify the following values:
  • True
  • False
/logRowCount Destination:value/lrcd:valueEnables row count logging for destination components found within Data Flows. The logRowCountDestination switch will be ignored if the logRowCount switch is set to False. The default value is True. Users can specify the following values:
  • True
  • False
/logSqlStatements: value/lsql:valueEnables logging of SQL Statements. The default value is True. Users can specify the following values:
  • True
  • False
/logConnectionStrings: value/lconn:valueEnables logging of connection strings. The default value is True. Users can specify the following values:
  • True
  • False
/logWarnings:value/lw:valueEnables logging of warnings issued during package execution. The default value is True. Users can specify the following values:
  • True
  • False
/logVariables:value/lv:valueEnables logging of variable value changes during package execution. The default value is True. Users can specify the following values:
  • True
  • False
/logVariableType:value/lvt:valueEnables logging of all variable value changes or only the variables used within ForEach tasks during package execution. The logVariableType switch will be ignored if the logVariables switch is set to False. The default value is All. Users can specify the following values:
  • All
  • ForEach
/logInitialValues:value/liv:valueEnables logging of initial variable values at the time of applying the Auditing Framework. The logInitialValues switch will be ignored if the logVariables switch is set to False. The default value is True. Users can specify the following values:
  • True
  • False
/logObjectVariables: value/lov:valueEnables logging of variables with an Object data type. The logObjectVariables switch will be ignored if the logVariables switch is set to False. The default value is True. Users can specify the following values:
  • True
  • False
/logTasks:value/lt:valueEnables logging of each task's start and stop times, which is essential for seeing package execution progress within the Monitoring Console. The default value is True. Users can specify the following values:
  • True
  • False
/logTaskType:value/ltt:valueEnables logging of each task's type and parent object. The default value is True. Users can specify the following values:
  • True
  • False
/continueOnAfDb Failure:value/coadf:valueEnables the continuation of package execution if there is an error connecting to the BI xPress database. The default value is False. Users can specify the following values:
  • True
  • False
/doNotReportAf Failure:value/dnarf: valueEnables the silencing of Auditing Framework failure messages in the Auditing Framework execution history. The default value is False. Users can specify the following values:
  • True
  • False
/maxIterations:number/mi:number

Specifies the maximum number of iterations to log within a For or Foreach loops. A value of 0 indicates that all iterations should be logged and only numbers greater than or equal to 0 are allowed.  The default value is 0. 

The following example instructs AuditingFramework.CommandLine to only log the first 5 iterations within For and Foreach Loops:

AuditingFramework.CommandLine.exe "C:\SSIS\MasterPackage.dtsx" /l:FileSystem /maxIterations:5

/logDataFlows:value/ldf:valueEnables the logging of Data Flows. The default value is True. Users can specify the following values:
  • True
  • False
/logConnsFor SrcDest:value/lcsd:valueEnables the logging of connection details for source and destination components within Data Flows. The logConnsForSrcDest switch will be ignored if the logDataFlow switch is set to False. The default value is True. Users can specify the following values:
  • True
  • False

Configuration Information

SwitchShorthandDescription
/configType:value/ct:valueEnables the use of configuration or parameters for externally setting Auditing Framework variables and property values. The use of Package and Project Parameters requires the SSIS Items to be SSIS 2012 or later. In addition, the use of Project Parameters require the SSIS Items to be SSIS project files. The default value is None. Users can specify the following values:
  • None
  • ConfigurationFile
  • PackageParameters
  • ProjectParameters
/configOverwrite:value/co:valueEnables overwriting the configuration or parameters if they already exist. The default value is True. Users can specify the following values:
  • True
  • False
/configPath:value/cp:valueWhen setting the configType switch to ConfigurationFile, the configPath switch directs the application to the directory location to place the configuration file. The directory must exist during application of the Auditing Framework. The configPath switch will be ignored if the configType switch is set to None, PackageParameters, or ProjectParameters. The default value is "C:\SSIS".
/configProjConnMgr Overwrite:value/cpcmo:valueEnables removing and replacing a package-level connection manager with a project-level connection manager when a connection manager with the same name exists in a package. The configProjConnMgrOverwrite switch will be ignored if the configType switch is set to None, ConfigurationFile, or PackageParameters. The default value is True. Users can specify the following values:
  • True
  • False
/configAudit Framework:value/ca:valueEnables the inclusion of a configuration entry controlling the ability to enable and disable the Auditing Framework. The configAuditFramework switch will be ignored if the configType switch is set to None. The default value is True. Users can specify the following values:
  • True
  • False
/configDataFlows:value/cdf:valueEnables the inclusion of a configuration entry controlling the ability to enable and disable Data Flow Row Count logging and Source Information logging. The configDataFlows switch will be ignored if the configType switch is set to None. The default value is True. Users can specify the following values:
  • True
  • False
/configWarnings:value/cw:valueEnables the inclusion of a configuration entry controlling the ability to enable and disable the logging of SSIS Warnings. The configWarnings switch will be ignored if the configType switch is set to None. The default value is True. Users can specify the following values:
  • True
  • False
/configVarsParams Conns:value/cvpc:valueEnables the inclusion of a configuration entry controlling the ability to enable and disable the logging of connection strings and variable logging. The configVarsParamsConns switch will be ignored if the configType switch is set to None. The default value is True. Users can specify the following values:
  • True
  • False
/configVarsConns Filtering:value/cvcf:valueEnables the inclusion of a configuration entry controlling the ability to mask specific connection managers, variables, and parameters. The configVarsConnsFiltering switch will be ignored if the configType switch is set to None. The default value is True. Users can specify the following values:
  • True
  • False

Connection Information

SwitchShorthandDescription
/bixExistingConnection: value/bxec:valueSpecifies whether an existing connection manager will be used or if the a new connection manager will be created when applying the Auditing Framework. The default value is False. Users can specify the following values:
  • True
  • False
/bixConnName:value/bxcn:valueSpecifies the name of the connection manager to be used by the Auditing Framework. If bixExistingConnection switch is set to False and a connection manager already exists with the same name, the existing connection manager will be used instead. If bixExistingConnection switch is set to True, the connection manager name is case-sensitive and must be entered exactly as it appears. The default value is "OLEDB_BIxPress".
/bixDataSource:value/bxds:valueSpecifies the name of the server hosting the BI xPress database (and instance name if applicable). This is the same database that the Monitoring Console will use to display package execution information.
/bixDatabase:value/bxdb:valueSpecifies the name of the database where the Auditing Framework data will be stored. This is the same database that the Monitoring Console will use to display package execution information.
/bixUserName:value/bxu:valueIf provided, the bixUserName switch (alongside the bixPassword switch) sets Integrated Authentication to False and specifies the user name to be used in order to connect to the BI xPress database.
/bixPassword:value/bxp:valueIf provided, the bixPassword switch (alongside the bixUserName switch) sets Integrated Authentication to False and specifies the user name to be used in order to connect to the BI xPress database.

Custom Variable Logging

SwitchShorthandDescription
/logCustVars:value/lcv:valueEnables custom logging of specific variables. The default value is False. Users can specify the following values:
  • True
  • False
/custVarDate1:value/cvdt1:valueSpecifies the name of the first variable with a date data type to log during package execution. The custVarDate1 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyDateVariable1".
/custVarDate2:value/cvdt2:valueSpecifies the name of the second variable with a date data type to log during package execution. The custVarDate2 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyDateVariable2".
/custVarDate3:value/cvdt3:valueSpecifies the name of the third variable with a date data type to log during package execution. The custVarDate3 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyDateVariable3".
/custVarNum1:value/cvn1:valueSpecifies the name of the first variable with a numeric data type to log during package execution. The custVarNum1 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyNumericVariable1".
/custVarNum2:value/cvn2:valueSpecifies the name of the second variable with a numeric data type to log during package execution. The custVarNum2 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyNumericVariable2".
/custVarNum3:value/cvn3:valueSpecifies the name of the third variable with a numeric data type to log during package execution. The custVarNum3 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyNumericVariable3".
/custVarString1:value/cvs1:valueSpecifies the name of the first variable with a string data type to log during package execution. The custVarString1 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyTextVariable1".
/custVarString2:value/cvs2:valueSpecifies the name of the second variable with a string data type to log during package execution. The custVarString2 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyTextVariable2".
/custVarString3:value/cvs3:valueSpecifies the name of the third variable with a string data type to log during package execution. The custVarString3 switch will be ignored if the logCustVars switch is set to False. The default value is True. The value may include the variable scope, for example "User::MyTextVariable3".
/custVarPackageScope: value/cvps:valueEnables logging of the specified variable(s) at the package level during package execution. The custVarPackageScope switch will be ignored if the logCustVars switch is set to False. The default value is True. Users can specify the following values:
  • True
  • False
/custVarTaskScope:value/cvts:valueEnables logging of the specified variable(s) at the task level during package execution. The custVarTaskScope switch will be ignored if the logCustVars switch is set to False. The default value is True. Users can specify the following values:
  • True
  • False
/custVarOnError:value/cvoe:valueEnables logging of the specified variable(s) when the OnError event handler executes. The custVarOnError switch will be ignored if the logCustVars switch is set to False. The default value is True. Users can specify the following values:
  • True
  • False
/custVarOnWarning:value/cvow:valueEnables logging of the specified variable(s) when the OnWarning event handler executes. The custVarOnWarning switch will be ignored if the logCustVars switch is set to False. The default value is True. Users can specify the following values:
  • True
  • False
/custVarOnPreExecute: value/cvope:valueEnables logging of the specified variable(s) when the OnPreExecute event handler executes. The custVarOnPreExecute switch will be ignored if the logCustVars switch is set to False. The default value is True. Users can specify the following values:
  • True
  • False
/custVarOnPostExecute: value/cvops:valueEnables logging of the specified variable(s) when the OnPostExecute event handler executes. The custVarOnPostExecute switch will be ignored if the logCustVars switch is set to False. The default value is True. Users can specify the following values:
  • True
  • False

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). 

BI xPress Server Notification Engine diagram

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

Templates

BI xPress Server Notification Engine 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.

OptionDescription
NameThe name for the message template.
No. of NotificationThe amount of notification events assigned to the following message template.
Content TypeThe type of content contained within the message template. Currently only HTML and Plaintext are supported.
PreviewDisplays a preview of the message template.
EditManually update the message template.
DeleteDelete the message template.

Events

BI xPress Server Notification Engine 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.

OptionDescription
NotificationThe name of the notification.
Last StatusThe data and time the notification was last sent and if it was sent successfully.
No. of Executions The number of times the notification ran.
PriorityThe priority level of the notification. Any Notification request sent with the same name and a priority greater than or equal to the listed priority triggers the notification event.
Event NameThe name of the notification event. Only HTTP requests that are sent with this event name can trigger.
TestSends a test notification to confirm the notification is properly configured.
EditManually update the notification event.
DeleteDelete the message template.

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:

Markup propertyDescription
PlaceholdersBinds values passed in by the notification request to the template. Placeholders may display text when outputted.
TagsManipulates how a message appears. Tags never display text when outputted.

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

Note:  If you want to alter the HTML structure, you should surround the advanced syntax language with comments.

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

ArgumentDescription
ParameterThe name of the input parameter. If the parameter is not provided and there is no default filter, then the output is an empty string.
FilterFilters change the behavior of the parameter. Filters proceed the parameter name with pipe characters separating each filter. Some filters need extra arguments. These arguments proceed their respective filter by a colon and a comma-separated list.

The following Placeholder filters are available:

Append

Adds the parameter to the beginning of the argument.

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

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter that will be appended to the front.
String ValueThe string that the parameter appends to the front of.

Example One

Input
{{ 'First' | append: 'Second'}}
Output'FirstSecond'

Example Two

InputParameter  Value'input'
Input
{{ InputParameter | append: 'Second'}}
Output'inputSecond'

Capitalize

Capitalizes words within the parameter.

Syntax

{{ <Parameter> | capitalize }}

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter containing the string to capitalize.

Example One

Input
{{ 'one two three four' | capitalize}}
Output'One Two Three Four'

Example Two

InputParameter Value'the quick brown fox'
Input 
{{ InputParameter | capitalize}}
Output'The Quick Brown Fox'

Ceil

Rounds a numerical parameter to the nearest integer.

Syntax

{{ <Parameter> | ceil }}

Arguments

ArgumentDescription
ParameterA numerical value or name of the input parameter containing the number to be rounded to the nearest integer.

Example One

Input
{{ 3.14 | ceil}}
Output4

Example Two

Input Parameter Value19.7
Input 
{{ InputParameter | ceil}}
Output20

Date

Reformats a date parameter using specified format.

Syntax

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

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter to be reformatted.
String ValueThe string that the contains the flags used to format the date.

The following is a list of accepted flags:

FlagDescription
%aAbbreviated Weekday
%AFull weekday Name
%bAbbreviated month name
%BFull month name
%cPreferred local date and time representation
%dDay of the month, zero-padded
%-dDay of the month, not zero-padded
%DFormats the date (dd/mm/yy)
%eDay of the month, blank-padded
%FReturns the date in ISO 8601 format (yyyy-mm-dd)
%HHour of the day, 24-hour clock, zero-padded
%IHour of the day, 12-hour clock
%jDay of the year
%kHour of the day, 24-hour clock, non-zero-padded
%mMonth of the year
%MMinute of the hour
%pMeridian indicator
%r12-hour time without seconds
%R24-hour time without seconds
%T24-hour time with seconds
%UWeek of the year starting with the first Sunday
%WWeek of the year starting with the first Monday
%wDay of the week starting with Sunday
%xPreferred representation for the date
%XPreferred representation for the time
%yYear without century
%YYear with century
%ZTime zone name

Example One

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

Example Two

Input Parameter Value'2009-05-08T10:30:00'
Input
{{ InputParameter | date: 'The year was %Y, the month was %b, the day was %d 
and the time was %I''}}
Output'The year was 2009, the month was May, the day was 08 and the time was 10:30'

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

ArgumentDescription
ParameterA string literal or name of the input parameter to use.
String ValueThe string that the contains the default value to use if the parameter is either empty or not provided.

Example One

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

Example Two

Input Parameter Value'This is a custom value'
Input
{{ InputParameter | default: 'This is a default value'}}
Output'This is a custom value'

Divided_by

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

Syntax

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

Arguments

ArgumentDescription
ParameterAn integer value or name of the input parameter that will be the dividend.
Integer ValueAn integer value that will be the divisor.

Example One

Input
{{ 13 | divided_by: 5}}
Output2

Example Two

Input Parameter Value42
Input
{{ InputParameter | divided_by: 8}}
Output5

Downcase

Converts all letters within the parameter to lowercase

Syntax

{{ <Parameter> | downcase }}

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter to convert to lowercase.

Example One

Input Parameter Value'I DO NOT KNOW WHY I AM SCREAMING'
Input{{ InputParameter | downcase: 'This is a default value'}}
Output'i do not know why i am screaming'

Example Two

Input
{{ 'LOUD NOISES' | downcase: 'This is a default value'}}
Output'loud noises'

Floor

Rounds a numerical parameter down to the nearest integer.

Syntax

{{ <Parameter> | floor }}

Arguments

ArgumentDescription
ParameterA numerical value or name of the input parameter containing the number to be rounded down to the nearest integer.

Example One

Input
{{ 3.14 | floor}}
Output3

Example Two

Input Parameter Value19.7
Input
{{ InputParameter | floor}}
Output19

Istrip

Removes all whitespace from the beginning of the parameter

Syntax

{{ <Parameter> | lstrip }}

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter to remove all whitespace from the beginning

Example One

Input Parameter Value'                  Space, the final frontier             '
Input 
{{ InputParameter | lstrip}}
Output'Space, the final frontier             '

Example Two

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

Minus

Subtraction. Subtracts a numerical parameter with a second value.

Syntax

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

Arguments

ArgumentDescription
ParameterA numerical value or name of the input parameter that will be the subtracted from.
Numerical ValueA numerical value that will be subtracted.

Example One

Input
{{ 13 | minus: 5}}
Output8

Example Two

Input Parameter Value42
Input
{{ InputParameter | minus: 8}}
Output34

Modulo

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

Syntax

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

Arguments

ArgumentDescription
ParameterA numerical value or name of the input parameter that will represent the dividend.
Numerical ValueA numerical value that will represent the divisor.

Example One

Input
{{ 13 | modulo: 5}}
Output3

Example Two

Input Parameter Value42
Input
{{ InputParameter | modulo: 8}}
Output2

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

ArgumentDescription
ParameterAn integer value or name of the input parameter to use.
Singular ValueA string literal that the contains the value if the parameter value is 0 or 1.
Plural ValueA string literal that the contains the value if the parameter value is greater than 1.

Example One

Input Parameter Value3
Input
{{ InputParameter | pluralize: 'student', 'students'}}
Outputstudents

Example Two

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

Plus

Addition. Adds a numerical parameter with a second value.

Syntax

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

Arguments

ArgumentDescription
ParameterA numerical value or name of the input parameter that will be the added to.
Numerical ValueA numerical value that will be added.

Example One

Input
{{ 13 | plus: 5}}
Output18

Example Two

Input Parameter Value42
Input
{{ InputParameter | plus: 8}}
Output50

Prepend

Adds the parameter to the end of the argument.

Syntax

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

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter that will be added to the end of the string.
String ValueThe string that the parameter will be added to the end of.

Example One

Input
{{ 'First' | prepend: 'Second'}}
Output'SecondFirst'

Example Two

Input Parameter Value'input'
Input
{{ InputParameter | prepend: 'Second'}}
Output'Secondinput'

Remove_first

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

Syntax

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

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter that will be searched through.
SearchThe first occurrence of the supplied string literal will be removed from the parameter.

Example One

Input Parameter Value'The following information has been REDACTED from view by the REDACTED'
Input
{{ InputParameter | remove_first: 'REDACTED'}}
Output''The following information has been  from view by the REDACTED'

Remove

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

Syntax

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

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter that will be searched through.
SearchAll occurrence of the supplied string literal will be removed from the parameter.

Example One

Input Parameter Value'The following information has been REDACTED from view by the REDACTED'
Input
{{ InputParameter | remove: 'REDACTED'}}
Output''The following information has been  from view by the '

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

ArgumentDescription
ParameterA string literal or name of the input parameter that will be searched through.
SearchThe first occurrence of the supplied string literal will be replaced from the parameter.
ReplacementThe value that will be inserted within the parameter in place of the first occurrence.

Example One

Input Parameter Value'The following information has been REDACTED from view by the REDACTED'
Input
{{ InputParameter | replace_first: 'REDACTED', 'censored'}}
Output'The following information has been censored from view by the REDACTED'

Replace

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

Syntax

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

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter that will be searched through.
SearchAll occurrence of the supplied string literal will be replaced from the parameter.
ReplacementThe value that will be inserted within the parameter in place of the all occurrence.

Example One

Input Parameter Value'The following information has been REDACTED from view by the REDACTED'
Input
{{ InputParameter | replace: 'REDACTED', 'censored'}}
Output'The following information has been censored from view by the censored'

Round

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

Syntax

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

Arguments

ArgumentDescription
ParameterA numerical value or name of the input parameter containing the number to be rounded to the nearest decimal place.
Integer ValueAn integer value represents the number of decimal places to round to.

Example One

Input
{{ 3.14 | round: 1}}
Output3.1

Example Two

Input Parameter Value19.759876
Input
{{ InputParameter | round: 5}}
Output19.75988

Rstrip

Removes all trailing whitespace from the parameter.

Syntax

{{ <Parameter> | rstrip }}

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter to remove all trailing whitespace.

Example One

Input Parameter Value'                  Space, the final frontier             '
Input
{{ InputParameter | rstrip}}
Output'                  Space, the final frontier'

Example Two

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

Size

Returns the size of an array or string.

Syntax

{{ <Parameter> | size }}

Arguments

ArgumentDescription
Parameter An array, string literal or name of the input parameter to use.

Example One

Input
{{ InputParameter | size}}
Output0

Example Two

Input Parameter Value'This is fifteen'
Input
{{ InputParameter | size}}
Output15

Slice

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

Syntax

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

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter to be sliced.
OffsetAn integer value representing the starting place for the slice. If Offset is negative, slice starts that far back from the end of the string.
LengthAn integer value representing the total number of characters to include within the slice.

Example One

Input Parameter Value'Four score and seven years ago'
Input
{{ InputParameter | slice: 5, 15}}
Output'score and seven'

Example Two

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

Times

Multiplication. Multiplies two numerical parameters.

Syntax

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

Arguments

ArgumentDescription
ParameterA numerical value or name of the input parameter that will be the multiplied.
Numerical ValueA numerical value that will be multiplied.

Example One

Input
{{ 13 | times: 5}}
Output65

Example Two

Input Parameter Value42
Input
{{ InputParameter | times: 8}}
Output336

Truncate

Returns a specific number of characters from a string.

Syntax

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

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter to be truncated.
TruncateAn integer value representing the amount of characters to include before truncating the rest.
AppendAn optional string value that will be appended to the end of the parameter after truncation occurs.

Example One

Input Parameter Value'Four score and seven years ago'
Input
{{ InputParameter | truncate: 5}}
Output'Four '

Example Two

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

Truncatewords

Returns a specific number of words from a string.

Syntax

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

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter to be truncated.
TruncateAn integer value representing the amount of words to include before truncating the rest.

Example One

Input Parameter Value'Four score and seven years ago'
Input
{{ InputParameter | truncatewords: 3}}
Output'Four score and'

Example Two

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

Upcase

Converts all letters within the parameter to uppercase.

Syntax

{{ <Parameter> | upcase }}

Arguments

ArgumentDescription
ParameterA string literal or name of the input parameter to convert to uppercase.

Example One

Input Parameter Value'i do not know why i am screaming'
Input
{{ InputParameter | upcase: 'This is a default value'}}
Output'I DO NOT KNOW WHY I AM SCREAMING'

Example Two

Input
{{ 'loud noises' | upcase: 'This is a default value'}}
Output'LOUD NOISES'

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

ArgumentDescription
TagThe name of the Tag. This changes the functionality of what occurs within the tag block.
ConditionsSome Tags require additional conditions. These conditions are places after a tag name and drive the content within the tag block.
Template BlockA small section of the template that the tag affects.
EndTagUsed alongside the Tag name to denote the end of a template block.

Note:  You should surrong the tag syntax language with comments if you want to alter the HTML structure.

<!-- {% <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

ArgumentDescription
ConditionA string literal or name of the input parameter used as the comparison.
MatchCandidates for comparison. The first candidate that matches the condition displays its respective template block.

Example

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

Comment

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

Syntax

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

Example

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

Cycle

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

Syntax

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

Arguments

ArgumentDescription
Group NameWhen calling the cycle tag, the group argument helps differentiate between cycles. If a group name is not supplied then cycle assumes multiple calls with the same parameters is the same group.
ItemThe comma-separated list of items that will be recursively cycled through.

Example One

Input
{% 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' %}
OutputBadger Badger Badger Mushroom Mushroom Snake Badger

Example Two

Input
{% 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' %}
Output1) Soccer 2) Football 3) Rugby 4) Baseball 5) Soccer 1) Football

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

ArgumentDescription
ArrayAn array or name of the input parameter array to use.
ItemUsed within the template block for the current element within the Array. Within a loop, two methods of specifying properties of an array are available. If the property name contains no whitespace, users may choose either method. If the property name does contain whitespace, then users must use the second method.
<Item>.<PropertyName>
<Item>['<Property Name>']
OptionsInfluence the behavior of the for loop. There are three optional parameters:
  • limit: <Integer Value> - Restricts the number of elements that will be looped through
  • offset: <Integer Value> - Allows users to start looping at the nth element
  • reversed - Loops through the collection from last to first

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:

Optional ArgumentDescription
forloop.lengthReturns the length of the entire loop.
forloop.indexReturns the index value of the current element [1, ..., n].
forloop.index0Returns the zero-based index value of the current element [0, ..., n].
forloop.rindexReturns the number of elements left to iterate through [1, ..., n].
forloop.rindex0Returns the zero-based number of elements left to iterate through [0, ..., n].
forloop.firstReturns a boolean value as to whether this is the first element within the loop.
forloop.lastReturns a boolean value as to whether this is the last element within the loop.

Example

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

Source - Error 1 Time - Time 1 Details - Details 1

Source - Error 2 Time - Time 2 Details - Details 2

Source - Error 3 Time - Time 3 Details - Details 3

Break

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

Syntax

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

Example

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

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

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

Source - Error 1 Time - Time 1 Details - Details 1

Source - Error 3 Time - Time 3 Details - Details 3

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

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

If

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

Syntax

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

Arguments

ArgumentDescription
ConditionAn expression that must resolve to a true or false value. Display of the template block occurs only if the condition returns true.

Boolean Operators

These operators combine conditions together:

BooleanDescription
andBoth conditions must evaluate to true.
orAt least one condition must evaluate to true.

Comparison Operators

These operators typically compare parameters with a set value:

OperatorDescription
==Used with strings or numerical values. Returns true if the left side of the condition equals the right side.
!=Used with numerical values. Returns true if the left side of the condition does not equal the right side.
<>Used with numerical values. Returns true if the left side of the condition does not equal the right side.
<Returns true if the left side of the condition is numerically less than the right side.
<=Used with numerical values. Returns true if the left side of the condition is numerically less than or equal to the right side.
>Used with numerical values. Returns true if the left side of the condition is numerically greater than the right side.
>=Used with numerical values. Returns true if the left side of the condition is numerically greater than or equal to the right side.
containsUsed with strings or array values. Returns true if the left side of the condition includes the right side of the condition within it.

Helper Tag

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

  • elseif
  • else

Example

Input Parameter Value'Orange'
Input
My favorite animal is:{% if InputParameter == 'Orange' %}
Fox
{% endif %}
OutputMy favorite animal is: Fox

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

Input Parameter Value6
Input
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 %}!
OutputI like a handful of different animals!

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

Input Parameter Value'Green'
Input
My favorite animal is:{% if InputParameter == 'Orange' %}
Fox
{% else %}
Iguana
{% endif %}
OutputMy favorite animal is: Iguana

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
Input
{% raw %}
I really need to use the mustache handlebar for my marvelously mustached smiley faces: :-{
{% endraw %}
OutputI really need to use the mustache handlebar for my marvelously mustached smiley faces: :-{

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

ArgumentDescription
ConditionAn expression that must resolve to a true or false value. Display of the template block occurs only if the condition returns false.

Boolean Operators

These operators combine conditions together:

OperatorDescription
andBoth conditions must evaluate to true.
orAt least one condition must evaluate to true.

Comparison Operators

These operators typically compare parameters with a set value:

OperatorDescription
==Used with strings or numerical values. Returns true if the left side of the condition equals the right side.
!=Used with numerical values. Returns true if the left side of the condition does not equal the right side.
<>Used with numerical values. Returns true if the left side of the condition does not equal the right side.
<Returns true if the left side of the condition is numerically less than the right side.
<=Used with numerical values. Returns true if the left side of the condition is numerically less than or equal to the right side.
>Used with numerical values. Returns true if the left side of the condition is numerically greater than the right side.
>=Used with numerical values. Returns true if the left side of the condition is numerically greater than or equal to the right side.
containsUsed with strings or array values. Returns true if the left side of the condition includes the right side of the condition within it.

Example

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

Templates

BI xPress Server Notification Engine Templates Page

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.

OptionDescription
Template NameThis details a user-friendly name that helps differentiate message templates.
Template IDAn auto generated, non-editable, unique identifier for the current message template.
Content ViewContains different formats for viewing and editing the template:
ViewDescription
PlaintextA simple, non-formatted text editor. This provides users with the option to add more advanced HTML beyond the HTML toolbar.
HTMLA simplified content editor that supplies common text formatting options.

Note:  Use Plaintext to add custom HTML beyond what the HTML toolbar provides. should use Plaintext. Switch between content views to see how the changes appear.

To send pre-formatted text through parameters you need to wrap your placeholders with pre-formatted text elements. This can be done using the following syntax:

<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.

BI xPress Server Notification Engine Notification Events

OptionDescription
Notification NameThis details a user-friendly name that helps differentiate the notifications.
Notification IDAn auto generated unique identifier for the current notification.
Event NameThe name of the event BI xPress Server will be listening for. Only HTTP requests that are sent with this event name could be triggered.
SeverityThe severity threshold for the notification. Any Notification request sent with the same event name as well as a priority greater than or equal to the listed severity will trigger the notification event.
FiltersAdditional conditions based on notification parameters that limit whether or not the notification will be triggered. All HTTP requests that match the filters will be excluded from being triggered.
ProviderThe type of notification that will be sent. The following notifications are available:
Notification TypeDescription
Event LogWhen triggered, a Notification using the Event Log Provider will write the message template to the Windows Event Log where BI xPress Server is located. Truncation may occur if the message template is longer than 15,000 characters.
SMS Text MessageWhen triggered, a Notification using the SMS Text Message Provider will text the message template to the desired phone number(s).
E-MailWhen triggered, a Notification using the E-mail Provider will e-mail the message template to the desired e-mail address(es).
SQLWhen triggered, a Notification using the SQL Provider will attempt to execute the message template as T-SQL.
Text FileWhen triggered, a Notification using the Text File Provider will write the message template to a text file.
TwitterWhen triggered, a Notification using the Twitter Provider will tweet the message template. Truncation may occur if the message template is longer than 140 characters.
Visual Studio Team ServicesWhen triggered, a work item is created using the message template as the work item's repro steps and description.
REST CallWhen triggered, a REST call is made to the specified URL, using the message template as the payload. Basic, OAUTH1 and OAUTH2 authentication types are supported.
Run ProcessWhen triggered, the specified executable is run. Optionally, a file can be created containing the message template and passed to the executable as a command line parameter.
Message TemplateThe current template that will be applied to the Provider when a valid HTTP request is received.
EnabledWhether or not the current notification is enabled and listening for HTTP requests.

Note:  To use the Event Log provider, The BI xPress Application Pool will need to initially create a BI xPress event source. The initial creation of an event source requires administrative privileges. After initially creating the event source, these administrative privileges are not required. 

Administrators can manually create the event source from an elevated Windows PowerShell prompt using the following information:

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

Additional Information: You need to create a Personal Access Token with the "Work Items (read and write) scope selected to use the Visual Studio Team Services provider. For more information on creating a Personal Access Token within your Visual Studio Team Services Instance or TFS Server, see the Authenticate access with personal access tokens MSDN article.

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

Note:  Using the BI xPress Notification Framework to send notifications through BI xPress Server's Notification Engine requires a BI xPress license.

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

SwitchDescription
-H or -?Shows the command prompt help message.

Operation

SwitchDescription
[-Operation] "value"This operation specifies the type of operation the Notification Engine command line tool will take. The sendNotifications operation will send all notifications with the same event name as the value of the eventName switch. The sendNotification operation will send the notification that has the notification id that matches the value of the notificationId switch. You can specify the following values:
  • sendNotifications
  • sendNotification

Information

SwitchDescription
-eventName "value"When used with the sendNotifications operation switch, all notification events that use the specified eventName will trigger, sending all related notifications.
-notificationId "value"When used with the sendNotification Operation switch, only the notification that has the specified notification id will trigger, sending a single notification.

Parameters

SwitchDescription
-"name" "value" [...n]You can pass any number of key-value paired parameters through the Notification Engine's command line tool. Each parameter you want to pass through must have a switch for the parameter name and the value you want to match to that parameter.

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. 

Important:  The SendNotifications request is the preferred method for posting requests to the BI xPress Server Notification Engine.

SendNotifications Request

URI
POST http[s]://<serverName>[:<port>]/api/BIxPress/NotificationEngine/SendNotifications
Content Typeapplication/json
Request Body
{
"eventName" : "event-name",
"notificationSeverity" : "severity",
"parameters" :
[
{"name" : "parameter-name", "value" : "parameter-value"}
]
}

SendNotification Request

URI
POST http[s]://<serverName>[:<port>]/api/BIxPress/NotificationEngine/SendNotification
Content Typeapplication/json
Request Body
{
"notificationId" : "notification-id",
"notificationSeverity" : "severity",
"parameters" :
[
{"name" : "parameter-name", "value" : "parameter-value"}
]
}

Request Containing Array Parameters

URI
POST http[s]://<serverName>[:<port>]/api/BIxPress/NotificationEngine/SendNotifications
Content Typeapplication/json
Request Body
{
"eventName" : "event-name",
"notificationSeverity" : "severity",
"parameters" :
[
{"name" : "parameter-name", "values" :
[
{"name" : "parameter-name", "value" : "parameter-value"}
]
}
]
}

Information

SwitchDescription
"eventName" : "value"All notification events that use the specified value for the eventName property will trigger, sending all related notifications.
"notificationId" : "value"Only the notification that has the specified notification id will trigger, sending a single notification.
"notificationSeverity" : "value"

The severity threshold for the notification currently being sent. Any request sent with the same event name or notification id as well as a severity greater than or equal to the notification's listed priority will trigger the notification event. Users can specify the following values:

  • very low
  • low
  • medium
  • high
  • very high
  • critical
  • catastrophic

Note:  If the notificationSeverity property is not provided or an invalid value is entered, the Notification Engine uses the default value of very low.

Parameters

SwitchDescription
{ "name" : 
"parameter-name", 
"value" : 
"parameter-value" }
Within the JSON Request Body, the parameters contain an array of key-value pairs. Each parameter the user wishes to pass through must include an additional line where the name property contains the name of the parameter and the value property is the value the user wishes to assign to that specific parameter.

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.

URIPOST http://localhost/api/BIxPress/NotificationEngine/SendNotifications
Request Body
{
"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".

URIPOST http://DEV-BIX-SRV:9090/api/BIxPress/NotificationEngine/SendNotifications
Request Body
{
"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".

URIPOST http://DEV-BIX-SRV:9090/api/BIxPress/NotificationEngine/SendNotification
Request Body
{
"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.

URIPOST http://DEV-BIX-SRV:9090/api/BIxPress/NotificationEngine/SendNotifications
Request Body
{
"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

Report NameDescription
Execution DashboardThis dashboard gives one place to view the most common counters of SSIS package execution, including top 20 slow packages, fast packages, minimum used packages, maximum used packages, recent data extracts, recent data loads.
Extract / Load TrendProvides graphical view of extract and load trend over several years, weeks, months and days.
Extract / Load DetailProvides extract / load detail for every Data Flow within each package execution.
Package PerformanceProvides package level performance graph within a specified interval.
Package & Task PerformanceProvides package and task level performance graphs within a specified interval.
Package AlertsProvides detailed error / warning report including package level errors.
Package Execution TrendThis report provides many useful information about trend of package runtime, task runtime and errors / warnings.
Recent Execution SummaryProvides information regarding the execution status of packages.
Recent Execution DetailsProvides information regarding the execution status of packages and tasks.
Runtime Standard DeviationThis report displays packages which are running slower / faster than their usual trend.

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.

Important:  The Monitoring Console only monitors SSIS package executions that have the Auditing Framework applied to them or were imported using the SSIS Catalog Import Feature.

Precedence constraints within containers are not supported within the Monitoring Console. This limitation has no impact on package executions.

Feature Highlights

  • Watch packages as they execute in real time

BI xPress Server SSIS Monitoring Dashboard

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
PanelDescriptionImage
Errors and WarningsThe errors and warnings panel provides details of all errors and warnings thrown by the currently selected package, making it easier to troubleshoot problem packages.BI xPress Server SSIS Monitoring Dashboard Errors and Warnings
VariablesThe variables panel displays a time line of all changes to user variables within the currently selected package.BI xPress Server SSIS Monitoring Variables
ConnectionsThe connections panel provides details of all present connection managers within the currently selected package.BI xPress Server SSIS Monitoring Connections

Package Execution Panel

BI xPress Server SSIS Monitoring Package Executions

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.

OptionDescription
Package Executions HeaderContains several options for adjusting the displaying of package executions:
ButtonDescription
BI xPress Server Package Executions Collapse All buttonCollapses all package groups when grouping is enabled.
BI xPress Server SSIS Montiroing Package Executions Toggle buttonToggles between organizing the package execution panel by groups or by package.
BI xPress Server SSIS Monitoring Package Executions Filter buttonContains settings for filtering executions based on a specific set of rules.
Execution NameLocated in the upper left hand corner of each execution, this displays the name of the package that was executed.
Execution SummaryEach execution displays a visual and textual summary for the specific execution:
StatDescription
StatusThe 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 RuntimeThe 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 SelectionDisplays the name and status of executions, and controls which execution display within the other Execution Diagram.

Execution Diagram Panel

BI xPress Server SSIS Monitoring 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

BI xPress Server SSIS Monitoring Execution Diagram Header bar

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:

OptionDescription
Execution NameDisplays the name of the execution.
User and Machine InformationDisplays the user account and machine that ran the package.
Date and RuntimeDetails the start time and how long the execution ran before completing.
StatusThe background color of the header describes the current status for the package:

  • Red - Package completed with at least one error
  • Green - Package completed without errors
  • Yellow - Package is still running
  • Blue - Package was manually stopped before execution completed

Toolbar

BI xPress Server SSIS Monitoring Execution Diagram toolbar

The toolbar controls what currently displays within the diagram.

OptionDescription
Control FlowSwitches the diagram view to display the current execution's control flow execution. The diagram displays the control flow execution by default.
Data FlowSwitches the diagram to display the current execution's data flow executions. The data flow button is only enabled if the execution contains at least one data flow execution. The diagram displays the first data flow execution by default. Use the data flow selector to select a different data flow execution.
Data Flow SelectorSelect the data flow execution you want to display within the diagram. The selector is only enabled if you are currently viewing the data flow executions.
DOC xPress SearchSearches DOC xPress Server's metabase for the selected object. A valid DOC xPress Server license must be present.
Auto Generated Diagram LabelThis label notifies you that the layout information is out of date or missing. This label is only displayed if you have auto layout enabled.
Auto Layout ButtonToggles between using the layout present within the package. If enabled, BI xPress detects out of date or missing layouts and attempts to generate a more accurate diagram. If disabled, BI xPress displays the provided package layout, regardless of whether it is out of date or missing.

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.

Note:  When selecting a package execution imported using the SSIS Catalog Import feature for the first time, the Monitoring Console attempts to download the package layout. This allows the Monitoring Console to display the package in a familiar way to SSIS developers.

This process requires you to run the BI xPress Server Application Pool with read permissions on the SSIS Catalog.

Navigation Buttons

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

ButtonDescription
BI xPress Server SSIS Monitoring Execution Diagram Zoom out buttonZooms out to display more of the execution diagram.
BI xPress Server SSIS Monitoring Execution Diagram Zoom in buttonZooms in to show less of the execution diagram.
BI xPress Server SSIS Monitoring Execution Diagram Reset ZoomResets 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

BI xPress Server SSRS Monitoring Dashboard

Note:  Reporting Monitor Console is compatible with SSRS 2008 or later databases only.

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.

BI xPress Server SSRS Monitoring Feature Settings

Note:  You need to be a Workbench Server Administration user to configure the feature settings for the Report Monitoring Console.

Report Averages

BI xPress Server SSRS Monitoring 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.

ColumnDescription
Report NameThe name of the report as defined by the filename on the reporting instance.
Last Report StatusIndicates the status of the most recent execution.
No. of ExecutionsThe number of times this report has executed, either successfully or unsuccessfully.
Avg. RuntimeThe average runtime of the report for all available executions.
Avg. Data Retrieval TimeThe average amount of time it took for the reporting server to retrieve data from the report specified data sources.
Avg. Processing TimeThe average amount of time it took for the reporting server to calculated and process the data retrieved from the data sources. Also, the amount of time it took to execute any formulas or expressions will be included in this value.
Avg. Rendering TimeThe average amount of time it took for the reporting server to draw the report including graphics, images, text, and charts for all executions available.
Avg. DataThe average size of all data that was transferred to the reporting server for all available executions.
Avg. RowsThe average number of rows that were retrieved by the reporting server for all available executions.
Avg. Max MemoryThe average value of the maximum memory consumption required by each report during execution.

Frequent Reports

BI xPress Server SSRS Monitoring 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

BI xPress Server SSRS Monitoring Most 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

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:

BI xPress Server System Dashboard Action buttons

OptionDescription
InformationAllows you to toggle the System Dashboard information text, located between the Workbench Server and tool icons.
RefreshUpdates the dashboard view with the most current data.
Edit DashboardOpens the System Dashboard Settings page to configure the tiles on the dashboard.

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

System Dashboard Settings

BI xPress Server 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

BI xPress Server System Dashboard Successful Packages tile

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

OptionDescription
NameThe display name of the tile on the System Dashboard.
DescriptionInformation that describes the tile (such as purpose, type of packages, etc.). Descriptions are not visible on the dashboard but can be seen on the System Dashboard Settings page.
Time PeriodDetermines the amount of time (in the past) that filters the results for the tile.
Package Name FilterFilters results based on the name.

Note:  Leaving blank will return all results within the time period.

Warning Packages

BI xPress Server System Dashboard Warning Packages tile

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

OptionDescription
NameThe display name of the tile on the System Dashboard.
DescriptionInformation that describes the tile (such as purpose, type of packages, etc.). Descriptions are not visible on the dashboard but can be seen on the System Dashboard Settings page.
Time PeriodDetermines the amount of time (in the past) that filters the results for the tile.
Package Name FilterFilters results based on the name.

Note:  Leaving blank will return all results within the time period.

Failed Packages

BI xPress Server System Dashboard Failed Packages

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

OptionDescription
NameThe display name of the tile on the System Dashboard.
DescriptionInformation that describes the tile (such as purpose, type of packages, etc.). Descriptions are not visible on the dashboard but can be seen on the System Dashboard Settings page.
Time PeriodDetermines the amount of time (in the past) that filters the results for the tile.
Package Name FilterFilters results based on the name.

Note:  Leaving blank returns all results within the time period.

Execution Count

BI xPress Server System Dashboard Execution Count

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

OptionDescription
NameThe display name of the tile on the System Dashboard.
DescriptionInformation that describes the tile (such as purpose, type of packages, etc.). Descriptions are not visible on the dashboard but can be seen on the System Dashboard Settings page.
Time PeriodDetermines the amount of time (in the past) that filters the results for the tile.
Package Name FilterFilters results based on the name.

Note:  Leaving blank will return all results within the time period.