SSIS and Intelligent Laziness: SQL Server Package Configurations

Porting and deploying SSIS 2008 packages as part of the software development life cycle can be a tricky process! Applying the principles of ‘Intelligent Laziness’, here are some thoughts around the best package configuration method to use which results in the minimum work with the maximum long-term results! There are two main issues regarding porting packages from development through to the production environment. One issue is ensuring that the package runs in the new environment, without any problems. The second issue in deploying packages to production involves ensuring that the deployed package picks up the correct production settings: not the old development settings.
Fortunately, there are different ways in SSIS of achieving the maximum portability in deploying packages throughout the software engineering cycle from development to production. The SSIS package is a collection of objects which depend on the environment. This can make deployment more complex than it perhaps needs to be for two reasons, which I call the ‘between environment’ and ‘within environment’ issues.
Between environment issue: for example, the package may take in data from one system, or perhaps load data from a file. This may change from environment to environment at the same point in time.
Within environment issue: there is the practical consideration is that the same environment will change over time. For example, security access may change, which means that your package login to source systems may need to be amended.
In order to address these issues, it’s important to be able to modify a package with the minimum disruption to the package operation; preferably, to make these changes in a way that is insulated from the package itself. SSIS offers a way of doing this, which is called Package Configurations. These have two important aspects:
Package Path to the property – this is like an address for each object in the package. An example may include \Package.connections[ConnectionName].ConnectionString, which may hold the connection string for an Oracle source database, for example.
Property Value – the actual value when the address is redeemed e.g. servername, user name
There are many different ways to configure Package Configurations, but my preferred method is the SQL Package configuration method. Essentially, SQL Server table configurations mean that the package configuration values are stored in a SQL Server table, which is de-coupled from the package itself. The packages only need to be deployed once. Each time the package executes, it sucks in the configuration parameters from SQL Server table. The SQL Server table does not even have to be stored on the SSIS server; it can be stored on a dedicated server. This is my preferred method for a number of reasons:
Flexible, central administration – that wholesale changes can be adopted much more easily than stored as XML, which would need re-deployed each time.
Transparency – by allowing support and development teams to be able to view the configuration table, then it’s possible to make the packages easier to administer. In the case where a package is being deployed, it’s possible for the configurations to be double-checked by everyone involved in the deployment process.
Managing ‘within environment’ changes – For example, let’s take the case where the package login needs to be changed from one value to another, or a password needs to be changed. By simply running an update statement before the next iteration of package execution, the password value would be simply retrieved from the central SQL table at the point of execution. In the situation where there are a number of packages that require an amended login, then this one simple change would mean that all of the packages would be automatically updated.
Managing ‘between environment’ changes – When taking a package from development through to production, it is possible to execute update statements to so that the package is pointing at the production settings rather than the development settings. This can be done as part of the deployment process. Further, in releasing an update script, the deployment personnel can double-check that the settings are correct.
Connection Manager changes – It’s possible to use the table to store connection manager information. This is useful when moving the SSIS package through the software engineering cycle from dev onwards.
Configuration Re-use – since the configurations are centrally stored, it is possible to re-use existing configurations. In reality, you probably end up building different packages from the same data stores; so why not re-use? This may save you some time in the longer run.
Package configurations can be your friend! They can really help developers to stick to software development principles of moving through dev/sys/uat/production. Personally, I don’t like using a production environment as a test environment and I would never recommend it. The usual ‘we don’t have the money to have separate environments’ excuse no longer washes, with the availability of VPCs and Hyper-Vs. To summarise, SQL Server package configurations can assist you to manage SSIS packages centrally with the minimum work involved in the management process.

Leave a Reply