BI xPress Auditing Framework

Auditing Framework

Feature IconDescription
BI xPress Auditing Framework IconAuditing Framework helps track the activity for SSIS packages, and provides you with rich auditing information from SSIS package executions, such as errors and warnings.

Note:  The Auditing Framework must have access to all third-party components and may not work with components that effect event handlers. You must use the SentryOne Workbench (32-bit) when applying the Auditing Framework to SSIS packages on a machine that only contains SSDT for Visual Studio 2015.

Warning:  Do not alter any task names or descriptions created by the Auditing Framework.

Feature Highlights

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

Open the Auditing Framework feature using the SentryOne Workbench, or in your development environment with the Auditing Framework Add-in.

BI xPress Auditing Framework Add-in
BI xPress Auditing Framework in the Workbench

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

  • Configuration(s), package or project parameter(s)
  • Package level event handlers for the following events:
    • OnError
    • OnWarning
    • OnPreExecute
    • OnPostExecute
  • Row Counts added after each source adapter and before each destination adapter
  • BI xPress Connection Manager pointing to the BI xPress Database
  • Variables within the OnPostExecute event handler

BI xPress Auditing Framework Architecture

Auditing Framework Add-in

The Add-in manages the auditing framework from within your development environment. The following development environments are supported:

  • Business Development Studio (BIDS)
  • Visual Studio (VS)
  • SQL Server Data Tools (SSDT)

Launching the Add-in

Launch the Auditing Framework add-in with the BI xPress toolbar in the development environment or from the Solution Explorer. 

Select SentryOne > BI xPress > Add/remove Auditing Framework to display the Auditing Framework Wizard.

Right-click on an SSIS project or package within the Solution Explorer, and then select Add/remove Auditing Framework to display the Auditing Framework Wizard.

Auditing Framework Wizard

Use the Auditing Framework Wizard to Add, Reapply, or remove the Auditing Framework from selected SSIS Packages.

Note:  The pages within the Wizard change depending on whether you are adding or removing the Auditing Framework.

Select Action Page

BI xPress Auditing Framework Wizard Select Action

The Select Action Page is the first page on the Auditing Framework Wizard. The Select Action page allows you to choose whether you want to add, re-apply, or remove the Auditing Framework from your SSIS packages. The available pages change based on the action you choose. Select the action you want to take, and then select Next to continue. 

Select Packages Page

BI xPress Auditing Framework Wizard Select Location and Packages

Use the Select Packages page to select the packages where you want to add /reapply or remove the auditing framework. The Select Packages page controls the behavior for the deployment mode that's used by the Auditing Framework when working with SSIS 2012 and above packages. The Select Packages page is divided into the package location and selection areas.

Package Location

BI xPress Auditing Framework Wizard Package Location

Use the Package Location area to configure where the Auditing Framework searches for your SSIS Package(s). You can select from the following options:

OptionDescription
File SystemThe Auditing Framework searches the File System, using the selected path, for SSIS Packages. Select the Recursive option to scan the File System folder and sub-folder(s) for SSIS Packages. Packages selected using this search method have the Auditing Framework added / re-applied or removed using the Package Deployment Model.
SQL ServerThe Auditing Framework searches a SQL Server with the provided connection information for deployed SSIS Packages. Packages selected using this search method have the Auditing Framework added / re-applied or removed using the Package Deployment Model.
SSIS ProjectThe Auditing Framework searches an SSIS Project File (*.dtproj) with the selected path for SSIS Packages. If the packages are SSIS 2012 or above, packages selected using this search method have the Auditing Framework added / re-applied or removed using the Project Deployment Model.
SSIS Package StoreThe Auditing Framework searches the SSIS Package Store with the provided connection information for deployed SSIS Packages. Using this location requires the Auditing Framework to be running as the Administrator account. Packages selected using this search method have the Auditing Framework added / re-applied or removed using the Package Deployment Model.
Visual Studio SolutionThe Auditing Framework searches a Visual Studio Solution (*.sln) with the selected path for SSIS Packages contained within SSIS Projects associated with the solution. Packages selected using this search method have the Auditing Framework added / re-applied or removed using the Package Deployment Model.

Package Selection

BI xPress Auditing Framework Wizard Package Selection

Use the Package selection area to select the package(s)  from your configured location. The following options are available:

OptionDescription
FilterUse the filter to quickly filter out packages that you don't want to select.
BrowserThe Browser contains all packages from the location that have not been filtered.
Selection ButtonSelect Select All to select all packages in the Browser. Select Unselect All to deselect all selected packages from the browser.
Scan Packages ButtonSelect Scan Packages to open the Scanning Options window and select or deselect packages based on a set of your configured parameters.
Selected ItemsDisplays your current selected packages from the Browser that will have the Auditing Framework added /re-applied or removed.

Scanning Options window

BI xPress Auditing Framework Wizard Scanning Options window

OptionDescription
Scan ForSelect the Frameworks (Auditing and / or Notification) that you want to scan for within each package.
When selected frameworkSelect whether the Auditing Framework searches for the presence (Found) or absence (Not found) of the selected frameworks.
List item actionSelect the action you want the Auditing Framework to take when a package meets the parameters selected in the first two steps.
Sort list after scanning?Select to sort the list of packages are scanning for the Auditing or Notification framework.

After configuring your options on the Select Packages page, select Next to continue Adding/re-applying, or removing the Auditing Framework.

Note:  Selecting Next when Adding / re-applying the Auditing Framework opens the Options pageSelecting Next when Removing the Auditing Framework opens the Project Items Removal, or Summary page based on your selections. 

Options Page

BI xPress Auditing Framework Wizard Options

When adding or re-applying the Auditing Framework, the Options page contains four tabs that control all of the options used to customize the Auditing Framework. After you have configured the Connection tab with your desired settings, select Logging Options, Advanced User Defined Logging, or Alert Filtering to continue configuring the Auditing Framework. Select Next to open the summary page, and begin the process of adding/re-applying your Auditing Framework.

Connection Information tab

Use the Connection Information tab to configure the BI xPress database, connection manager, and configuration file /parameter options. The Connection Information tab is divided into three sections that differ based on the deployment location set on the Select Packages page.

 Auditing Database Connection Information

