Dynamically Changing Server Names in SSIS

Question

I have several environments that my SSIS packages move through during a development cycle (Development, QA, Staging, and Production) as such I'd like to use a configuration in SSIS to set the Server Names in my Connection Managers so that I don't need to do this by hand.

I've read about using xml config files, SQL config tables, and environment variables. However the problem I have is that my QA and Staging environments are on the same server but using two separate SQL instances. How can I configure the Server Name dynamically in this instance?

Answer 1

Ok this is how we handle. We use an environment variable to determine the database to read teh rest of teh configuration from. Environment variables are associated with users, so we set up the job for QA to one user and the job for staging to another. Our users are called something like SQLQA and SQLstaging and are only used to run jobs. Then the environment variable points to the database where we store the rest of the configurations in SSIS config.

Answer 2

You could still use one configuration file for QA and Staging. Include both servers in the file.

Then, when you build your QA processes that execute the package, include an optional run-time parameter which takes the specific environment you want the package to execute in, and use a small script task at the beginning of your package to set the appropriate variables (or even just dynamic variables).

It's not perfect, but it should at least let you execute in different environments without having to change the package itself.

Answer 3

My solution to this problem was that design time values always pointed to the dev environment. Any developer opens up the package, it validates against that environment and all is well and good.

Running the packages outside of dev meant that they were run through SQL Agent. If you can exact that level of control, then it's a simple matter of creating your jobs to point to the correct configuration repository.

Physically implemented, I used a custom ssis catalog per environment (SYSDB) that held our configuration, logging framework + the standard logging table (sysdtslog90/sysssislog). Every package was required to have a variable User::Default_ConfigurationServer and that variable was used as an expression on the Configuration Connection Manager's ConnectionString property. Sounds complex but it's not---

1. Create a variable of type string
2. Copy the value of the configuration connection manager's connection string and paste that as the value
3. Assign an expression back to the configuration connection manager's ConnectionString property

Net effect in dev is that it does nothing but now you're positioned to make it work in other environments. My Agent's all look something like

DECLARE @serverName sysname
,    @jobstep_command nvarchar(4000)
-- Lots of other stuff removed
SET @serverName = @@servername

SET @jobstep_command = N'/SQL "\MyPackage"' + '" /SERVER "' + @serverName + '" /CHECKPOINTING OFF /REPORTING E /SET "\Package.Variables[User::Default_ConfigurationServer].Properties[Value]";"\"Provider=SQLNCLI10;Data Source=' + @serverName + ';Initial Catalog=SYSDB;Integrated Security=SSPI;\""'

-- create the job, also removed
-- Create the job step
EXECUTE @return_code = msdb.dbo.sp_add_job 
    @job_name = @job_name
,   @enabled = @job_enabled
,   @description = @job_description
,   @start_step_id = @job_start_step
,   @category_name = @category_name
--, @category_id = @category
,   @owner_login_name = @job_owner_login_name
,   @notify_level_eventlog = @job_notify_level_eventlog
,   @notify_level_email = @job_notify_level_email
,   @notify_level_netsend = @job_notify_level_netsend
,   @notify_level_page = @job_notify_level_page
,   @notify_email_operator_name = @job_notify_email_operator_name
,   @notify_netsend_operator_name = @job_notify_netsend_operator_name
,   @notify_page_operator_name = @job_notify_page_operator_name
,   @delete_level = @job_delete_level
,   @job_id = @job_id OUTPUT

Now, regardless of where my job is created, it points that variable to the right location which in turns leads to the package discovering the correct repository and I have less work to do.