Best Practices per la configurazione e il deployment dei package in SSIS
Su technet sono presenti alcuni interessanti documenti sull’approccio giusto da scegliere per la configurazione ottimale e il deployment dei package in Sql Server 2008 Integration Services. Ve li segnalo.
Considerations for Choosing a Configuration Approach
There are several important aspects in defining an approach to configurations.
1. Understand the advantages and limitations of each configuration type. Books Online includes details about each type of configuration (https://msdn.microsoft.com/en-us/library/ms141682(SQL.100).aspx). One limitation has a direct effect on the configuration approach - Parent Package Variable configurations are always applied later than the other types. This means that a Parent Package Variable configuration cannot be used to set values that other configurations depend on.
2. Some configuration types are excluded by environmental factors. For example, in some environments, the people responsible for the servers may not allow new environment variables to be created on them. This eliminates environment variable configurations from consideration. It also prevents the use of indirect configurations, since those rely on environment variables as well. If the deployment environment does not include a SQL Server relational instance, then SQL Server configurations cannot be used. Sometimes, it may be worth seeking an exception to such restrictions, as they add complexity to the configuration solution.
3. How the packages will be run? Will the packages be run as single, standalone packages? Or will multiple packages be run together as a unit, using a parent package to call multiple child packages? If the packages are to be run as single units, the configuration approach needs to avoid the use of parent package variables. In some cases, the packages may need to be capable of running both standalone and as a unit, which can require special handling.
4. How will the location of the configurations be specified? With SQL Server configurations, Integration Services needs to know which database holds the configuration table. With an XML configuration file, Integration Services needs to know the path to the XML file. These values are stored in the package, but they often need to be updated or overridden when the package is deployed.
5. Which properties are going to be grouped in a single configuration file? An Integration Services configuration container can store multiple property settings. For example, an XML configuration file can hold connection string values for two separate connection managers. When the configuration is applied, SSIS will attempt to set the connection strings for all connection managers specified in the file. The ConfigurationFilter value serves the same purpose of grouping property settings together for SQL Server configurations. If you attempt to apply a configuration to a package, and the configuration contains a property that does not exist in the current package, a warning will be generated. For this reason, you should only include multiple property settings in a configuration file or group when you are confident that any package that is using the configuration will have all the properties defined.
6. How often will the configurations need to be changed? Will the actual configuration need to be changed dynamically when the package is executed, for example, by using the /CONFIG, /CONN, or /SET switch of DTEXEC? Or will the configuration be consistent whenever the package is executed in a given environment?
Riferimenti :
https://msdn.microsoft.com/en-us/library/cc671625.aspx
https://msdn.microsoft.com/en-us/library/cc671628.aspx