BI xPress Auditing Framework Wizard Options Auditing Database Connection Information

Use the Auditing Database Connection Information section to configure the server information used by the Auditing Framework to connect to the BI xPress Database. The following options are available:

OptionDescription
ServerThe name of the server where the BI xPress database is stored.
Windows Authentication
Controls whether the Auditing Framework uses Windows or SQL Server Authentication. Deselecting Windows Authentication prompts you to enter your SQL Server User ID and Password. 
  • User ID - The SQL Server Username you use to connect to the BI xPress Database.
  •  Password - The password for the SQL Server username you use to connect to the BI xPress Database.
DatabaseThe name of the database that contains the BI xPress Framework architecture.

Note:  If the selected database does not contain the Auditing Framework architecture, you will be prompted to generate the Auditing Framework architecture on the database.
OfflineCreates the Auditing Framework connection manager and sets it to Work Offline mode, allowing you to browse and continue developing your package when the BI xPress database isn't accessible.
Continue package execution on auditing database connection failureWhen selected, allows SSIS packages that contain the Auditing Framework to continue executing if the Auditing Framework fails.
Do not report failure if Auditing failsWhen selected, hides all errors that occur due to the Auditing Framework.
Create New DatabaseCreates a new database if there is not already a database with the BI xPress Framework Architecture.

Package / Project Connection

BI xPress Auditing Framework Wizard Options Package / Project Connection

Use the Package / Project Connection section to configure whether to create a new, or use an existing, connection manager. The scope of the connection manager changes depending on the deployment model selected on the Select Packages page. The following options are available:

Note:  If you selected File System, SQL Server, SSIS Package Store, or Visual Studio Solution as the location on the Select Packages page, the Auditing Framework will use the package connection managers.

If you selected SSIS Project as the location on the Select Packages page and the selected packages are SSIS 2012 or above, the Auditing Framework will use project connection managers.

OptionDescription
Create new package connectionPackage Connection Manager - Creates a new connection manager within each package with the specified connection manager name. Project Connection Manager - Creates a new connection manager at the project level with the specified connection manager name.
Use existing package connectionPackage Connection Manger - Select an existing connection manager from the first selected package. Project Connection Manager - Select an existing project connection manager.

Important:  Using an existing connection manager automatically disables the Auditing Database Connection Information section. When using this option, make sure the selected connection manager exists in all other packages, otherwise the Auditing Framework will not be applied. 

If you are using an existing connection manager, the selected connection manager should point to a database that contains the BI xPress Framework Architecture.

If you are using an existing project connection manager, the selected connection manager should point to a database that contains the BI xPress Framework Architecture.

Configuration Options

BI xPress Auditing Framework Wizard Options Configuration Options

Use the Configuration Options section to add a configuration file or parameters to your packages to control the behavior of the Auditing Framework. The configuration options, and the scope of the parameters change depending on the deployment model selected on the Select Packages page. The following options are available:

OptionDescription
Store Configuration InEnables the usage of configurations and the scope of the configurations. If configurations are enabled, the you can select the scope of the configuration settings.
Overwrite if existsWhen selected, the configuration file is overwritten if it already exists.
Config FolderControls the location where the configuration file is saved.
Use Configuration File (Package Deployment Model)The Auditing Framework creates a configuration file to store all of the configuration entries.
Use Package ParametersPackage Deployment Model - The Auditing Framework creates package parameters within each package to store all of the configuration entries. Note:  This option is available if the selected packages are SSIS 2012 or above. Project Deployment Model - The Auditing Framework creates project parameters to store all of the configuration entries. Note:  This option is available if the selected SSIS Project and packages are SSIS 2012 and above. The configuration options are only for project parameters.
Enable / Disable entire Auditing Framework Manually turns off the Auditing Framework.
Enable / Disable variable, parameter, and connection loggingManually turns off the logging of variable, parameter, and connection managers.
Enable / Disable real-time Data Flow monitoringManually turns off the Auditing Framework's real-time Data Flow monitoring.
Enable / Disable logging for all warningsMinimizes the amount of logging that occurs by not logging warnings that occur during execution.
Variable and connection filteringManually masks sensitive values contained within variables from being displayed when executions are viewed within the Monitoring Console.

Logging Options tab

BI xPress Auditing Framework Wizard Logging Options

Use the Logging tab to customize how the Auditing Framework controls Data Flow, Variables, and Connection Manager logging. The Logging Options tab is divided up into three sections, each controlling three different areas of the Auditing Framework's logging.  After you have configured the Logging Options  tab with your desired settings, select Connection Information, Advanced User Defined Logging, or Alert Filtering to continue configuring the Auditing Framework. Select Next to open the summary page, and begin the process of adding/re-applying your Auditing Framework.

Data Flow Logging

BI xPress Auditing Framework Wizard Logging Options Data Flow

The Data Flow logging section controls logging details within each Data Flow present within each SSIS Packages. The following options are available:

OptionDescription
Enable Real-time Data Flow MonitoringWhen selected, the Auditing Framework monitors data flows in real-time. Important:  If real-time Data Flow monitoring is turned off, some of the features of Monitoring Console's Execution Diagram will be unavailable.
Log Row CountsWhen selected, the Auditing Framework captures rows counts flowing inside data flows, and each data flow is modified to track row count data.
Log Source Row CountsWhen selected, Log Source Row Count tracks the number of rows that are coming from source components.
Log Destination Row CountsWhen selected, Log Destination Row Counts tracks the number of rows that are going to destination components.
Source SQL Statement LoggingWhen selected, Source SQL Statement Logging logs SQL Statements used to extract data. This information can be viewed using BI xPress Extract / Load Detail Report.
Connection Detail Logging When selected, Connection Detail Logging logs details for each source and destination component regarding the connections used.

Variable, Parameter, and Connection

BI xPress Auditing Framework Wizard Logging Options Variable, Parameter, and Connection

The Variable, Parameter, and Connection logging section controls logging details for Variables, Parameters, and Connection Managers within each SSIS Package. The following options are available:

