Quickly Apply SSIS Package Configurations to Multiple Packages with BI xPress

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.

Capture1

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.

Capture2

To create a package configuration, right-click in a blank area of the Control Flow and select Package Configurations.

Capture3

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.

Capture4

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.

Capture5

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.

Capture6

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

Capture7

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.

Capture8

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.

Capture9

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

Capture10

Make sure the option to Modify Packages (From Template) is selected. Click Next.

Capture11

Then I’m going to select the template I just created.

Capture12

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.

Leave a Reply