In need of a Smarter Environmental Package Configuration

Question

I am trying to set up a package template in SSIS, following the Wrox Programmer to Programmer book, SQL Server 2008 Integration Services: Problem - Design - Solution. I'm really liking this book even though it is 2008 and we're using SQL Server 2005. I've got a working package template that uses an Indirect XML package configuration to identify what environment (local developer, dev, QA, production, etc) the package is being run in. That locates the SQL Server package configuration for the environment.

That set-up is great and all except for the environment variable at the very front of it all. My team would prefer it if the package could use the same environment resource locator as all our other applications and tools use, so we don't two environment markers with essentially the same information in them. Normally we look up a registry key in HKey_Local_Machine but the Registry Package Configuration type only lets you look up the HKey_Current_User registries.

My first thought was to write a new Package Configuration Type class that extends the Registry type; after all we'd had such luck writing our own custom log provider. SSIS is super extendable, right? So there doesn't seem to be a way to write your own Package Configuration Types.

Is there still some way I can configure my SSIS SQL Server package configuration from a HKLM registry key connection string?

If this is not possible, what other workarounds are available? My idea is to write a PowerShell script that will create/modify the Environment Variable that the package will use by fetching the connection string from the registry. This way there's still two markers, but at least then it's automatically maintained and automated. Is this kind of workaround necessary?

Thank you for your time.

Answer 1

So I went ahead and went with the PowerShell route because no alternatives were offered.

Here's an abbreviated version of the script, I can't show the code looking up the connectionString variable since it has some private details there but if you're using this as a reference those lines would be unique for your environment anyway.

$ErrorActionPreference = "Stop";

# Open a connection to verify that the connection string works.
"Verifying connection string: $connectionString";
$oleDbConnection = New-Object System.Data.OleDb.OleDbConnection $connectionString;
$oleDbConnection.Open();
$oleDbConnection.Dispose();

# Set the environment variable
"Writing to System Environment Variable 'SSIS_CONFIG' replacing old value: $($env:SSIS_CONFIG)";
[Environment]::SetEnvironmentVariable("SSIS_CONFIG", $connectionString, "Machine");