OptionDescription
Log ConnectionsLog Connections log every connection manager's connection string value during run-time. Connection Masking - Hides the connection string for sensitive connection managers. Wildcards can be used for partial matching. If no wildcards are used, then the masking value will only match a connection manager's full name. Place multiple masking values within their own rows.
Log VariablesLog Variables log the values of every variable when the package execution is started and when the package execution completes. Variable Masking - Hides the values for sensitive Variables and parameters. Wildcards can be used for partial matching. If no wildcards are used, then the masking value will only match a Variable's full name. Place multiple masking values within their own rows.
Enable Variable Change TrackingMaintains a historical record of all value changes to variables during an SSIS execution.
Variable Logging TypeSelect between tracking all variables, or only variables used within For Each loops.
Initial Parameter Value LoggingInitial Parameter Value Logging also logs the initial values of all non-sensitive parameters within SSIS packages 2012 and up.

Miscellaneous

BI xPress Auditing Framework Wizard Logging Options Miscellaneous

The Miscellaneous section controls other logging options that do not control details regarding data flow, variables, parameters, and connection managers. The following options are available:

OptionDescription
Max Loop IterationsControls the number of loops within a For or For Each Loop you want to document. Setting this value to 0 means to log all iterations.
Script LanguageControls the programming language you want the Auditing Framework to use within Script Tasks.
Log WarningsControls whether to log warnings that are encountered during executions in the BI xPress database.

Advanced User defined logging

BI xPress Auditing Framework Wizard Advanced User defined Logging

Use the Advanced User defined logging tab to explicitly specify the logging of specific variables within the Auditing Framework.  After you have configured the Advanced User defined logging  tab with your desired settings, select Connection Information, Logging Options, or Alert Filtering to continue configuring the Auditing Framework. Select Next to open the summary page, and begin the process of adding/re-applying your Auditing Framework.The following options available:

OptionDescription
Enable Custom Variable Logging Explicitly specifies the logging of specific variables:
  • Numeric Variables - Log up three custom variables of a numeric data type.
  • String Variables - Log up three custom variables of a string data type.
  • Data Variables - Log up three custom variables of a date data type.
Events to LogSpecifies the event(s) where you want the configured custom variables to be logged:
  • OnPreExecute (Start) - Logs all custom variables at the start of the selected logging scope(s).
  • OnPostExecute (End) - Logs all custom variables at the end of the selected logging scope(s).
  • OnError - Logs all custom variables when an error occurs.
  • OnWarning - Logs all custom variables when a warning occurs.
Logging ScopeSpecifies the scope of custom logging:
  • Package Level - Logs custom variables at the Package Level for the events specified in the Events to Log section.
  • Task Level - Logs custom variables for every task using for the events specified in the Events to Log section.

Warning Filtering tab

BI xPress Auditing Framework Wizard Warning Filtering

Use the Warning Filtering tab to define warnings you want the Auditing Framework to ignore when they are encountered. Excluding warnings improves package execution times and lower database use. It also reduces logging clutter by only logging messages important to the user. During package execution, BI xPress ignores any warning that match a filter. After you have configured the Warning Filtering tab with your desired settings, select Connection Information, Logging Options, or Advanced User Defined Logging, to continue configuring the Auditing Framework. Select Next to open the summary page, and begin the process of adding/re-applying your Auditing Framework.

Important:  You can only access the Warning Filtering page if you can connect to the BI xPress database with the information configured in the Connection Information tab.

Warning Editor

BI xPress Auditing Framework Wizard Warning Editor

The Warning Editor allows you to filter out some of the common warnings from being reported. When creating a new warning filter, you select between a code or keyword comparison. The comparison type chosen dictates whether to use code or keyword property. The description property provides you with a property for describing the warning filter. The description property is not used to determine warning exclusion and has a character limit of 1,000. 

Filter warning in one of the following ways:

  • Alert Code - Removes all warnings based on the warning's SSIS ErrorCode.
  • Alert Keyword - Removes all warnings based on a specific word or phrased within its ErrorDescription.

The following options are configurable in the Warning Editor:

OptionDescription
ServerThe name of the server with the BI xPress database. This panel uses the information configured within the Connection Information tab by default.
Windows AuthenticationWhen selected, the warning editor uses Windows Authentication. When unselected, the warning editor uses SQL Server authentication.
  • User ID - The SQL Server username used to connect to the BI xPress database.
  • Password - The SQL Server password used to connect to the BI xPress database.
DatabaseThe name of the database that contains the BI xPress Framework architecture. This panel uses the information configured within the Connection Information tab by default. Note:  You will be prompted to generate the auditing framework within the selected database if the database does not contain the BI xPress Framework Architecture.
Warning Editor TableContains the list of Alerts to filter out of execution logs in the BI xPress database:
  1. AlertID - The Unique Identifier within the BI xPress Database for the current Alert.
  2. FilterThisAlert - Select the alert(s) to filter out of the execution logs in the BI xPress database.
  3. AlertCode - Used to searched for all matching alerts and excluding them from being logged if searching by code.
  4. CompareAction - Used to change the Alert Filter's behavior for comparing alerts within a package execution.
  5. AlertKeyWords - Used to search the Alert Message for matches and exclude them from being logged if searching by keyword.
  6. AlertDescription - Your entered description or reason for filtering the alert.

Note:  All users have access to the warning filters present within a BI xPress database. Any changes made to the database's warning filters affect all users. Only delete warnings that will not have adverse affects on other users.  Having a large library of warnings will not affect package executions. Only the selected and applied filters could increase package performance.

Select Remove Auditing Framework from Project Options page

BI xPress Auditing Framework Wizard Select Remove Auditing Framework from Project Options

The Project Items Removal page specifies whether project items, such as the BI xPress project connection manager and parameters, are removed. The following options are available:

OptionDescription
Do not remove the project level connection manager and parametersLeaves the project connection manager and all parameters associated with the Auditing Framework within the SSIS Project. Use this option if a few packages are having the Auditing Framework removed and there are still some SSIS packages within the project using the project connection manager and parameters associated with the Auditing Framework.
Remove the project level connection manager and parametersRemoves the project connection manager and all parameters associated with the Auditing Framework within the SSIS Project.

