BI xPress Package Tools

Package Deployment Wizard

IconDescription
BI xPress Deployment IconThe Package Deployment Wizard in BI xPress gives you more options to deploy SSIS packages. Deploy multiple packages at once without having to deploy an entire project. Use the 2 way deployment feature to copy packages from a target location to a source.

Feature Highlights

  • Deploy multiple packages at once, even from different projects
  • Copy XML configuration files to the target location during deployment
  • Handles deployment to file system, database, and package store
  • Override configurations and connections strings

Important:  Package Deployment can't deploy SSIS packages to the SSIS Package Catalog. The SentryOne Workbench (32-bit) must be used when deploying SSIS packages on a machine that only contains SSDT for Visual Studio 2015.

Using Deployment

Open the SSIS Deployment wizard by doing one of the following:

  • Select Deployment in the SentryOne Workbench
  • Open Visual Studio and select SentryOne > BI xPress > Deploy SSIS Package  
  • Open Visual Studio, right click your package in the solution explorer, and then select Deploy SSIS Package

Deployment Options

BI xPress Package Deployment Wizard Deployment Options

BoxDescription
Package Deployment OptionsOverwrite existing packages that are already deployed or skip deployment of packages that already exist in the targeted environment. You can also specify the CheckPoint folder location, and the Package protection level.
Configuration Deployment OptionsSelect the package configurations you want to deploy, and specify the location to deploy the package configurations. You can also specify credentials to access remote file systems.

After configuring your settings, select Next to continue to the Source and Target Selection page.

Source and Target Selection

BI xPress Package Deployment Wizard Source and Target Selection

Use the Source and Target Selection page to specify the location of the SSIS Packages that you want to deploy, and to specify the target location. Use the Location drop-down lists to select one of the following options:

OptionDescription
File SystemSpecify the correct path for the source and/or target location. Note:  You can specify a remote server as the locations for the packages.
SQL ServerSpecify the server and user credentials.
SSIS Package StoreSelect the desired server and specify the path of the packages.

Note:  Select the funnel icon to apply filters to your selection.

Note:  You can right click the desired package in the Selected Packages window and select the View Selected Packages to open the package in Visual Studio and make any necessary changes.

After specifying the source and target locations, select Deploy to deploy the selected packages.

Note:  Select the Show Log check box to display the Package Deployment Process Log when your packages are deployed. The log displays a summary of the deployment process, a validation summary, and application log. Select Save Log to save the log for further review. Select Close to return to the package Deployment Wizard.BI xPress Package Deployment Wizard Process Log

Select Finish to close the package Deployment wizard.

Package Builder

IconDescription
BI xPress Package Builder IconThe Package Builder Wizard can create templates, and packages, and modify them based on selected tasks.

Feature Highlights

  • Create and share templates of frequently used packages
  • Recreate packages in minutes

Important:  If you are attempting to create, modify or use a template that contains Third-Party Component(s), you must have the third-party component(s) installed on the machine using BI xPress Package Builder.

SentryOne Workbench (32-bit) must be used when applying templates to SSIS packages or modifying templates on a machine that only contains SSDT for Visual Studio 2015.

Open the BI xPress Package Builder using one of the following methods:

  • Open the SentryOne Workbench, and then select Package Builder.
  • Open Visual Studio, and then right click an SSIS Package File in the Solution Explorer.
  • Open Visual Studio, and then right Click in the SSIS Designer Surface.
  • Open Visual Studio, and then right Click an SSIS Package node in the Solution Explorer.
  • Open Visual Studio, and then select the Package Builder Icon on the BI xPress toolbar.

Select Action

BI xPress Package Builder Wizard Select Action

The Select Action page is the first page in the Package Builder. The following options are available:

New Template, Package, or Control Flow

OptionDescription
New Template (from selection)Create a new template from the selected control flow items in BIDS. Note:  This option is only available when you launch the Package Builder from the SSIS Designer surface.
New Template (from Package)Create a new template from an existing SSIS Package.
New Package (from Template)Create a new SSIS package from an existing package template.

Modify existing Template or Packages

