vrijdag 20 december 2013

3 easy steps to parametrized deploy environments using SSIS and Visual Studio

Just a little something I'd thought I'd share with you.  Recently someone asked me to have a look at a problem with parametrized connection manager variables that just wouldn't work.  I'll spare you the details.  Suffice it to say that they overcomplicated things and through redundancy couldn't get things to work.

So, if you're looking for an easy, yet pretty solid way to go about deploying your SSIS packages in a multi-environment setup, give this one a spin.
More info:
  • - Business Intelligence
  • - ETL flow
  • - Microsoft Stack (Visual Studio, MS SQL Server, SQL Server Integration Services)
  • - multi-environment

In the example I'll be using, the source repository happens to be a Sharepoint Server (Microsoft Office Sharepoint Server - MOSS --> that's why you'll see that acronym a lot)


1. Set up your connection managers in Visual Studio

I've created 3 connection managers; one for my source system (that's the one I'll be using in the example), one for my DWH staging area, and one for the actual DWH.  I've got a lot of SSIS packages (as can be seen below), but for the example I'll just pick one (E_MOSS_10).  Above you see the detail for that package's data flow task.  Simple source to target.

I suppose you're with me so far :)

In visual studio I don't do anything else too fancy (NO PARAMETERS at this stage), just fill in the credentials for the connection manager:
I then build and deploy my package onto my SQL Server, and then switch tooling: exit VS, enter MSSQL Server Management Studio.

Woohoo, successful deployment !
As you know (and if you don't, you really shouldn't be reading this blog, but preferably some more initiating topics), your Mgmt Studio lists the deployed SSIS packaged separately.  So at the completion of step 1, we can drill down to the right package (10), right click it, and execute.
If you have a quick look at the connection Managers tab, you'll see initial values (sometimes null), indicating that the values from Visual Studio (the non-parametrized settings for the connection managers) are used.

Step 2 - Simple configuration after deployment

If you want to switch environment (e.g. to a production environment after you've deployed), you can use the Configure option for the deployed ssis package:
Above the end result, and below how you enter the value.  If you do this in configuration (not execute), these settings will remain the same until your do a new deployment.


After that, same execute as before, and voila, you've now successfully switched environment.  However, this is not good enough, because for each deployment you have to go through a manual process of entering all the correct values.  And let's say that I want to swap between several environments after deployment (on the fly).  What a hassle ... but wait, there's step 3 ...

Step 3 - Setting up Environments

Independently from your ssis packages you can configure Environments in SQL Mgmt Studio.  This is really great, because in a formal production environment I can't stress enough that the production user and passwords should not be shared with all the development team !  Here an environment can be prepared (preferably multiple ones; DEV, TEST, UAT, PROD) and the developer never needs to worry about connection strings and credentials.  But back to the example.

Below the SSIS projects (per deployment folder, in my case ssis01) there is a Environments folder
Right-click and select "Create Environment ..."
Something like that, click ok and it should show up:
(hit refresh if it doesn't)
Double-click and in the Variables section, create the variables you need (in my example I've just done user and password, in a real situation you'll need the connection string as well)
Notice the Sensitive checkbox ? Your development team will never see that production password (go ahead, try to uncheck it :) )

Last step is to connect the environment variables to the connection parameters.  But, wait ... didn't we not do any variable creation, not ?  Yes, we did(n't).
For the connection manager all the properties are converted to parameters by default (that's why we saw them in the connection managers tab when we did the configuration --> note that this only applies to connection managers, for everything else you need to create and use parameters in Visual Studio before you can link them !)

First we need to tell the SSIS project (bundle of packages) that we want to be able to use the environment:

In the configuration dialog go to the references section and click add (and then add your newly created environment).  
And the, finally, the mapping itself.  In the parameters section, click the [...] button to edit the value for a connection manager property (as we did before), but this time instead of entering the value, select the use environment variable option, and in the dropdown the created variables should show up.
That's it.  Mine looks like this:
If you create multiple environments, just make sure that the variable names you use are the same (so in my case 'MossUser' should exist with a different value in each environment).

When you execute a package, all you need to do know is select the right environment:

There you go.  Simple stuff, but great as a best practice in real BI product factories.

Let me know what you think ...