Note:  The Project Items Removal page is available when you selected to Remove Auditing Framework from SSIS 2012 or above packages with the SSIS Project Location on the Select Packages page.

After configuring your options, select Next to open the Summary page and begin the process of removing the Auditing Framework.

Summary Page

BI xPress Auditing Framework Wizard Summary

The Summary page lists information, warnings, and errors that may occur during the application or removal of the Auditing Framework. Select Back to review your selections and make any necessary changes. Select Start to either add / re-apply or remove the Auditing Framework. 

Note:  Depending on how many packages you have selected, the application of the Auditing Framework could take anywhere from a few seconds to a few minutes. 

If the Close on successful completion option is enabled, the Auditing Framework add-in closes automatically if the process completed successfully without warnings.

Select Finish to close out of the Auditing Framework when you are through reviewing the Process Summary.

Auditing Framework from the SentryOne Workbench

Open the Auditing Framework component from the SentryOne Workbench. The Auditing Framework contains two sections that help configure functionality within the framework:

SectionDescription
Package SelectionThe package selection page controls the deployment mode behavior for SSIS 2012 and above packages.
Framework ConfigurationFramework Configuration is divided into five pages with Auditing Framework options:
  • Database Connection - Used to configure connection string information for the BI xPress database.
  • Connection Manager - Configuration properties used to determine the storing of the connection manager.
  • Package Grouping - Used to determine whether the selected packages belong to a package group.
  • Configuration Options - More in-depth options used to customize the Auditing Framework.
  • Logging Options - More options used to customize package logging. Warning Filtering - Used to configure which warnings should be ignored during package execution.

Selecting Packages 

Use the Select Packages page to select the packages you want to apply or remove the auditing framework. The Select Packages page also controls the deployment behavior mode for the Auditing Framework when working with SSIS packages 2012 and above.

Package Location

BI xPress Auditing Framework Workbench Package Location

The location of your package(s) where you want to Add, re-apply, or remove the Auditing Framework. You can select from the following locations:

Package LocationDescription
My ComputerThe Auditing Framework searches the File System, displaying all SSIS packages within each directory as you expand and collapse folders. Packages selected from this location have the Auditing Framework applied or removed using the Package Deployment Model.
SSIS ProjectThe Auditing Framework searches an SSIS Project File (*.dtproj), using the selected path, for SSIS Packages. If the packages selected are SSIS 2012 or above, then the selected packages have the Auditing Framework applied or removed using the Project Deployment Model.
SSIS SolutionThe Auditing Framework searches a Visual Studio Solution (*.sln), using the selected path, for SSIS Packages contained within SSIS Projects associated with the selected solution. Packages selected from this location have the Auditing Framework applied or removed using the Package Deployment Model.
SQL ServerThe Auditing Framework searches SQL Server, using the provided connection information, for SSIS Packages deployed. Packages selected from this location have the Auditing Framework applied or removed using the Package Deployment Model.
SSIS Package StoreThe Auditing Framework searches the SSIS Package Store, using the provided connection information, for SSIS Packages deployed. Using this location requires the SentryOne Workbench to be running as an Administrator. Packages selected from this location have the Auditing Framework applied or removed using the Package Deployment Model.
SSIS CatalogThe Auditing Framework searches the SSIS Catalog, using the provided connection information, for SSIS Packages deployed. Packages selected from this location have the Auditing Framework applied or removed using the Package Deployment Model.

Package Selection

BI xPress Auditing Framework Workbench Package Selection

Use the Package Selection screen to browse through the selected location, expand and collapse folders, and select all applicable SSIS packages where you want to Add, re-apply, or remove the Auditing Framework. After selecting your package(s), select Next to open the Database Connection page.

BI xPress Database

BI xPress Auditing Framework Workbench BI xPress Database

Use the BI xPress Database section to configure the Server information used by the Auditing Framework to connect to the BI xPress Database. The following options are available:

OptionDescription
Browse for existing BI xPress DatabaseOpens the Connect to Database window. Enter the server name and security information for the connection. Select OK to create the connection string for the server.

Note:  The information you enter in the Connect to Database window populates the other properties within the Database Connection section.
Create New BI xPress DatabaseCreates a new BI xPress database if there isn't a database that contains the BI xPress Framework architecture.
ServerThe name of the server where the BI xPress database is located.
DatabaseThe name of the database that contains the BI xPress Framework Architecture.
Windows AuthenticationSelect the Windows Authentication checkbox to use window authentication.

Note:  This option is selected by default. Unselect the Windows Authentication checkbox to use SQL Server Authentication. Enter the SQL Server username and password for your SQL Server connection.
Offline ModeSelect Offline Mode to create the Auditing Framework Connect Manager set it to Work Offline. Work Offline allows you to browse and continue developing your package when the BI xPress database is inaccessible.
Continue package execution on Auditing Framework database connection failureSelecting this option allows SSIS packages that contain the Auditing Framework to continue executing in the event of a connection failure.
Do not report failure if Auditing Framework failsSelect this option to hide all errors that occur due to the Auditing Framework.

After you have configured your BI xPress Database settings, you can continue to any of the following sections:

  • Package Connection
  • Package Grouping
  • Configuration Options
  • Logging Option

Select Apply Auditing Framework, or Remove Auditing Framework to begin the process of adding or removing the Auditing Framework.

Package Connection

Use the Package Connection section to configure to create a new, or use an existing, connection manager. The scope of the connection manager (package or project level) changes depending on the deployment model selected on the Select Packages page. The following deployment models are available:

Package Deployment Model

The Auditing Framework uses package connection managers if you selected SSIS 2005, 2008, or 2008 R2 Packages, or  you selected My Computer, SQL Server, SSIS Package Store, or Visual Studio Solution as the selected location on the Select Packages page. The following options are available:

OptionDescriptionImage
Create new connection managerCreates a new connection manager within each package with the specified connection manager name.BI xPress Auditing Framework Workbench Package Connection Create new connection manager
Use existing connection Select an existing connection manager from the first package selected.BI xPress Auditing Framework Workbench Package Connection Use existing connection

Note:  If the selected connection manager does not exist in all of the packages, the Auditing Framework will not be applied. If you're using an existing connection manager, the selected connection manager should point to a database that contains the BI xPress Framework Architecture.

Project Deployment Model

The Auditing Framework uses project connection managers if you selected SSIS packages 2012 and above and you selected SSIS Project as the selected location on the Select Packages page. The following options are available:

OptionDescriptionImage
Create new connection managerCreates a new connection manager at the project level with the specified connection manager name.BI xPress Auditing Framework Workbench Project Connection Create new connection manager
Use existing connectionSelect an existing project connection manager.BI xPress Auditing Framework Workbench Project Connection Use existing connection

After you have configured your Package Connection settings, you can continue to any of the following sections:

  • BI xPress Database
  • Package Grouping
  • Configuration Options
  • Logging Option

Select Apply Auditing Framework, or Remove Auditing Framework to begin the process of adding or removing the Auditing Framework.

Package Grouping

BI xPress Auditing Framework Workbench Package Grouping

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

Note:  The Auditing Framework currently groups packages based on package name. Updating package groups affects all executions.

After you have configured your Package Connection settings, you can continue to any of the following sections:

  • BI xPress Database
  • Package Connection
  • Configuration Options
  • Logging Option

Select Apply Auditing Framework, or Remove Auditing Framework to begin the process of adding or removing the Auditing Framework.

Configuration Options

BI xPress Auditing Framework Workbench Configuration Options

Use the Configuration Options section to specify whether to add a configuration file or parameters to your packages, and control the behavior of the Auditing Framework. The configuration options as well as the scope of the parameters (package or project level) change depending on the deployment model selected on the Select Packages page.

OptionDescription
Use ConfigurationsEnables the use of configurations.
Configuration TypeDisplays the current type of configuration that will be used.

Note:  The displayed configuration depends on your SSIS version, and the deployment model used by the selected packages. The following configurations are available:
  • Package Configuration File - Used if SSIS 2005, 2008, or 2008 R2 packages were selected.
  • Package Deployment Model - Used if you selected SSIS 2012 and above and selected My Computer, SSIS Solution, SQL Server, or SSIS Package Store as the selected location on the Select Packages page.
  • Used if you selected SSIS 2012 and above SSIS Packages, and selected SSIS Project as the selected location on the Select Packages page
Configuration FolderThe location where the configuration file is saved.
Overwrite if the configuration file already existsWhen selected, overwrites the configuration file if it already exists.
Add configuration file entry to enable or disable the Auditing FrameworkAdds a configuration file that lets you manually turns off the Auditing Framework.
Add configuration file entry to enable or disable variable, parameter, and connection loggingAdds a configuration file that lets you manually turn off the logging of variable, parameter, and connection managers.
Add configuration file entry to enable or disable real-time Data Flow monitoringAdds a configuration file that lets you manually turn off the Auditing Framework's real-time Data Flow monitoring.
Add configuration file entry to enable or disable logging for all warningsAdds a configuration file that minimizes the amount of logging by not logging warning that occur during execution.
Add configuration file entry to specify variable and connection logging filteringAdds a configuration file that lets you manually mask sensitive values in variables when you view executions in the Monitoring Console.

After you have configured your Configuration Options settings, you can continue to any of the following sections:

  • BI xPress Database
  • Package Connection
  • Package Grouping
  • Logging Option

Select Apply Auditing Framework, or Remove Auditing Framework to begin the process of adding or removing the Auditing Framework.

Logging Options

Use the Logging Options section to customize how the Auditing Framework controls Basic, Data Flow, Connection, Parameter, Variable, and Advanced User Defined Logging Options. The following options are available:

Basic Options

BI xPress Auditing Framework Workbench Basic Options

The Basic section controls logging options that do not control details regarding Data Flow, Connection, Parameter, and Variable, and Advanced User Defined Logging Sections.

OptionDescription
Script LanguageThe programming language that you want the Auditing Framework to use within Script Tasks.
Maximum Loop IterationsThe number of loops within a For or For Each Loop that you want to document. Setting this value to 0 means to log all iterations.
Log WarningsControls whether to log warnings that are encountered during executions in the BI xPress database.

Data Flow Logging Options

BI xPress Auditing Framework Workbench Data Flow Logging Options

The Data Flow Logging section controls the logging details in each Data Flow in the SSIS Package(s). 

OptionDescription
Real-time Data Flow MonitoringControls whether the Auditing Framework monitors data flows in real-time.

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 LoggingControls whether the Auditing Framework captures row counts flowing inside data flows. When enabled, each data flow is modified to track row count data.
Source Row Count LoggingWhen Row Count Logging is enabled, this option controls whether the Auditing Framework tracks the number of rows that are coming from source components.
Destination Row Count LoggingWhen Row Count Logging is enabled, controls whether the Auditing Framework tracks the number of rows that are going to destination components.
Source SQL Statement LoggingWhen Row Count Logging is enabled, controls whether the Auditing Framework logs SQL Statements used to extract data. This information can be viewed using BI xPress Extract / Load Detail Report.
Connection Detail LoggingWhen Row Count Logging is enabled,  controls whether the Auditing Framework logs details for each source and destination component regarding the connections used.

Connection, Parameter, and Variable Logging Options

BI xPress Auditing Framework Workbench Connection, Parameter, and Variable Logging Options

The Connection, Parameter, and Variable Logging section controls logging details for connection managers, parameters, and variables in each SSIS Package.

OptionDescription
Connection LoggingControls whether the Auditing Framework logs every connection manager's connection string value during run-time.
Variable LoggingControls whether the Auditing Framework 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 is started and when the package execution completes.
Object Variable LoggingWhen Variable Logging is enabled, controls whether the Auditing Framework logs object variables.
Initial Parameter Value LoggingWhen Variable Logging is enabled, controls whether the Auditing Framework logs the initial parameter values.
Variable Logging TypeWhen Variable Logging is enabled, controls whether the Auditing Framework changes the behavior of variable logging to log all variables, or only variables used within For Each loops.

Advanced User Defined Logging Options