Modify Packages(from selection)Update multiple packages from the selected controlflow items in BIDS. Note:  This option is only available when you launch the Package Builder from the SSIS Designer surface.
Modify Packages (from template)Update multiple packages by applying an existing template.
Modify TemplateModify existing template properties.

After making your selection, select Next to continue.

Select Package Template

BI xPress Package Builder Wizard Select Template

The template browser screen is available when you are creating or modifying packages using templates, or you are modifying package templates. The following options are available:

  • Add a template
  • Remove a template library
  • Remove template
  • Explore template folder location
  • Edit description of template by double clicking the description area
  • Assign screen shot to template by double clicking the picture viewer area

Library Management Options

OptionDescription
Add LibrarySelect the Add Library icon on the toolbar, or right click the library node and select Add Library from the context menu.
Delete LibrarySelect the Delete Library icon on the toolbar, or right click the library node and select the Delete Library context menu option.

Note:  The Delete Library option is only available when the library node is highlighted in the tree view.
Edit LibrarySelect the Edit Library icon on the toolbar, or right click a library node and select the Edit Library context menu option.

Note:  The Edit Library option is only available when the libary node is highlighted in the tree view.

After making your selections on the Select Package Template page, select Next to continue.

Select configurable objects and properties

BI xPress Package Builder Wizard Select configurable objects and properties

Select the package objects and properties to allow for configuration. On the Select configurable objects and properties screen you can:

  • Configure the default properties and values that will be replaced when you create a new SSIS package using the package template.

Note:  All the default configurable properties are listed under <Default Configurable Properties> node.

  • Edit any Property listed in Property Browser by selecting the Current Value cell.

Note:  Any property that is expected to be changed is listed in Green.

  • Verify the properties that are expected to be changed when creating a new template.
  • View the internal structure of packages including all objects (Tasks, Connections, Configurations, variables, Data Flow Components, Event Handlers and Log Providers).

Package Builder Examples

Creating a new template from a package

Create a template from an existing SSIS package by completing the following steps:

  1. Open the desired Visual Studio SSIS project. Right click the package where you want to generate a template in the solution explorer, and then select Package Builder Wizard from the context menu.
  2. Select the New template from package option on the Select Action page. 
  3. Enter a template name, and select the library folder path where you want to store the template file. Select Next to continue. Note:  You can optionally enter a description and attach a template screen shot before continuing.
  4.  Select the package you want to use to create the template. Select Next to review the selected objects and define parameters/placeholdersNote:  A package should be already selected if you launched the wizard from the BIDS solution explorer. On the package selection screen you can only select one base package while creating a new template.
  5. Highlight the object, and select one or more properties to be active the next time the template is used to create a new package. Note:  Selecting properties marks them in green and they show up in the Default Configurable Properties node in the Object Explorer.

  6. Select Next and then select Start to save the template.

Create a new template from a Control Flow selection

This option is only available if you launched the Package Builder from the SSIS Designer.

Create a template from a ControlFlow selection by completing the following steps:

  1. Highlight the objects you want to use as the template in the SSIS Package Designer. Right click your selection, and then selection Package Builder Wizard from the context menu. Note:  The Package Builder wizard will open with New template from selection option automatically selected and the number of selected objects listed in the counter link.
  2. Enter a template name, and select the library folder path where you want to store the template file. Select Next to continue. Note:  You can optionally you can enter a description and attach a template screen shot.
  3. Select the package that you want to use to create the template. Select Next to review your selected objects and define parameter and placeholders. Note:  The package should be already selected if you launched the wizard from the BIDS solution explorer. On the package selection screen you can only select one base package while creating new template.
  4. Highlight the object, and select one or more properties to be active the next time the template is used to create a new package. Note:  Selecting properties marks them in green and they show up in the Default Configurable Properties node in the Object Explorer.
  5. Select Next and then select Start to save the template.

Create new SSIS Package(s) using a template

