t’s time to go back to discussing the environment when it comes to SSIS packages. For the last few posts of the 31 Days of SSIS series I’ve talked about developing packages. As important as developing packages is, we must be prepared to develop them in a way that they can be securely deployed.
As part of this need, this post will focus on the use ofenvironmental variables with SSIS packages. Environmental variables allow variables to be specified and set within an operating system environment. The value is available to all applications and exists from one reboot to another. A very common example of an environmental variable is TEMP variable which directs the operating system to use C:\Window\Temp. At least, that is where it is set on my Windows 7 laptop.
WHY ENVIRONMENTAL VARIABLE?
Since environmental variables are specific to an environment, they can be use to store information specific to that environment. In this context of the term “environment”, I am referring to your development, test, and production environments. Each of these will have some statics values that are dependent on the environment.
When looking at these environments, there are some common factors that all SSIS packages will have regardless of the purpose of the package. For instance, all of the SSIS packages that reference the AdventureWorks database in the development environment will use one server. All of those same SSIS packages will reference a different server or instance when they are executed in the test or production environment. These settings that are specific to your environments are the configuration values of SSIS packages that are prime candidates to use with environmental variables
When environmental variables are used properly with SSIS packages, they can be deployed to any environment and perform within the confines of that environment. This when an SSIS package is developed on my workstation, the local environmental variable can reference the development servers. When I deploy it to the test environment, the testers can execute the package and it will automatically reference the test environment. Once they validate the package, it can be deployed to production and the changes for that environment will just occur.
ENVIRONMENTAL VARIABLE SETUP
Let’s suppose, as mentioned above, we have a number of SSIS packages that will be referencing the AdventureWorks database. We will want the packages that run on my laptop to point to a local instance. When executed on other servers we will need to point to other instances or servers.
In order to accomplish this we need an environmental variable and to configure it in an SSIS package. To start this setup, open the System Properties window for the desktop or server that needs to have the environmental variable created. This can be found in Computer –> Properties –> Advanced system settings. From this window choose the Environmental Variables button.
From the Environmental Variable window select the New… button. This will open a dialog box where you are able to type in the name of the variable and it’s value. For this demonstration, name the variable AdventureWorks. That value should be – Data Source = STRATE-02\SQL2008R2; Initial Catalog = AdventureWorks; Provider = SQLNCLI10.1; Integrated Security = SSPI; Application Name = EnvironmentalVariable;.
Of course, that connection string will be modified slightly to reference your own SQL Server. I’m pretty certain my server is locked down enough to prevent you from gaining access to it.
After this is done, the environmental variable will be available in the list of variables for the environment. If you used the variable named above, it will appear at the top 
of the list in the Environmental Variables window. Unfortunately, there is one more task that needs to be completed to finish setting this up. You desktop, laptop, or server will need to be restarted. Until then the variable will be there but it won’t be accessible to SSIS. Forgetting this can be a pain so I will reiterate – reboot after you setup the variable.
of the list in the Environmental Variables window. Unfortunately, there is one more task that needs to be completed to finish setting this up. You desktop, laptop, or server will need to be restarted. Until then the variable will be there but it won’t be accessible to SSIS. Forgetting this can be a pain so I will reiterate – reboot after you setup the variable.
Now that you have a variable to work with, open up an SSIS package. Add a connection to AdventureWorks in Connection Managers. From here we are ready to begin.
Open the Package Configuration Organizer window. This can be accessed through the menu at or by right-clicking in the Control Flow. In this window select Enable package configurations and click the Add… button.
In the Package Configuration wizard, choose the Configuration type of Environmental variable. The drop-down box will list the AdventureWorks environmental variable along with all other environment variable on the system. Select this item and click Next.
On the next screen, you need to associate the environmental variable with a property in the SSIS package. The property we will want is Connection Managers –> AdventureWorks –> Properties –> ConnectionString. Browse to this item and then select Next.
The last screen will allow you to name the configuration setting. It can be named anything that you’d like. Often I choose to name the configuration after the environmental variable variable for clarity.
Once you click on Finish, the setup of the environmental variable will be complete. All you need to do now is set this up in your other environments and drop the package in and run it. As long as the connection string is pointed to the correct server, this and all other similarly configured packages will have one place that controls how they execute in each environment.
ENVIRONMENTAL VARIABLE WRAP-UP
I find using environmental variables for directing connectivity of SSIS packages to be one of the more perfect uses for it. As long as everyone is on the same page, a lot of the work in deploying packages is removed and you can focus on the features of the SSIS packages rather than the functionality.
The other benefit, as mentioned in the last section, is consolidation of configuration information. Rather than having 5, 10, 20, or more packages to edit when a server moves or information changes – the environmental variable allows for a single point of configuration for the entire environment. One edit and everything starts working with the new configurations.
No comments:
Post a Comment