Microsoft SQL Server Integration Services (SSIS) and reading configuration from a database part 1 idea

Tags: SSIS, SQL Server, Microsoft SQL Server 2008, Microsoft SQL Server Integration Services

A deployed SSIS package consist of some compiled code and it’s configuration.

Typically the configuration will be more or less static information such as database connection strings, pathnames, urls etc. In most professional environments changes to these settings will require a full re-deployment cycle.a

 

However, some settings should be maintainable as a business setting. Imagine each integration job completes with an email to a business person or someone monitoring the job. Changing the recipient should be easy.

 

If the SSIS job downloads some highly dynamic data, perhaps statistics on a per country basis? Adding or removing countries to download is a business decision not on which should require a re-deployment.

The solution could be something like this:

The SSIS Package will contain a connection string to its configuration database. Also, some form of graphical interface to maintain the values could be developed. Both the component (Task) used by the pacakge, the database and the user interface should be easy to deploy.

image

To keep things as simple as possible:

  • A single dll solution. No fancy dependencies like NHibernate to read the configuration.
  • Should work with a out-of-the-box minimal SQL Server 2008 with Integration Services installation.
  • Single table in database
  • Metadata is self-updated and maintained by the SSIS Task
  • GUI Should have as low dependencies as possible. No installation necessary

No Comments

Add a Comment