BI xPress Auditing Framework Workbench Advanced User Defined Logging Options

The Advanced User Defined Logging section controls the logging of specific variables within the Auditing Framework.

OptionDescription
Custom Package Level Variable LoggingEnables custom variable logging and allows the Auditing to explicitly specify the logging of specific variables.
Numeric VariablesWhen Custom Package Level Variable Logging is enabled, the three Numeric Variable text boxes allow you to log up to three custom numeric variables.
String VariablesWhen Custom Package Level Variable Logging is enabled, the three String Variable text boxes allow you to log up to three custom string variables.
Date VariablesWhen Custom Package Level Variable Logging is enabled, the three Date Variable text boxes allow you to log up to three custom date variables.
Log on these eventsWhen Custom Package Level Variable Logging is enabled,  allows you to specify the event(s) where you want the configured variables to be logged:
  • OnPreExecute (Start) - When enabled, all custom variables are logged at the start of the selected logging scope(s).
  • OnPostExecute (End) - When enabled, all custom variables are logged at the end of the selected logging scope(s).
  • OnError - When enabled, all custom variables are logged when an error occurs.
  • OnWarning -  When enabled, all custom variables are logged when a warning occurs.
Custom Variable Logging Scope(s)When Custom Package Level Variable Logging is enabled, Custom Variable Logging Scope allows you to specify the scope of custom logging:
  • Package Level - When enabled, custom variables are logged at the Package Level for the events specified in the Events to Log section.
  • Task Level -  When enabled, custom variables are logged for every task using for the events specified in the Events to Log section.

After you have configured your Logging Options settings, you can continue to any of the following sections:

  • BI xPress Database
  • Package Connection
  • Package Grouping
  • Configuration Options

Select Apply Auditing Framework, or Remove Auditing Framework to begin the process of adding or removing the Auditing Framework.

Warning Filtering

BI xPress Auditing Framework Workbench Warning Filtering

Use Warning filtering to exclude specific warnings from logging.  Excluding warnings improves package execution times and lowers database use. It also reduces logging clutter by only logging messages you find important.  

Filter warnings in code or by keyword:

Filter warning byDescription
CodeRemove all warnings based on the warning's SSIS Alert Code.
KeywordRemove all warnings based on a specific word or phrase within its Alert Message.

Note:  With Warning Filtering enabled, BI xPress ignores any warning that matches the comparison type during package execution.

When adding a new Warning Filter, you select to filter between a code or keyword comparison. The comparison type that you select determines the property that's used. The description property lets you enter a description for the warning filter. Select the checkboxes to apply warning filters to your desired package(s). Select Is Default to make the selected warning apply by default. 

Note:  The description filter does not determine warning exclusions and has a character limit of 1,000.

Note:  All users have access to the warning filters present within a BI xPress database. Any changes made to the database's warning filters affect all users. Only delete warnings that will not have adverse affects on other users.  Having a large library of warnings will not affect package executions. Only the selected and applied filters could increase package performance.

Configuration File Options

Use the configuration file to control most of the options in the BI xPress Auditing Framework. 

Note:  This section explains how to generate XML configuration files, but similar techniques can be applied if you are using SQL Configuration with the Path and Value property.

Configurable Variables and Parameters

Variable / PropertyDescription
Package\User::varSSISOps_DisableBIxAuditingThis variable controls the execution of all event handlers for BI xPress Auditing Framework.  Set this variable to True or -1 if you want to disable auditing framework related code inside all event handlers. Set Package.LoggingMode=2 along with this variable if you want to disable the framework completely.
Property: Package.LoggingModeThis property controls native SSIS Logging:
  • 0 = UseParentSetting
  • 1 = Enable Logging
  • 2 = Disable Logging

Note:  Setting this property to disables real-time Data Flow monitoring.

2017.1 and later:
  • Package\User::varSSISOps_DisableVarLogging
2016.4 and earlier:
  • OnPreExecute\User::varSSISOps_DisableVarLogging
  • OnPostExecute\User::varSSISOps_DisableVarLogging
This variable controls the logging of variable values (in PreExecute and PostExecute Event Handlers in 2016.4 and earlier). Set this variable to True or -if you want to disable variable logging (on package pre-execute and/or post-execute in 2016.4 and earlier).
Property: OnVariableValueChanged.DisableSet this property to true or -1 if you want to disable logging of variable changes.
2017.1 and later:
  • Package\User::varSSISOps_DisableConnLogging
2016.4 and earlier:
  • OnPreExecute\User::varSSISOps_DisableConnLogging
  • OnPostExecute\User::varSSISOps_DisableConnLogging
Set this variable to True or -1 if you want to disable connection logging (on package start/stop events (PreExecute/PostExecute) in 2016.4 and earlier).

OnVariableValueChanged\User::varSSISOps_FilteredVars OnPreExecute\User::varSSISOps_FilteredVars OnPostExecute\User::varSSISOps_FilteredVars

Set this variable to a list of variables you want to mask during logging process. You can use a comma separated list and you can use wild-card for pattern matching names that are case in-sensitive. This option is used to mask variable values during logging process. This option is helpful when you have sensitive data (e.g. varFTP_Password) or the variable value is extremely large to display (e.g. varXML_Document). When masking is enabled, "****" is logged in the Bi xPress Audit Database rather than the actual value of the variable.

Note:  You can configure a list of variables you want to mask outside the package by configuring "varSSISOps_FilteredVars". There are 3 places where you have to set this value (OnPreExecute, OnPostExecute, OnVariableValueChanged).
2017.1 and later:
  • Package\User::varSSISOps_FilteredConns
2016.4 and earlier:
  • OnPreExecute\User::varSSISOps_FilteredConns
  • OnPostExecute\User::varSSISOps_FilteredConns
Set this variable to a list of Connection Managers you want to mask ConnectionStrings during the logging process inside an OnPreExecute Event Handler. You can use a comma separated list and you can use wild-card * for pattern matching names that are case in-sensitive. This option is helpful when you want to hide certain ConnectionStrings or file paths from users who are using BI xPress Monitoring Console or Reports. When masking is enabled, "****" is logged in the BI xPress Audit Database rather than actual ConnectionString of the specified connection managers.