Create a new package using existing template by completing the following steps:

  1. Open the desired project in Visual Studio. Right Click the Packages node in the Solution Explorer, and then select Package Builder Wizard from the context menu. Note:  The Package Builder wizard will open with the New Package from Template option automatically selected.
  2. Enter the package name. Select Next to continue to the template page. Note:  A random name is generated for new packages you are trying to create by default .
  3. Select an existing template that you want to use for package creation. Select Next to open the object explorer screen.
  4. Review any properties listed in green. If you don't want to select each one individually, then select the first node in the object explorer (Default Configurable Properties) to display all of the configurable properties in one place.
  5. Edit the property value column if needed for all green properties. Select Next to go to the summary screen. Note:  If you have any parameters defined for the template then you will be prompted to enter parameter value before you can leave the screen. This screen may not appear if you have no parameters defined.
  6. Review your final selections, and then select Start to create the new package.

Modify an existing template

You can change a template after it has been created. You can change place holders, parameters, add descriptions, and screen shots. Modify an existing template after it has been created by completing the following steps:

1. Open the SentryOne Workbench and then select the Package Builder.

2. Select the Modify Template option, and then select Next to continue to the template browser screen.

3. Select the template you want to modify. You can edit the description by double clicking on the description panel or edit screen shots by double clicking on the screen shot panel. If no screen shot or description is defined, select the Add Description or Add Screen shot. Select Next to continue to the object explorer screen.

4. Use the object explorer screen to perform any of the following modifications:

  • Add/Edit Parameters
  • Check/Uncheck properties (Check property to include as placeholders)
  • Edit default property value
  • Delete unwanted objects from the template (Right click on object name and click "Delete")
  • The delete option will not work for Data Flow Component but all other objects can be deleted (e.g. Variable, Connection, Task, Configuration).

Select Save to save your modifications.

Create a new parameter

Create a new parameter by completing the following steps:

  1. Select Add/Edit Parameters on the object explorer screen in the Package Builder Wizard.
  2. Enter the parameter name (for example, DimTableName). Select the Auto Search Replace Property Value option.
  3. Enter a value that you want to search for with this parameter (for example, Dim_Customer).
  4. Select Add Parameter. When Auto Search Replace Property Value option is selected, you will be prompted by how many replacements are performed for the search string. Select OK to review all properties that are automatically checked for parameter replacement.
  5. Select Save to save your template modifications.BI xPress Package Builder Wizard Template Parameters window

Search and Replace Values

BI xPress Search and Replace window

Use Search and Replace to search for specific strings that you want to replace with a custom value (or Parameter Name). Select the Search and Replace toolbar option to open the Search and Replace dialog box.

Editing a Property Value

BI xPress Package Builder Wizard editing a property value example

You can edit default property values using the in-line editing functionality, or you can select Edit to open the multi-line editor. Select the desired object from the Package Explorer. Replace the selected text with an existing parameter by using the right click Parameters Context Menu. This menu is only visible if you have parameters already defined.

Delete unwanted objects from template

BI xPress Package Builder Wizard Delete SSIS Object from Package

Delete any objects from a template by using the Delete option. The Delete option allows you to delete any of the following objects:

  • Variable
  • Connection
  • Event Handler
  • Task
  • Configuration
  • Log Provider

Note:  Deleting a Data Flow component is not supported. You can delete an entire Data Flow task.

Creating a shared template library using network share

Currently, BI xPress only supports file system templates. You can share template changes across your team using any source control (for example, VSS or SVN). You can also share templates on a shared network folder, and all developers can use that path when they create a new template library:

  1. Create a new network share which all developers can access (\\mydevserver\sharedtemplates).
  2. Open the Package Builder Wizard.
  3. Select the Modify Template Option, and then select Next to continue.
  4. Select the Add Library Icon in the toolbar. Specify the shared folder location where templates will be stored.
  5. Select OK to save your changes. 

Success: Now any new template files/folders created under this library location by any developer will be visible to all users.

Creating a shared template library using SVN or any other source control system

  1. Create a new folder anywhere on your disk (C\SSIS\templates).
  2. Add template folder to SVN (each source control has different way to add folders to source control and check-in. Refer to your vendor help file).
  3. Open the Package Builder Wizard.
  4. Select the Modify Template Option, and then select Next to continue.
  5. Select the Add Library icon in the toolbar. Specify the template folder location which you checked in to SVN.
  6. Select OK to save your changes. 

