Package Deployment Wizard
|The 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.|
- 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.
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
|Package Deployment Options||Overwrite 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 Options||Select 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
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:
|File System||Specify the correct path for the source and/or target location. Note: You can specify a remote server as the locations for the packages.|
|SQL Server||Specify the server and user credentials.|
|SSIS Package Store||Select 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.
Select Finish to close the package Deployment wizard.
|The Package Builder Wizard can create templates, and packages, and modify them based on selected tasks.|
- 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.
The Select Action page is the first page in the Package Builder. The following options are available:
New Template, Package, or Control Flow
|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 Template||Modify existing template properties.|
After making your selection, select Next to continue.
Select Package 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:
Library Management Options
|Add Library||Select the Add Library icon on the toolbar, or right click the library node and select Add Library from the context menu.|
|Delete Library||Select 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 Library||Select 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
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:
- 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.
- Select the New template from package option on the Select Action page.
- 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.
- 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.
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:
- 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.
- 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.
- 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.
- 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.
- 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:
- 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.
- 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 .
- Select an existing template that you want to use for package creation. Select Next to open the object explorer screen.
- 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.
- 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.
- 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:
- Select Add/Edit Parameters on the object explorer screen in the Package Builder Wizard.
- Enter the parameter name (for example, DimTableName). Select the Auto Search Replace Property Value option.
- Enter a value that you want to search for with this parameter (for example, Dim_Customer).
- 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.
- Select Save to save your template modifications.
Search and Replace Values
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
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
Delete any objects from a template by using the Delete option. The Delete option allows you to delete any of the following objects:
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:
- Create a new network share which all developers can access (\\mydevserver\sharedtemplates).
- Open the Package Builder Wizard.
- Select the Modify Template Option, and then select Next to continue.
- Select the Add Library Icon in the toolbar. Specify the shared folder location where templates will be stored.
- 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
- Create a new folder anywhere on your disk (C\SSIS\templates).
- 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).
- Open the Package Builder Wizard.
- Select the Modify Template Option, and then select Next to continue.
- Select the Add Library icon in the toolbar. Specify the template folder location which you checked in to SVN.
- 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.
|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||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|
|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|
|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|
|Use 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.|
- Sorts packages alphabetically in the project
- Makes finding packages easier in large projects
Using the Package Sorter
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: