BI xPress Config Tools

Config Editor

IconDescription
BI xPress Config Editor IconThe Config Editor allows you to open existing configurations from XML files or SQL Table. Use this tool to edit, search, and save your changes easily.

Feature Highlights

  • Edit configuration files quickly
  • Edit the properties of multiple configuration files at one time
  • Organize configured properties into an easy to read grid view

Note:  You must use the SentryOne Workbench (32-bit) when modifying SSIS configuration files on a machine that only contains SSDT for Visual Studio 2015.

Important:  After installing the SentryOne Workbench with BI xPress, all *.dtsConfig file extensions will be associated with the BI xPress Configuration Editor.

Open the Configuration Editor through one of the following methods:

Method Description
From the WorkbenchOpen the SentryOne Workbench and select the Config Editor feature.
From BIDS/Visual Studio/SSDTIn Visual Studio, select the BI xPress Toolbar and then select Quick Config Edit.
From the File SystemDouble click any *.dtsConfig file in Windows Explorer.

Using Config Editor

BI xPress Configuration Editor window

All config entries are listed in Grid View by default. Grid view makes it easy to view and edit config entries. You can also use the grid view to search and replace values.

BI xPress Configuration Editor window XML view

The Config Editor also has an XML view. Select the file you want to edit from the left side file list and then select the XML View tab to open the XML view.

Note:  The XML view is only available for XML configuration files.

Search and Replace in multiple configuration files

Use the Search and Replace dialog box to quickly replace values in hundreds of config files. The Search and Replace dialog box provides the following options:

OptionDescription
SearchFind any matching value in the config entry from any file/filter listed in the files/filter list.
Search and ReplaceFind any matching value in the config entry and replace them with user defined values from any file/filter listed in the files/filter list.
Search and Replace for specific propertyFind and Replace any matching value in the config entry for a specific property name.

Filter your information by entering a specific search term in the search box. Use the Config Editor search by Property Name, Value or Configured Object Name.

When you edit values, the save option is enabled and the row color changes to light orange. Select Save to save the changes to all edited files at once. This bulk edit feature makes it easy to edit multiple files. To edit files you select single/multiple files (or SQL filter) on left hand side, or select multiple items. Select the header check box to select all items.

Config Manager

IconDescription
BI xPress Config Manager iconUse the Config Manager, to create secure configurations and add or edit configuration values. You can also generate multiple shared configurations for selected connections and adjust naming conventions using the smart rename feature.

Feature Highlights

  • Automatic configuration file generation for multiple packages/connections using split configuration option
  • Add/Edit Configuration for multiple packages
  • Encrypt password/sensitive properties in configuration xml file or SQL configuration table.
  • Allow editing of configured value in property grid
  • Allow filtering properties from property grid
  • Simple mode to configure most common properties for Connections and Variables
  • Support for placeholders in Configuration Name, File Path or Table Filter
  • Copy/Paste Configurations
  • Editing package configurations outside Visual Studio without opening packages in BIDS.
  • Editing package configurations for packages stored on SQL Server on remote machine
  • Bulk property edit

Note:  You must use the SentryOne Workbench (32-bit) to modify an SSIS package's configuration file(s) on a machine that only contains SSDT for Visual Studio 2015.

Creating Configs 

Add/Edit SSIS Configuration for multiple packages

Instead of creating a configuration for each package, you can create the same configuration for multiple packages with the Secure Config wizard which will modify many packages at once. Create the same configuration for multiple packages by completing the following steps:

1. Select the packages you want to modify in the Solution Explorer. 

Note:  Select CTRL+Left Click to select an individual item or SHIFT + Left Click for range selection.

2.  Select Add to all and continue to the configuration screen.

Note:  Add to all is only visible when multiple packages are selected.

3. Specify the new configuration properties on the Configuration type screen. Select Next to continue to the Property selection screen.

4. Select the property you want to configure. Select Next to continue to the review screen.

5. Review your selections and then select Start to begin the configuration process.

Note:  These steps insert the same configuration into all of the selected packages.

Copy/Paste Configuration

The Copy/Paste feature allows you to copy configurations from one package to another. Copy any confirguartion from one package to another by completing the following steps:

1. Select the packages you want to modify in the Solution Explorer. 

Note:  Select CTRL+Left Click to select an individual item or SHIFT + Left Click for range selection.

2. Verify that multiple packages are listed with their configurations.

3.   Right click on the configuration you want to copy to the other package(s) and select Copy. Right click the package where you want to copy the configuration, and then select Paste. Repeat this step for all the packages where you want to change the configuration.

4. Close the wizard to open the saving prompt, and then select Save to save your changes.

Create separate SSIS configurations for all connections (Split SSIS Configurations)

Create separate configurations for each connection automatically by completing the following steps:

Note:  Creating multiple configurations for your selected connections is only available for XML and SQL Configuration types when using the Split option. The Split option is only visible during the Add configuration operation. Once the configuration is created you cannot split. When you select the Split option you cannot select variables on the property selection screen. Variables are only visible when Split option is unchecked.

1. Open the Secure Configuration Wizard for the package you want to modify.

2. Select Add to create a new configuration.

3. Select the configuration type (XML or SQL) and enter your location settings on the configuration type screen.

4. Select split configuration and then select by connection name from the drop down list. Select Next to continue.BI xPress SSIS Configuration Wizard Split Configuration option

5. Select the desired properties that you want to configure for each connection. Select Next to continue. 

Note:  You can select Switch to Simple Mode to hide properties and create a compact list for on the ConnectionString property.BI xPress SSIS Configuration Wizard Switch to Expert Mode button

Note:  If you select Switch to Advanced Mode, you can use the property filter option to filter your desired properties into a selectable list. Once you have your list, you can select all of your desired properties by selecting the root node.

6. Review your selections and select Start to begin creating configuration entries or XML configuration files for your connection.

Search and Replace

Use the Configuration Manager's Search and Replace (Bulk Edit) feature to update up to hundreds of configuration files. Perform updates on all visible nodes, or filter the properties and then replace the desired properties with a new value. Complete the following steps:

1. Filter any unnecessary items before starting the bulk update. 

Note:  This step is optional. The following image displays the varying options that are available to filter unwanted nodes from the tree list.

BI xPress SSIS Configuration Wizard Filter By drop down

2. Select Search/Replace (Bulk Edit) to open the Search and Replace dialog box.

BI xPress Search and Replace window

3. Select the action type that you want to apply. Select Search to begin your selection. 

Note:  The following options are available:

OptionDescription
SearchFind any matching values within the currently displayed configuration entries from any file listed.
Search and ReplaceFind any matching values within the currently displayed configuration entries and then replace the values with the listed replacement value.
Search and Replace for Specific propertyFind any matching values within the currently displayed configuration entries that matches a specific property name and then replace the values with the listed replacement value.

Secure Configs

It's common to store passwords or other sensitive information in configurations. Currently SSIS does not perform encryption on any values stored in configurations. Configuration Manager allows you to securely encrypt any SSIS package properties. Use the Configuration Manager to encrypt your SSIS Package properties by completing the following steps:

1. Open the desired SSIS package where you want to add secure configurations in Visual Studio.

2. Right-click on the package within the Solution Explorer and select Package Configurations (BI xPress).

3. Select Enable Package Configurations, and then select Add.BI xPress SSIS Configuration Wizard Configuration list for selected packages

4. Select the configuration type, path, and then select Next to continue to the property selection screen.

5. Select an encryption mode from the drop-down list. The following options are available:BI xPress SSIS Configuration Wizard Encryption Mode

OptionDescription
No EncryptionNo value is encrypted, and the package is in the regular configuration. This option is selected by default.
Encrypt Sensitive (Auto)All password properties are encrypted automatically. Any property without sensitive information is stored as plain text.
Encrypt Sensitive (Manual)Password properties are encrypted when the Encrypt column is checked for selected properties. Any property without sensitive information is stored as plain text.
Encrypt Full Value (Auto)Encrypt full value of all configurable properties.
Encrypt Full Value (Manual)Encrypt full value of configurable properties only when the Encrypt column is checked.

Note:  Depending on the encryption mode selected, you can manually encrypt a property, or allow BI xPress to choose for you. Any property with a lock icon next to it indicates that the property will be encrypted when it's saved to the configuration file.

6. Update your desired properties, and select to include the properties within the configuration file. Select Next to continue to the Summary Screen. Select Start to begin creating the encrypted configuration file.

7. Go to the configuration file location and open the new dtsConfig file to inspect the XML.

Success: Your sensitive information is encrypted.BI xPress Encrypted Package example

Important:  When you select any type of encryption mode, Event Handler code is generated to perform transparent decryption. Select Remove Secure Config to remove the secure configuration code.

Shared Configs

You can create configurations through one of the following methods:

  • Split configurations by package name
  • Split configurations by connection name
  • Split configurations uniquely by data source name (Database name or filename)

Splitting configurations by package name and connection name are the most time consuming to maintain because you have to update several areas when connection settings change. Creating a shared configuration is the most effective way of reusing configurations. Imagine that you have 100 packages and each package has 10 connections. In this case you would end up maintaining 1000 configuration entries if you select to split configurations by package name, or split configurations by connection name. Considering this scenario, using either method would be time consuming and error prone.

When you create shared configurations you end up maintaining fewer configuration entries based on database or file name which produces a lot less configuration entries.

Important:  Shared configurations have to name the connections the same way in all packages because the configuration refers to the connection by name. For example, if one package names its connection adventureworks_connection and the other package connection_adventureworks then it will not work even though both have the exact same ConnectionString.

Create a Shared Configuration file by completing the following steps:

1. Open the Secure Configuration Wizard for a single, or multiple packages. 

Note:  Select CTRL + Left Click for an individual item selection or SHIFT + Left Click for range selection.

2. Select Add to all to create shared configurations for all selected packages.

Note:  Select Add to selected to modify a single package.

3.  Select the configuration type (XML or SQL), and enter the location settings on the configuration type screen.

4. Select the split configuration option. Select by connection name from the drop-down list, and select Generate shared configurations.BI xPress SSIS Configuration Wizard Select configuration type and other settings

5. Select Smart Rename Options open the Smart Rename Options window and review your settings. Select the naming convention for connections (and configurations). Select Ok to save your settings. BI xPress Smart Rename Options window

Note:  This window displays a preview of connections before and after the rename.

6. Select Next to open the property selection screen. Select the desired Properties you want to configure for each Connection, or you can switch to Simple Mode to hide properties and create a compact list for only ConnectionString property. Select Next to continue.

Note:  If you select Switch to Advanced Mode, you can use the property filter option to filter your desired properties into a selectable list. Once you have your list, you can select all of your desired properties by selecting the root node.

7. Review your selection and then select Start to process the selected connections, and create Configuration entries in the Database, or create XML Configuration files. 

Note:  Connection names in all selected packages are standardized to match with smart rename naming convention.