Success: Now any new template files/folders created under this library will be visible to all users after you check-in changes to the template folder.

Note:  All other developers have to get  latest after you check-in otherwise they will not see new files in their library.

You can also exclude some unwanted folders (".svn" ".vss")listed in the library by right clicking on the library and selecting Edit Library, and then entering your folder exclusion list (folder1|folder2|folder3).

Package Builder SSIS Templates

BI xPress comes with several predefined templates in the package builder wizard for SSIS 2005/2008/2012 packages.

CategoryTemplate
Active DirectoryExtract Users Information from ActiveDirectory (ADONET Method)
Active DirectoryExtract Users Information from ActiveDirectory (Script Method - Advanced Settings Allowed)
DataCleansingAddress parsing, cleaning and standardization to proper case (USPS Standard)
DataCleansingChange DateFormat
DataCleansingReplace NULL with user defined Value
DataCleansingProperCase - Standardize name, address, title and notes
DataCleansingReplace alpha-numeric characters
Dataloading PatternsUpsert Destination (Bulk update or insert records, this template requires Pragmatic Works Task Factory)
Dataloading PatternsCross Join Example Using Merge
Advanced Email OperationsSend HTML email, use HTML template and automatically replace variable place holders
EmailAdvanced HTML Email with Placeholders
Excel ProcessingLoad Data (Looping) from All Worksheet of Excel File
Excel ProcessingLoad Data from First Worksheet of Excel File
Excel ProcessingRead data from Excel 2007 (XLSX)
Excel ProcessingWrite data to Excel 2007 (XLSX)
Excel ProcessingLoad Data from Last Worksheet of Excel File
Execute ProcessExecute Process With Arguments (Return output in variable)
Execute ProcessExecute Process With Arguments
File SystemCopy file and rename with time-stamp
File SystemCopy File
File SystemDelete file
File SystemDelete folder content
File SystemDelete Folder
File SystemMove file and rename with time-stamp
File SystemMove file
File SystemMove folder
FrameworkAuditing and Notification Framework
FrameworkAuditing Framework
FrameworkNotification Framework
FTP-SFTP OperationsUpload files using Secure FTP (SFTP, FTPS)
HTTP Download-UploadDownload File over HTTP
Looping Various ObjectsLoad multiple files and move to archive folder
Looping Various ObjectsLoad multiple files and rename file with time-stamp
Looping Various ObjectsLoop Database Objects using SMO ForEachEnumerator (Execute DBCC ForEach Table)
Looping Various ObjectsLoop Files Older than N days or Newer than N days
Looping Various ObjectsLoop Recordset and Process Each Row
Looping Various ObjectsLoop through folders and sub-folders
Looping Various ObjectsLoop through sorted files by date, size or name
Looping Various ObjectsLoop through User Defined FileList
System InformationRead Any EnvironmentVariable into a variable
System InformationRead ProgramFilePath into a variable
System InformationRead SystemRootPath into a variable
System InformationRead UserProfilePath into a variable
System InformationWMI - Collect disk space information for multiple servers
XML ProcessingLoad XML - Data Stored As Attribute
XML ProcessingLoad XML - Data Stored As Element
XML ProcessingLoad XML - Data Stored in Multi-Level Elements
XML ProcessingLoad XML - Data with Inline Schema Definition
XML ProcessingLooping through XML Nodes
Zip-UnzipUnzip Compressed File
Zip-UnzipZip File or Folder

Package Sorter

IconDescription
BI xPress Package Sorter IconUse the Package Sorter option to sort packages listed under the SSIS Packages node in solution explorer. You can sort packages by name, size and date modified.

Feature Highlights

  • Sorts packages alphabetically in the project
  • Makes finding packages easier in large projects

Using the Package Sorter

BI xPress Sort Project Items Visual Studio context menu option

Open the sort packages option by right clicking the SSIS Packages node in the solution explorer and then selecting the Sort Project Items icon. Hold Shift and select Sort Project item to skip the option dialog box.

Sort packages in ascending or descending order by the following attributes:

  • File Name
  • Date Modified
  • File Size
BI xPress Sort Files window