BI xPress Auditing Framework

Auditing Framework

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

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.

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

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.

Select Action Page

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

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

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

Package Selection

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

Scanning Options window

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

Options Page

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

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:

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:

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:

Logging Options tab

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

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

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:

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:

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:

Warning Filtering tab

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.

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:

Select Remove Auditing Framework from Project Options page

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:

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

Summary Page

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. 

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:

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

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

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:

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:

Option	Description	Image
Create new connection manager	Creates a new connection manager within each package with the specified connection manager name.
Use existing connection	Select an existing connection manager from the first package selected.

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:

Option	Description	Image
Create new connection manager	Creates a new connection manager at the project level with the specified connection manager name.
Use existing connection	Select an existing project connection manager.

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

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.

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

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.

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

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

Data Flow Logging Options

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

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.

Advanced User Defined Logging Options

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

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

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:

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. 

Configuration File Options

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

Configurable Variables and Parameters

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

<?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 2 and enabled by setting the property to 1 (0=UseParent, 1=Enable, 2=Disable). 

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

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

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.

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.