SSIS Package Configuration in SQL Server 2005

Parent Package Variable

In complex situations, you might have several levels of packages. For example Package A will call Package B and Package B will call package C and D.  If you have packages which take your values like text files path other settings, it is advisable to load it to the main package (Package A) and then pass it to others. In this design method, you have the luxury of changing configuration only in main package so that it will pass to other packages.
In Parent package configurations, you need to provide current package variables and parent package variables which you need to retrieve values.
Storing Package Location
If you look closely you can observe that there is an option to save the location of the package configurations. For example, you can save the location of the XML file in a Environment variable, so that it is only matter of changing the value of the Environment variable. However, all the issues applied of environment variables apply here too.

Important Points

If you have an SSIS Package with loading configuration settings, sometimes you may receive the following error. Incidentally this is a very frequent question in the user forums.

Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

The message is only raised when the package contains the actual protected data such as passwords and connection strings that can't be decrypted. If the package has ‘EncryptSensitiveWithUserKey’ as ProtectionLevel, but does not have any sensitive data, you can change this to DontSaveSensitive so that you will not see this message.

By default SSIS packages have an ‘EncryptSensitiveWithUserKey’ setting for the ProtectionLevel. As you are providing passwords in package configurations, it is not encrypted so it should be changed 'DontSaveSensitive' . You can change this by selecting property option of the packages.

Best Practices

It is always best to assign one setting each configuration. For example, in our example of loading a text file to SQL Server database, we could have easily configured one XML config file to load database connections and text file path. If you select more than one configuration, it will reduce the usability and will increase the risk of errors. If there are numerous configurations in one setting, you have to pay attention to the priorities which can be difficult.

Your settings are going to contain the database user credentials which raises security concerns. In that case you have to take precautions that unauthorized users should not be able to access the database credentials. In this case, SQL Server configuration is ideal as you can use the SQL Server security model. However, if you decide to store them in XML files you have to make sure that those XML files are kept in a secured folder so that other users do not have access to them.

Suggestion to Microsoft

No doubt this is a fascinating feature in SQL Server Integration Services. However, I have one question, why this feature is not included for other members of the Microsoft SQL Server Business Intelligence family such as SQL Server Analysis Server (SSAS) and SQL Server Reporting Services (SSRS). It would have been much better if this feature is available at least for data sources of SSAS and SSRS.

Conclusion

Package configuration is one the great features available with SQL Server 2005. However, before selecting appropriate option carefully consider all the possibilities.