SQL Server 2012 Environment Variables

There are several new features introduced in SQL server 2012.  There have been some very compelling improvements to SSIS. The new SSIS catalog is one of the most important because it means that SSIS now ships with it’s own Performance Framework.

Another feature and the purpose of this BLOG post is to introduce Environment Variables. This is a cool feature because now you don’t have a dependency on environmental variables in the operating system.  Some Network Admin won’t let BI developers access the OS. Environment Variables also simplify disaster recovery because you don’t have to recreate the environmental variables.

I am not going to give a step-by-step on how to step environment variables. Plenty of tutorials have already been created on the web. What I would like to do is demonstrate  that Environment Variables can be referenced by anything that Environmental Variables can reference this includes project and package level SSIS objects like connection managers, parameters, and variables.

I created a demo package below with project and package level parameters and connection managers:

image

image

image

I created a catalog and folder on SSISDB and deployed it to the server:

image

Now environment variable can be created and reference the any SSIS object property in the project: (make sure the data types match)

image

Now you can create the reference which ties the SSIS object property to the environment variable.

image

Then choose the parameter or connection manager and set it’s value to the environment variable

image

Notice how you can choose from the package level or project level.

About these ads

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

Join 38 other followers

%d bloggers like this: