Hello!

Back when SQL Server 2012 was known as Denali, one of the new and exciting features released was that all properties of a connection manager in a SSIS project were parameterised. This even happened automatically when they got deployed to a SSIS Server. Great! The feature was also written about some time before 2012 was released.

However I’ve got an issue with this. What you get is not project parameters filling in the values at deploy time, but rather the values hard coded in. So it’s useful that they’ve broken down the connection string into it’s constituent parts, but it’s still not really parameterised.

So how do we parameterise connection managers? Using WWI SSIS as an example, let’s parameterise part of the connection string stored in those projects.

Open up the dtsx project that uses the connection managers –

Right click on the object and you’ll see an option called “parameterise” – if you’ve ever parameterised something in SSIS then this will be familiar to you.

From the drop down you can see that you can either parameterise the entire connection string, or just part of it.

I’m going to do ServerName. And I’m going to repeat the process for the other connection manager.

And so now I’ve got two project parameters in the project.params file. Here I have set them to be local instance (we’ll talk about how we make these environment specific and how to automate this further on).

OK, once I’ve deployed this I now have project parameters that fill in the value of the server name.At the moment they’re going to take the default value from the project, which is the local instance.

If I create an environment and fill in the values there, then I can use environment variables to change the values, depending on what environment I am using.

I’ll also need to create an environment reference –

To change the value of the project parameter, click the ellipse in the “value” field and alter it to use the environment.

This is the project configure window after I have updated the values to an environment variable –

Note that the value of each environment variable is now a named instance, not the default instance which is in the project params file (so this is what would be deployed and used by default, had I not set up an environment.)

Run this using the environment….

and great, the dtsx package runs smoothly.

So now we’ve parameterised the connection manager, but there was a lot of set up to get to that point. How do we automate this?

Using AssistDeploy is how!

I’ve written about AssistDeploy at great length elsewhere, however to sum up, the configuration of the environment and how it maps to parameters is stored in a json file. Now that we’ve got the SSIS project working, we can extract the information out into the json file that is used by AssistDeploy to automate the deployment process.

Confused? OK, let’s clarify with a demo. AssistDeploy is available on PowerShellGallery, so you can run Install-Module to download it. You can also use Nuget, or download it from GitHub. So download it whichever way you want.

Now that we have the module, we can use the “Get-SsisAsJson” function to connect to the instance we deployed to, extract the parameter and environment information and store as json.

View the code on Gist.

This is the logging from the above script. The function found two variables that are associated to this project, and the corresponding parameters.

Opening up the json file shows the mapping of the project parameters to the environment variables we created:

View the code on Gist.

Tidy.

I’m going to delete everything off my local server and re-deploy, only to verify that all the mappings are correct. But it’s worth noting that AssistDeploy functions are idempotent – that is, they only change what needs changing.

Before:

View the code on Gist.

Below is the log from the script above. Briefly what is happening here is that we verify that the json and the ispac match, create folder on server if it doesn’t exist, create the environment if it doesn’t exist, deploy the ispac, create or update the environment variables and map them to parameters and finally create an environment reference if one doesn’t exist.

Returning back to the ISC, the whole project has been deployed with the expected mappings.

Of course, the problem here is that we are deploying with the ServerName as “.\hh”. So we’re still no closer to parameterising them through an automated deployment. Except that we are. Lines 7 and 12 in this Gist includes a switch called LocalVariables. If we took that out and re-ran the script it would fail:

What we have to do is create these variables at runtime

• D_ETL_WWI_DW_Destination_DB_ServerName
• D_ETL_WWI_Source_DB_ServerName

Super tidy.

I’ll create some variables at this point, remove the localVariables switch in lines 7 (now 9) and 12 (now 14) and re-run:

In the logs it states that it has found matching environment variables but with different values and will update these. It would have been easier to drop and recreate all variables, but it seemed a bit pointless to make changes when they weren’t required.

Now we check the environment variables on the server and their value will be “.\lg”

Excellent!

A lot of the features in SSIS projects, such as parameterisation, and the use of environment variables is useful in a Continuous Delivery environment, where the philosophy is “build once, deploy many”. However the ability to automate the process was never really presented as a complete out of the box solution. And I’d like to think that AssistDeploy has gone some way in resolving that issue.

If you’re interested in reading more about AssistDeploy then there are several posts that cover it’s functionality.

AssistDeploy Is Available on GitHub

AssistDeploy 1.2 Release Notes

AssistDeploy 1.3 Release Notes

Hope this demo of AssistDeploy has been useful!