Tag Archives: Package Configurations

Pragmatic Works, SSIS

Adding/Copying SSIS XML Package Configurations

SSIS Package Configurations allow us to modify SSIS package properties from outside of the packages. This gives us a seamless way to dynamically point our packages to our development, QA, and production environments without needing to manually open and modify our connection managers within each SSIS package. Package Configurations can be created using a SQL Server table, environment variable, XML files, registry entry, or parent SSIS package, but this post will focus on creating an package configuration using an XML file.

To create the Package Configuration, open an SSIS package in SSDT or BIDS and right-click in the blank area of the control flow and select Package Configurations.

image

Check the checkbox to enable package configurations and then click Add to add a package configuration to the package.

image

In the drop down list next to Configuration type select XML configuration file. Browse to the location where you wish to store the XML configuration file and type in the name which you’d like to give your configuration file and hit enter.

image

And now time for a quick note on best practices and my thoughts on using XML configuration files. SSIS package configurations are most often used to configure Connection Managers from outside the SSIS packages. With this in mind, I prefer to create one package configuration per Connection Manager. I also prefer to name the configuration file the same name as the Connection Manager. In my experience, the 1:1 ratio of XML configuration files to Connection Managers eases maintenance and management of the XML configuration files. If you’re creating XML configuration files to configure other properties of the package such as package variables or task properties, you may find another solution works better for you. That’s just my .02 cents on the matter.

Now we need to specify the property which we wish to configure. In this example, I’m selecting the ConnectionString property of a Connection Manager. Check the checkbox next to the ConnectionString property under the Connection Manager. Click Next. On the final screen you’ll need to give the Package Configuration a name. I give the same name as the XML Configuration File to help things stay nice and neat.

image

We can see our package configuration has been created. Click close.

image

Yay! Now your package has an XML configuration file set up to dynamically configure your connection string of the connection manager you selected at run time. All that is left to do is to copy the XML configuration file to each environment the package will be run on: developer machines, development, QA, and production environments. Then you must also update the XML configuration file with the environment’s connection string. Just open the XML file in note pad and update the Connection String to point to the appropriate server and database.

image

So we’ve set up the package configuration on a package. But what if we need to copy the package configuration to many other packages? Sure, we could complete the previous steps manually on each package, but that would take a lot of time. We can, however, easily accomplish this in a few clicks with BI xPress. In order to complete the following steps you will need to have BI xPress installed. You can download and install a free trial version of BI xPress here.

image

Shift select all of the packages within your SSIS project including the package that has the package configuration applied as well as the packages you wish to modify. Then right-click the packages in the Solution Explorer and select Package Configurations (BI xPress) to open the BI xPress Configuration Wizard.

image

Click Next pass the first screen of the Configuration Wizard.

image

You should see all of the packages highlighted and in the Select Items window. Click Next.

image

In the following screen you should see your packages listed and the lone package configuration displayed beneath the SSIS package we added the configuration to.

image

Right-click the package configuration and select copy or simply highlight the package configuration and hit Ctrl+c to copy the package configuration.

image

Then select each package in the list individually and hit Ctrl+p to add the package configuration to each package.

image

Click the disk icon to save your changes and we’re done! Our package configuration has been added to each package with much less work than we initially went though.

image

Just be aware that if package configurations have not been enabled for the packages you added the package configuration to you will still need to complete that step for each package.

For more information on BI xPress just head over to PragmaticWorks.com or head here to download the free trial version of BI xPress. Enjoy!

Pragmatic Works, SSIS

Quickly Edit Package Configuration Values from BIDS

If you’ve followed my blog at least somewhat, you’ve probably read a blog post or two where I cover one or several of the really cool features of BI xPress. That’s because, simply put, BI xPress is a very versatile and useful tool that no SSIS developer should be without. It pains me to think about all the time I have to waste whenever I go work for a client that does not have BI xPress.

