Wednesday, August 22, 2012

My Favorite SSIS Tips and Tricks–Tip #3

This next tip is related to package properties and package GUID’s.  Whenever I create a new package (or whenever I create a package template), I always set two package properties right out of the gate:

  • I set the ProtectionLevel package property to DontSaveSensitive.  This property determines how sensitive information (passwords for connection strings, for example) are stored.  The default for this property is EncryptSensitiveWithUserKey.  This default causes sensitive information to be encrypted with the developer’s AD Windows account information.  Other developers who open the package will run into problems as any encrypted information can then not be unencrypted.  With the property set to DontSaveSensitive, sensitive information is not saved with the package.  Thus, if passwords are needed for connection strings (for example), that information must be set in the package via another mechanism such as package configurations, variables, property expressions, etc.  This is typically a better design, however, than letting sensitive information be encrypted and stored with the package.
  • I set the SuppressConfigurationWarnings package property to True.   With this property set, if the package refers to a package configuration (XML or SQL Server table) that contains settings that are not relevant for the package (such as variables that the package doesn’t contain), then warnings related to the missing settings will be suppressed.  The default for this property is False, which results in package configuration warnings that can typically be safely ignored.

As mentioned above, I set these properties in new packages and in package templates.  In most cases, my package development either starts with a package template or starts from an existing package (by copying and pasting an existing package back into the project).  As such, containers, tasks, and components end up having the same GUID values in the new package as they had in the old package (and if you are using SSIS 2005 and BIDS 2005, the package itself has the same GUID as the old package).  In some cases, this may not cause problems.  However, as several SSIS developers have discovered in the last 5-7 years, there are cases when this can cause big problems.  If two packages having components with the same GUIDs run at the same time, all types of problems can occur as the SSIS engine tracks packages and components by GUID.  And several BIDS designer issues can occur as well.  Thus, as soon as I create a new package based on a package template or prior package, I immediately reset all package GUIDs.  The easiest way to accomplish this is to use the Reset GUIDs feature of BIDS Helper (a fabulous tool that every SSIS developer in the world should be using daily).

Hope these small tips help budding SSIS developers (or even experienced developers who may not have come across these suggestions before)!

1 comment:

Anonymous said...

very well and educated ifnormation.
I am trying to get hold of you but it does seem like working