Package Builder

BI xPress ›› Features ››
Parent Previous Next

Package Builder


The Package Builder Wizard in BI xPress can create templates and packages and modify them based on selected tasks.

Feature Highlights

  Easily create and share templates of frequently used packages

  Recreate packages in minutes


Important Note:

If you are attempting to create, modify or use a template that contains Third-Party Component(s), the machine in which BI xPress' Package Builder is installed on will also require the Third-Party Component(s) installed on it as well.


Additionally, Pragmatic 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.


Package Builder Wizard can help you to build time consuming SSIS patterns in few clicks. You can launch package builder wizard 5 ways

  Click on the Package Builder Icon in the Pragmatic Workbench.

  Right Click in the Solution Explorer on an SSIS Package File in Visual Studio (BIDS). You can also Select Multiple Packages.

  Right Click in the SSIS Designer Surface in Visual Studio (BIDS).

  Right Click in the Solution Explorer on "SSIS Packages Node" in Visual Studio (BIDS).

  Click on the Package Builder Icon on the BI xPress toolbar.


On the main screen of the Package Builder you can select any of the six options:




Important Note

New Template (From Selection) and Modify Template (From Selection) are only enabled when you launch Package Builder Wizard From SSIS Designer Surface. If you launch Package Builder outside the Visual Studio using Standalone application then these both options will be disabled.


SSIS Template Browser Screen

The template browser screen is available when you are creating/modifying packages using templates or modifying package templates.

On this screen you can:

  Add/Remove template library.

  Remove template.

  Explore template folder location.

  Edit description of template by double click on description area.

  Assign screen shot to template by double click on picture viewer area.





Important Note

Double click on the description panel to edit description
Double click on the screen shot to browse for a new screen shot image


Add/Remove/Edit Library

You can create your own template library which can point to any folder location on your local machine or network share.
 
Add Library: To add a new library click on the add library icon from the toolbar or right click the context menu option.

Delete Library: To remove a library from the list click on the remove library icon from the toolbar or right click the context menu option. The remove library option is only available when the library node is highlighted in the tree view.

Edit Library: To edit the library name or location, click on the edit library icon from the toolbar or click the context menu option. The edit library option is only available when the library node is highlighted in the tree view.


Property Browser Screen

On this screen you can:

  Configure default properties and values which will be prompted for replacement when you create new SSIS package using package template. All default configurable properties listed under <Default Configurable Properties> node.

  Any Property listed in Property Browser can be edited by clicking on Current Value cell. Any property which is expected to be changed will be listed in Green.

  If you are creating a new template then you can check which properties are expected to be changed.

  See the internal structure of packages including all objects (i.e. Tasks, Connections, Configurations, variables, Data Flow Components, Event Handlers and Log Providers)





How to create a new template

You can create new template using two different ways:

  Template from package.

  Template from ControlFlow selection (Only works when you are in BIDS and SSIS designer is active)


Create a new template from package

To create a template from existing SSIS package perform the following steps:


1.   Select a package from the solution explorer from which you want to generate template.


2.   Right Click > Select "Package Builder Wizard".


3.   On the main screen of package builder wizard you will notice that "New template from Package" option is automatically selected.


4.   Enter a template name, select the library folder path where you want to store the template file.


5.   Optionally you can enter a description and attach a template screen shot.


