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.

Greenfield Business Intelligence – where do we start?

Generally, the human condition means that we often don’t know when to start doing, and don’t know when to stop. I’m sure you can think of plenty of examples in life! This happens in business a lot; as an illustrative example, some managers might not be willing to stop a strategy which the evidence shows isn’t working…. conversely, they don’t know when to start a new strategy.

The human condition also applies to business intelligence, since it involves people. However, there is the added problem of not knowing how to start, or how to stop. Greenfield business intelligence projects might sound like a dream; completely new technology, completely new solution to design, build and test, and there’s no hangover from previous bad coding.

Greenfield business intelligence does give us another set of problems, however. The human condition means that users don’t know what they want. This may sound strange, but sometimes users can perhaps lack confidence to say what they think that they might need now or in the future. After all, who wants to take the blame when something goes wrong later on? User input and experience is extremely valuable in producing business intelligence solutions and it’s very important to include them early on. So, if you can’t get the user input easily, what can you do?

Greenfield business intelligence isn’t all about technology; there are people issues as well.  In these situations, the following advice might help:
  • As a recommended strategy, producing reports in an agile way can help users to provide feedback. This will increase their confidence in the system, in addition to feeling that their input is valuable. It wouldn’t hurt to drive home that their input is necessary to make the project a success. This means that report changes can be done in a kaikaku way – iterative, smaller changes that constitute a part of the overall whole.
  • Remember that Excel is your friend; users love Excel. Users eally appreciate being able to see something. An ideal way to do this is to produce a few mock-up reports in Excel with some made-up data. Users tend to give an almost visceral response, for example, ‘I really like that, I really need that!’ or ‘ugh, not what I wanted  at all!’ This can help focus your efforts in planning the data that goes into the data warehouse.
  • Use a ‘traffic light’ system to identify what you must have, what you should have, and what’s not necessary. In project management speak, this is sometimes called the MosCow method: Must have, Should have, Could have. This is easy for everyone to understand, and can help move the requirements to move forward quite quickly.
  • All feedback is useful; the negative and the positive. If people don’t like a report or need the data, then it’s good to be ‘lean’ and not include it at all. Why give yourself extra work to include data which isn’t necessary?
  • In Kimball’s books, this is a real emphasis on this strategy and the first few chapters are about the ways of talking to business users. If you haven’t read any Kimball, I can really recommend the wisdom you’ll find there.
To summarise, although getting user input can be difficult, there are some tools to use which can help to obtain it more easily. It’s better to try and get user input in the planning phase rather than after you’ve spent months working on a business intelligence solution that does not answer the business questions. If that happens, the users won’t accept the solution and it’s ultimate failure.

I’d love your comments and if you’ve any other advice, please do leave a comment. I’d love to learn from your experiences!


Add to Technorati Favorites