Note:  You can configure a list of variables you want to mask outside the package by configuring "varSSISOps_FilteredConns". In versions 2016.4 and earlier, there are 2 places where you have to set this value (OnPreExecute, and OnPostExecute).
2017.1 and later:
  • Package\User::varSSISOps_DisableDataSrcInfoLogging
2016.4 and earlier:
  • OnPostExecute\User::varSSISOps_DisableDataSrcInfoLogging
Set this variable to true or -1 if you want to disable logging of Data source related information (for example, ConnectionName, TableName, SQLCommand, and FileName).
2017.1 and later:
  • Package\User::varSSISOps_DisableDFTRowCountLogging
2016.4 and earlier:
  • OnPostExecute\User::varSSISOps_DisableDFTRowCountLogging
Set this variable to true or -1 if you want to disable logging of row count attached with source and destination components.

Note:  When you disable row  count logging using configuration it also disables data source information logging.
2017.1 and later:
  • Package\User::varSSISOps_MaxVarCharsToLog
2016.4 and earlier:
  • OnVariableValueChanged\User::varSSISOps_MaxVarCharsToLog
  • OnPreExecute\User::varSSISOps_MaxVarCharsToLog
  • OnPostExecute\User::varSSISOps_MaxVarCharsToLog
Set this variable to configure how many characters will be logged for each variable. Default variable length for logging is 4000, anything after that will be truncated.  A value from zero to 2147483647. In versions 2016.4 and earlier, there are 3 places where you have to set this value (OnPreExecute, OnPostExecute, and OnVariableValueChanged)

Important:  Most of the options in this section only apply to Auditing Framework using BI xPress v3.2.0 or higher. If you are getting warnings or errors about package corruption, or missing objects because one or more objects referred by configuration were not found in your package, then you are likely running an older version of auditing framework or certain auditing functionality are disabled. You can eliminate the following warnings/errors by changing Package Property SuppressConfigurationWarnings to True. To change SuppressConfigurationWarnings property right click package designer surface in BIDS and select Properties. Warning loading MyPackage.dtsx: The package path referenced an object that cannot be found: \Package.Variables[User::varSSISOps_DisableBIxAuditing].Properties[Value]. This occurs when an attempt is made to resolve a package path to an object that cannot be found. C:\SSIS\MyPackage.dtsx BI xPress Visual Studio Error example

Enable/Disable auditing framework completely without modifying packages

BI xPress v3.2.0 introduced several options to configure various BI xPress Auditing Framework features from the configuration file. The following sample config file illustrates how to enable/disable auditing framework using two parameters. Make sure your packages have the latest version of auditing framework to use this feature (must be modified with v3.2.0 or higher).

Important:  When you disable auditing framework, no data will be generated for any package configured using the following config file.

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!--  Following variable controls all auditing options except real-time Data Flow monitoring.
       0 = Enable Auditing Framework
       -1 = Disable Auditing Framework -->
       <Configuration ConfiguredType="Property" Path="\Package.Variables[User::varSSISOps_DisableBIxAuditing].Properties[Value]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
       <!-- Following Property controls LogProvider Settings.
       0 = UseParentSetting,
       1 = EnableLogging
       2 = DisableLogging -->
       <Configuration ConfiguredType="Property" Path="\Package.Properties[LoggingMode]" ValueType="Int32">
               <ConfiguredValue>1</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

Enable/Disable real-time Data Flow monitoring

Real-time Data Flow monitoring can be disabled by setting LoggingMode Property to and enabled by setting the property to 1 (0=UseParent, 1=Enable, 2=Disable). 

Important:  When you disable real-time monitoring you can't view the Data Flow diagram in Monitoring Console.

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!-- Following Property controls LogProvider Settings.
       0 = UseParentSetting,
       1 = EnableLogging
       2 = DisableLogging -->
       <Configuration ConfiguredType="Property" Path="\Package.Properties[LoggingMode]" ValueType="Int32">
               <ConfiguredValue>1</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

Enable/Disable variable logging

Use the following configuration options to enable or disable variable logging.

2017.1 and later:

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!-- Following setting controls variable change history logging
       0 = Enable Logging of variable changes
       -1 = Disable Logging of variable changes -->
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnVariableValueChanged].Properties[Disable]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
       <!--  Following variable controls variable logging when package starts and ends [OnPreExecute = Package Start, OnPostExecute=Package End]
       0 = Enable Logging
       -1 = Disable Logging -->
       <Configuration ConfiguredType="Property" Path="\Package.Variables[User::varSSISOps_DisableVarLogging].Properties[Value]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

2016.4 and earlier:

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!-- Following setting controls variable change history logging
       0 = Enable Logging of variable changes
       -1 = Disable Logging of variable changes -->
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnVariableValueChanged].Properties[Disable]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
       <!--  Following variable controls variable logging when package starts and ends [OnPreExecute = Package Start, OnPostExecute=Package End]
       0 = Enable Logging
       -1 = Disable Logging -->
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnPreExecute].Variables[User::varSSISOps_DisableVarLogging].Properties[Value]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnPostExecute].Variables[User::varSSISOps_DisableVarLogging].Properties[Value]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

Enable/Disable connection logging

Use the following configuration options to enable or disable connection logging.

2017.1 and later:

<?xml  version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!--  Following variable controls connection logging when package starts and ends [OnPreExecute = Package Start, OnPostExecute=Package End]
       0 = Enable Logging
       -1 = Disable Logging -->
       <Configuration ConfiguredType="Property" Path="\Package.Variables[User::varSSISOps_DisableConnLogging].Properties[Value]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

2016.4 and earlier:

<?xml  version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!--  Following variable controls connection logging when package starts and ends [OnPreExecute = Package Start, OnPostExecute=Package End]
       0 = Enable Logging
       -1 = Disable Logging -->
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnPreExecute].Variables[User::varSSISOps_DisableConnLogging].Properties[Value]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnPostExecute].Variables[User::varSSISOps_DisableConnLogging].Properties[Value]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

Mask a list of variables during logging process

You can specify a list of variables so they are stored as masked values inside the Auditing Database. 

