Joe the Business Intelligence Guy

A journey through the design and delivery of business intelligence solutions.

Pragmatic Works BI xPress – Templates Speed SSIS Development

When we have been developing data integration solutions using SQL Server™ Integration Services (SSIS) for a long enough time (or frankly any other type of design or development for that matter), we start to realize that many of our solutions employ the same patterns of handling information or the manipulation of the information.

Case in point, when loading data warehouse solutions we begin to apply patterns to well known data warehouse data integration processes such as extraction of source data to staging, loading of dimension tables from staging, and loading of fact tables from staging. For a very long time I’ve employed a bit of a copy and paste approach to handling SSIS packages designed based on the patterns for each type of data warehouse data integration process.

Disclaimer: At the time of this writing, I am presently employed by Pragmatic Works; but, even though I am paid by the company, I am providing my own professional observations about a wickedly cool product we happen to have created called BI xPress.

I now have the ability to use my company’s BI xPress product to really help speed development using patterns for the design of each package. Some really useful features were added in a direct response to my own field usage when trying to make templates as powerful and productive as possible. Notably, we have added a concept of parameterization that enables me to set up token replacement values for often repeating values that will change when generating an SSIS package using the template. Prior to this enhancement I might have the same value repeated in many different properties in the SSIS package which required a lot of extra steps to ensure the values were correctly assigned when using the template; but, now with the parameterization these repeated uses are consolidated into a single input value to be changed when using the template.

Let me demonstrate how the use of a BI xPress template with the new parameterization feature helps speed our SSIS package development.

After installing BiXpress the product configures many different useful Business Intelligence Developer Studio (BIDS) add-ins. We can access the Package Builder Wizard many ways including selecting the BI xPress main menu bar’s Package Builder Wizard menu item or, as in the following screenshot, right click on the SSIS Packages folder in BIDS Solution Explorer pane and select the Package Builder Wizard menu item.

Opening BI xPress Package Builder Wizard

Opening BI xPress Package Builder Wizard

For this demonstration I will simply show the design of the template that I created to handle the repetitive pattern design applied to my source data extract SSIS packages. I have the following design requirements for each package:

  1. Use two tiers of package configuration – the first tier reads a Windows Environment Variable to get the path to an XML dtsconfig file (Indirect XML configuration) which contains the configuration for my SSISConfigSqlDb connection manager and the second tier (applied to all remaining configuration entries) uses the SSISConfigSqlDb connection manager to connect to my SQL Server database’s [dbo].[SSIS Configurations] table to configure other connection managers and variables
  2. Use four connection managers that must be configured using package configuration (see prior details)
  3. Use nine variables that are either configured using package configuration or using property expressions most notably the SQLQuickBookSource variable (scoped to the data flow task) will be identical among the packages with the only exception being the actual table name.
  4. Use a data flow task to move data from the original source system to a staging database
  5. Within the data flow task use Pragmatic Works Task Factory Upsert Destination to manage Updating and Adding New values to the staging database

The following screenshot shows the overall design of the package.

Sample SSIS Extract Package Design

Sample SSIS Extract Package Design

With these design requirements in hand, we quickly identify that all the packages can use this generic pattern to create SSIS packages and more specifically that only a very few things need to change when generating each new package such as:

  1. The name of the package (and perhaps the description – you do add descriptions to your packages, tasks, and data flow components don’t you)
  2. The SQLQuickBookSource variable expression for the value property which contains our source SQL statement
  3. The name of the data flow task (I prefer descriptive names to assist with understanding logging and auditing)

After launching the BI xPress Package Builder Wizard we select the option to create a new package from a template and provide the desired package name and folder location as seen in the following screenshot.

BI xPress Package Builder Wizard - Select Action

BI xPress Package Builder Wizard - Select Action

Now we select the desired template from the options presented. Note how BI xPress templates permit providing a screenshot and description to help others to understand the usage of the template.

BI xPress Package Builder Wizard - Select Package Template

BI xPress Package Builder Wizard - Select Package Template

Now we are at the really important and fun part of using these templates, the configuration of values to be used to generate the new package based on the template. The following screenshot shows the various options that were configured during the setup of the template.

BI xPress Package Builder Wizard - Select Configurable Objects and Properties

BI xPress Package Builder Wizard - Select Configurable Objects and Properties

Specific to our scenario, the SQLQuickBooksSource variable’s value which contains our SELECT statement needs to be changed to reference the new table being processed by our new package. Additionally, the naming of the data flow task will also be renamed to include the new table name. The template uses the new parameters capability to permit the user of the template to enter a name of the new structure once and this value will then be pushed into every configurable property that has the parameter as part of the current value (e.g. “SELECT * FROM <@QuickBooksSourceTableName>”). I have had clients where one parameter ended up being pushed into over twenty different configurable properties and as a result they saved having to enter the same value consistently twenty times in order to create the package from the template.

As shown in the following screenshot, the Set Parameters button can be used to set the value of the parameter to the desired value or alternatively this will also be prompted for when proceeding to the next step of the wizard when selecting Next.

BI xPress Package Builder Wizard - Set Parameters

BI xPress Package Builder Wizard - Set Parameters

After the package is generated, in our scenario we will have expected data validation errors due to the fact that the table used as a source and destination in the template differs from the one generated by our template. We simply open the source and destination components in our data flow to make corrections by accepting the option in the source to have the component fix these errors automatically and in the destination by setting the column mapping options in the Restore Invalid Column References Editor to “Delete invalid column reference” as shown in the following screenshots.

SSIS Source and Destination Component Mapping Fixes

SSIS Source and Destination Component Mapping Fixes

With these steps completed we now have done all that is required to create a new package that meets all of our requirements and permits us to extract another source table. BI xPress templates and the parameterization feature really cut down on the effort needed to develop this package and many others that share common design patterns.

More details about this and the many other features within BIxPress can be found at the product’s web site page http://pragmaticworks.com/Products/Business-Intelligence/BIxPress/Default.aspx.

Advertisements

Written by Joe Salvatore

June 15, 2011 at 2:54 pm

Posted in Uncategorized

%d bloggers like this: