One of the most powerful features of BI xPress, in my opinion, is the Package Builder Wizard. The Package Builder Wizard allows an SSIS developer to create a template from a package or a from a few components of a package. The developer can then use those templates to create new packages or alter existing packages. In this short post, I’m going to walk through creating a template that contains a package configuration and then show you how to apply that template to all the packages in my SSIS project.
Recently I was consulting for a client that required me to add multiple package configurations to dozens of existing packages. Using the follow method, I completed that work in less than an hour. Needless to say, the client was very impressed and has since bought many copies of BI xPress.
If you’d like to follow along with me, visit http://www.PragmaticWorks.com and download the free trial of BI xPress.
I’ve got an SSIS projects that has a few packages in.
My requirements are that I need to put package configurations on each of these packages so that way I can manage the package’s connections outside of BIDS.
The first thing I need to do is create package configurations for the properties I’d like to configure from outside the package. In my case, I’m creating two package configurations: One for my connection to my destination database and another for the connection to my auditing database.
To create a package configuration, right-click in a blank area of the Control Flow and select Package Configurations.
With the Package Configuration Organizer, make sure to check the check box to Enable Package Configurations. Then click the Add button. Next select the type of Configuration. In this example, I’m creating an XML configuration file (.dtsConfig). Specify the file path and name of the dtsConfig file. Then click Next.
On the next screen, select the property that you want to configure. I’m going to select the ConnectionString property for the CashForClunkersDW Connection Manager. Click Next.
On the final screen, give the Configuration a name and click Finish. I then created an additional configuration file for my other connection. Here you can see the two configurations I created.
Now that I’ve created the package configurations I want to apply to all my packages. I need to create a template. To create a new template, right-click in an empty area of the Control Flow and select Package Builder Wizard (BI xPress).
When the wizard opens, select the option on the top left to create a New Template (From Selection). Specify the Template Name and select the folder to store the template. Click Next.
On the next screen, select the package objects to include in the template. Scroll all the way to the bottom of the Configurable Objects window and select the Package Configurations. Then click the Next button.
Then click Start on the next screen to create the template. Now that the template is created, now we can apply our template to the rest of our packages. In the Solution Explorer, select the packages you want to apply the new template to by holding Ctrl and clicking. Then right-click one of the selected packages and select Package Builder Wizard (BI xPress).
Make sure the option to Modify Packages (From Template) is selected. Click Next.
Then I’m going to select the template I just created.
On the next screen you should see the packages you selected in the Solution Explorer. You can select other packages or deselect. Click Next again. On the next screen, click Next again.
If a warning message appears letting you know that the package layouts will be modified, click Yes to continue. Then click Start to apply the template to the packages.
After applying the template to the other packages, make sure you open each package and check the configurations just to make sure everything went as planned before you start testing. And that should be it! You just added package configurations to all of your packages in a matter of minutes using BI xPress.