Note:  There are three places where the variable value is logged. You have to repeat variable lists multiple times for each event handler as shown below.

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!-- Following parameter contains list of variables to be masked from logging
       you can use comma separated list you can use wild-card "*" for pattern matching
       names are case in-sensitive -->
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnVariableValueChanged].Variables[User::varSSISOps_FilteredVars].Properties[Value]" ValueType="String">
               <ConfiguredValue>*obj*,file*</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnPreExecute].Variables[User::varSSISOps_FilteredVars].Properties[Value]" ValueType="String">
               <ConfiguredValue>*obj*,file*</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnPostExecute].Variables[User::varSSISOps_FilteredVars].Properties[Value]" ValueType="String">
               <ConfiguredValue>*obj*,file*</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

Mask ConnectionString during logging process

You can specify a list of Connection Managers so ConnectionStrings are stored as masked values inside the Auditing Database. 

Note:  There are two places where connections are logged. You have to repeat Connections multiple times for each event handler as shown below.

2017.1 and later:

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!-- Following parameter contains list of connections to be filtered from logging
       you can use comma separated list you can use wild-card "*" for pattern matching
       names are case in-sensitive -->
       <Configuration ConfiguredType="Property" Path="\Package.Variables[User::varSSISOps_FilteredConns].Properties[Value]" ValueType="String">
               <ConfiguredValue>*FTPConn*,*HRSalary*</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

2016.4 and earlier:

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!-- Following parameter contains list of connections to be filtered from logging
       you can use comma separated list you can use wild-card "*" for pattern matching
       names are case in-sensitive -->
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnPreExecute].Variables[User::varSSISOps_FilteredConns].Properties[Value]" ValueType="String">
               <ConfiguredValue>*FTPConn*,*HRSalary*</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnPostExecute].Variables[User::varSSISOps_FilteredConns].Properties[Value]" ValueType="String">
               <ConfiguredValue>*FTPConn,*HRSalary*</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

Enable/Disable DataSource logging (i.e. ConnectionStrings, TableName, SQL Statement, and FileName)

Use the following settings to enable or disable DataSource logging . DataSource logging captures source/target connection and data source related information. Data source and target information can be seen on Extract/Load Detail Report.

2017.1 and later:

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!-- Following variable controls datasource information logging
       0 = Enable Logging
       -1 = Disable Logging -->
       <Configuration ConfiguredType="Property" Path="\Package.Variables[User::varSSISOps_DisableDataSrcInfoLogging].Properties[Value]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

2016.4 and earlier:

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!-- Following variable controls datasource information logging
       0 = Enable Logging
       -1 = Disable Logging -->
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnPostExecute].Variables[User::varSSISOps_DisableDataSrcInfoLogging].Properties[Value]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

Enable/Disable row count logging

When row count logging is enabled BI xPress generates code to capture row counts for each source and target inside the Data Flow. If you want to disable this without reapplying auditing framework then use the following configuration file setting.

Important:  Disabling row count logging using the configuration file also disables datasource information logging.

2017.1 and later:

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!--  Following variable controls row count logging
       0 = Enable Logging
       -1 = Disable Logging -->
       <Configuration ConfiguredType="Property" Path="\Package.Variables[User::varSSISOps_DisableDFTRowCountLogging].Properties[Value]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

2016.4 and earlier:

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!--  Following variable controls row count logging
       0 = Enable Logging
       -1 = Disable Logging -->
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnPostExecute].Variables[User::varSSISOps_DisableDFTRowCountLogging].Properties[Value]" ValueType="Boolean">
               <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

Change number of characters to log for variable value

Starting from v3.2.0 BI xPress doesn't log full values of variables (by default only 4000 characters are logged) to increase logging performance. To change this behavior, and to log full values of variables, or log different numbers of characters, use the following configuration parameters.

BI xPress 2017.1 and later:

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!--  Default variable length for logging is 4000 anything after that will be truncated.
       A number from zero to 2147483647 = Log full variable value. There is one place where you have to set this value (see below) -->
       <Configuration ConfiguredType="Property" Path="\Package.Variables[User::varSSISOps_MaxVarCharsToLog].Properties[Value]" ValueType="Int32">
               <ConfiguredValue>4000</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

BI xPress 2016.4 and earlier:

<?xml version="1.0"?>
<DTSConfiguration>
       <DTSConfigurationHeading>
               <DTSConfigurationFileInfo GeneratedBy="PW\User1" GeneratedFromPackageName="" GeneratedFromPackageID="" GeneratedDate="7/13/2011 12:37:17 AM" />
       </DTSConfigurationHeading>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_BIXPRESS_1].Properties[ConnectionString]" ValueType="String">
               <ConfiguredValue>Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BIxPress;Data Source=(local);Auto Translate=False;</ConfiguredValue>
       </Configuration>
       <!--  Default variable length for logging is 4000 anything after that will be truncated.
       A number from zero to 2147483647 = Log full variable value. There are 3 places where you have to set this value (see below) -->
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnPreExecute].Variables[User::varSSISOps_MaxVarCharsToLog].Properties[Value]" ValueType="Int32">
               <ConfiguredValue>4000</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnPostExecute].Variables[User::varSSISOps_MaxVarCharsToLog].Properties[Value]" ValueType="Int32">
               <ConfiguredValue>4000</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.EventHandlers[OnVariableValueChanged].Variables[User::varSSISOps_MaxVarCharsToLog].Properties[Value]" ValueType="Int32">
               <ConfiguredValue>4000</ConfiguredValue>
       </Configuration>
</DTSConfiguration>

How do I performance optimize my N-Tier SSIS Packages for use with the Auditing Framework?

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

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

Complete the following steps to design your parent package(s) for optimal 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.Note:  You can also elect to create an empty event handler for the OnError event if you don't want 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.

Important:  The advantage of this approach is that each package will log exactly what it generates and does not include any Auditing Framework details that belong to one of its child packages. This will also let the user 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 will prevent the Monitoring Console from being able to view the Execute Package Task start and end time and will display them as though they were not executed at all. That being said, the Sequence Container will display its status normally. This is a small price to pay for an exponential increase in package performance.