With that said, one of the smaller but more useful features of BI xPress that I found myself making use of is the Quick Config Edit feature. With Quick Config Edit, you can easily view the configurations being used in your package and edit the values of those configurations very easily, all without leaving BI xPress.

To open Quick Config Edit, go to the BI xPress menu at the top of BIDS and select Quick Config Edit.

10-31-2011 2-17-05 PM

Once Configuration Editor is open, you’ll need to specify where your configurations are stored, whether it be on the File System or in a SQL Server table. In my case, I’m using a SQL Server table to configure this particular package. Specify the server, the credentials used, the database, and the table.

10-31-2011 2-23-37 PM

You’ll then be able to view the Configurations in your package, the package properties being configured, the values used to configure the package, and also make changes to those configurable values. It even becomes possible to edit multiple package configurations at one time.

10-31-2011 2-39-22 PM

Here you can see a configuration I have for a connection string. From this window I can easily adjust the connection string stored in the SQL Server table. Once I make a change to the Value I can just click Save to make the change final.

10-31-2011 2-27-32 PM

This is an example of a configuration for a Package Variable. From this view I can easily see the scope of the variable (Foreach Loop Container, in this case), the variable being configured, and the value of the package configuration.

10-31-2011 2-27-48 PM

Quick Config Edit also has a very handy feature that makes is easy for you to do a quick Find and Replace of your package configurations, allowing you to update multiple package configurations in the blink of an eye.

10-31-2011 2-34-09 PM

Quick Config Edit is a very simple but extremely useful feature. It may seem small, but these are the kinds of features that increase the speed of package development by allowing you to stay in the BIDS environment. This is hands down one of my favorite features of BI xPress because it is so convenient.

Check out this page if you’d like to read more about the features of BI xPress.

Pragmatic Works, SSIS

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.

Pragmatic Works, SSIS

Encrypting Sensitive Information in SSIS Package Configurations using BI xPress

One of the downfalls to SSIS package configurations is that it does not natively allow you to encrypt your sensitive information, such as a connection string or a variable, that you may be configuring with an outside package configuration. There are some work-arounds that you can impliment that will allow you to automatically encrypt and decrypt stored passwords, but its not pretty. The good news is that now, with BI xPress, SSIS developers can easily and automatically encrypt any configured property of an SSIS package. So lets walk through using the BI xPress Package Configuration Organizer and look at a couple of the different options we have when encrypting an SSIS package configurations.

There’s a couple ways you can access the BI xPress Package Configuration Organizer. Just right-click on some empty space in your Control Flow design area and select Package Configurations (BI xPress).

Capture1

You can also click the BI xPress menu at the top of BIDS and then select Package Configurations (BI xPress).

Capture1a

This should open the BI xPress SSIS Configuration Wizard. This is the wizard that will allow you to select which package properties you would like to configure and encrypt. Make sure you check the box to Enable package configurations.

Capture4

On the next screen of the wizard, you’ll need to set a few options. The first is to decide what type of Configuration you want to use. You can choose from several options seen below, but I’ll be using an XML configuration file.

Capture4 

After you specify the Configuration Name, click Next.

On the next screen specify which property you want to configure/encrypt. At the top of the window, I can select an encryption mode, I can choose to encrypt only the sensitive data or the full value of the property. In this example, I’m encrypting the connection string for the connection manager OLEDB_SSISOPS_1, so I’ve checked the check box next to the ConnectionString property under OLEDB_SSISOPS_1. We can also select an algorithm type. I’m using the Advanced algorithm to encrypt the connection string. Using these settings, the entire connection string for this connection will be stored in the configuration file in an encrypted format.

 

Capture6

Click Next and on the next screen click Start to generate the configuration file. Once the wizard is finished a summary screen will appear. Click Finish to close the wizard.

And you’re done! Here is a screen shot of the configuration file. You can clearly see that connection string is encrypted and unreadable.

Capture7

Finally, it’s a piece of cake to encrypt sensitive information stored in package configurations!