6.   Click Next to select the package you want to use to create the template (One 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.


7.   Once the package is selected, click "Next" to review the selected objects and define parameters/placeholders.


8.   Highlight the object and check one or more properties which you want to set next time when the template is used to create a new package. Checking properties will mark them in green and it will also show up in the first node "Default Configurable Properties" in the Object Explorer.


9.   Click Next and press "Start" to save the template.


Create a new template from a ControlFlow selection (Only valid when launched from SSIS Designer)

To create a template from a ControlFlow selection perform the following steps:


1.   In the SSIS Package Designer highlight the objects you want to use as template.

2.   Right Click > Select "Package Builder Wizard".


3.   On the main screen of the package builder wizard you will notice that "New template from selection" option is automatically selected. You will also notice that number of selected objects are listed in the counter link. Selected objects sometimes can be more than you have selected, this indicates that you have dependent objects (e.g. Variables, Connections). You can click on counter link to view list of all included objects.


4.   Enter a template name, select library folder path where you want to store the template file.


5.   Optionally you can enter a description and attach a template screen shot.


6.   Click "Next" to select the package you want to use to create the template (One package should be already selected if you launched wizard from BIDS solution explorer). On the package selection screen you can only select one base package while creating new template.


7.   Once the package is selected click "Next" to review selected objects and define parameters/placeholders.


8.   Highlight object and check one or more properties which you want to set next time when template is used to create new package. Checking properties will mark them in green color and it will also show up in the first node "Default Configurable Properties" in the Object Explorer.


9.   Click Next and press "Start" to save the template


How to create new SSIS packages using a template

To create new package using existing template perform the following steps:


1.   Right click on the "Packages" node on solution explorer.


2.   Click the "Package Builder Wizard" menu.


3.   On the main screen of the package builder wizard you will notice that "New Package from Template" option is automatically selected.


4.   Enter the package name. By default a random name is generated for new packages you are trying to create.


5.   Click "Next" to select a template.


6.   On the Template Selection Screen, select an existing template which you want to use for package creation.


7.   Click "Next" to open the object explorer screen.


8.   On the object explorer screen review any properties listed in green. If you do not want to click each one individually then simply click on the first node in the object explorer (named as "Default Configurable Properties"), this will show all configurable properties in one place.


9.   Edit the property value column if needed for all green properties.


10.   Click Next to go to the summary screen. 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.


11.   Once on the summary screen review the selection and press "Start" to create the new package.


How to modify an existing template

You can change a template after it has been created. You can change place holders, parameters, add description, screen shots etc. To modify an existing template after it has been created perform the following steps:


1.   Launch the "Package Builder Wizard" from BIDS or Pragmatic Workbench.


2.   On the main screen of the package builder select the "Modify Template" option to go to the template browser screen. If this option was already selected when you launched the wizard then just click "Next".


3.   On the template browser screen you can select the template you want to modify. Here 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 then simply click the "Add Description" or "Add Screen shot" link.


4.   Click "Next" to open the object explorer screen.


5.   On the object explorer screen you can perform 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).


6.   Once you are done with template modifications click "Save".


7.   Add/Edit or Remove Parameters.

Create a new parameter
To create a new parameter perform the following steps:


1.   On the object explorer screen click "Add/Edit Parameters".


2.   Type the parameter name (e.g. DimTableName)


3.   Check "Auto Search Replace Property Value" option.


4.   Type a value to be searched for this parameter (e.g. Dim_Customer).


5.   "Click Add Parameter". When "Auto Search Replace Property Value" option is checked you will be prompted by how many replacements are performed for the search string and when you hit OK you can review all properties that are automatically checked for parameter replacement.


6.   Once you are done with template modifications click "Save"





Search and Replace values

BI xPress v3.1 introduced Search and Replace functionality for Package Builder Wizard. Using this feature you can easily search deep down for specific strings which you want to replace by custom value (or Parameter Name). You can launch search/replace dialog box by clicking on "Search & Replace" toolbar option.





Editing a property value

You can edit default property values using in-line editing functionality or you can click the edit button and open the multi-line editor. To edit any property you first have to select the object from Package Explorer. To replace the selected text with existing parameter use the right click Parameters Context Menu. This menu is only visible if you have parameters already defined.





Delete unwanted objects from template

After creating a template, if you want to delete any objects then you can use the Delete option. The Delete option allows you to delete any of the following objects:

  Variable

  Connection

  Event Handler

  Task

  Configuration

  Log Provider


Deleting a Data Flow component is not supported. However you may delete an entire Data Flow task.





Creating a shared template library using network share:

Right now BI xPress supports file system templates only. However you can share template changes across your team easily using any source control (e.g. VSS, SVN). Or 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 (e.g. \\mydevserver\sharedtemplates).


2.   Launch Package Builder Wizard.


3.   Click "Modify Template" Option.


4.   On the template browser click the "Add Library" Icon in the toolbar.

 

5.   Specify the shared folder location where templates will be stored.


6.   Hit OK. 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 (e.g. 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 vendor help file).


3.   Launch Package Builder Wizard.


4.   Click "Modify Template" Option.


5.   On the template browser click "Add Library" in the toolbar.


6.   Specify the template folder location which you checked in to SVN.


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


8.   You can also exclude some unwanted folders (e.g. ".svn" ".vss")listed in the library by right clicking on the library and click "Edit Library" and enter your folder exclusion list (e.g. folder1|folder2|folder3).


Built-in SSIS Templates

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

Category

Template

Active Directory

Extract Users Information from ActiveDirectory (ADONET Method)

Active Directory

Extract Users Information from ActiveDirectory (Script Method - Advanced Settings Allowed)

DataCleansing

Address parsing, cleaning and standardization to proper case (USPS Standard)

DataCleansing

Change DateFormat

DataCleansing

Replace NULL with user defined Value

DataCleansing

ProperCase - Standardize name, address, title and notes

DataCleansing

Replace alpha-numeric characters

Dataloading Patterns

Upsert Destination (Bulk update or insert records, this template requires Pragmatic Works Task Factory)

Dataloading Patterns

Cross Join Example Using Merge

Advanced Email Operations

Send HTML email, use HTML template and automatically replace variable place holders

Email

Advanced HTML Email with Placeholders

Excel Processing

Load Data (Looping) from All Worksheet of Excel File

Excel Processing

Load Data from First Worksheet of Excel File

Excel Processing

Read data from Excel 2007 (XLSX)

Excel Processing

Write data to Excel 2007 (XLSX)

Excel Processing

Load Data from Last Worksheet of Excel File

Execute Process

Execute Process With Arguments (Return output in variable)

Execute Process

Execute Process With Arguments

File System

Copy file and rename with time-stamp

File System

Copy File

File System

Delete file

File System

Delete folder content

File System

Delete Folder

File System

Move file and rename with time-stamp

File System

Move file

File System

Move folder

Framework

Auditing and Notification Framework

Framework

Auditing Framework

Framework

Notification Framework

FTP-SFTP Operations

Upload files using Secure FTP (SFTP, FTPS)

HTTP Download-Upload

Download File over HTTP

Looping Various Objects

Load multiple files and move to archive folder

Looping Various Objects

Load multiple files and rename file with time-stamp

Looping Various Objects

Loop Database Objects using SMO ForEachEnumerator (Execute DBCC ForEach Table)

Looping Various Objects

Loop Files Older than N days or Newer than N days

Looping Various Objects

Loop Recordset and Process Each Row

Looping Various Objects

Loop through folders and sub-folders

Looping Various Objects

Loop through sorted files by date, size or name

Looping Various Objects

Loop through User Defined FileList

System Information

Read Any EnvironmentVariable into a variable

System Information

Read ProgramFilePath into a variable

System Information

Read SystemRootPath into a variable

System Information

Read UserProfilePath into a variable

System Information

WMI - Collect disk space information for multiple servers

XML Processing

Load XML - Data Stored As Attribute

XML Processing

Load XML - Data Stored As Element

XML Processing

Load XML - Data Stored in Multi-Level Elements

XML Processing

Load XML - Data with Inline Schema Definition

XML Processing

Looping through XML Nodes

Zip-Unzip

Unzip Compressed File

Zip-Unzip

Zip File or Folder