Configuration tables are a best practice in just about any SSIS environment. They make it easy to update multiple packages from a single change. But one of the issues with configuration tables is the location of the server is different on each server. You may have a Server name of Dev on your development server and a Server name of Prod on the production server. This has to be updated in the package before the package can be moved to the new server. If you have several configuration tables then you have to update several files. This can be time consuming and tedious. Environment variables can take away this work and make the transfer from server to server easy.

Environment variables can hold the name of the server on each server. In the Configuration Manger you place in the name of the Environment Variable. The Environment Variable does not hold the value of the variable or connection that is passed to the package. The environment variable holds the name of the server. This value tells the package where to look for the configuration table and reads the configuration table for the configured values to pass into the package.

You can think of the Environment Variables as pointers for the package. When you move a package to another server it will look for an Environment Variable. It does not matter on which server your package is running. As long as the server has an Environment Variable named the proper name and it contains the name of the proper server, the package will run properly.

You will now walkthrough a small example of building a package with a configuration table and with environment variables.

1. Open BIDS (Business Intelligence Development Studio)

2. Click File>New>Project

3. Select Integration Services Project

4. Named the Project Environment Test

5. Click OK

6. Right Click on the SSIS Packages Folder and click New SSIS Package

7. Change the Name of the Package to EnvironmentVar

8. Click yes to change package object as well

9. Right Click on the Control Flow of the package and select Variables

10. Create a String Variable name strServerName

11. Set the Value of the strServerName to “Localhost” (You local server name)

12. Close the Variable Window

13. Drag in a script task to the Control Flow

14. Double Click on the Script Task to open the Editor

15. Set the Read only Variables property to strServerName

16. Click Edit Script (Design Script in SQL 2005)

17. Replace the “Add your code here” comment with the following VB code

MsgBox(Dts.Variables(“strServerName”).Value)

(This will cause a message box to appear showing the value of the variable)

18. Save and Close the Script Editor

19. Click Ok in the Script Task Editor

20. Debug the Package

21. A Popup with the word “Development” will appear

22. Click OK in the popup box

23. Stop the package from debugging

Now you will create a two set of configuration files on your local machine. You will need to have two folder locations from which you can read and write. I have created two locations on my machine: C:\ConfigQA and C:\ConfigProd. These would represent the QA and Production machine on different servers. In your business environment you may have mapped drive or UNC paths on different servers. We also need to create an Environment Variable on your local machine. Each operating system is different on how to get to the Environment Variable Screen. The following are the instructions for Windows 7. Open the Control Panel, click on System and Security, click System, click Advanced System Settings, click on the advanced tab, and click the Environment Variables Button.

24. Create a New Environment Variable called ConfigLocation

25. Set the Value to C:\ConfigQA

26. Click Ok and return to BIDS

27. Right Click in the Control Flow and Select Package Configurations

28. Place a check next to Enable Package Configurations

29. Click Add

30. Click Next (If the Welcome window Appears)

31. Leave the Configuration Type to XML Configuration File

32. Place a dot in the Radio Button next to “Configuration Location is stored in an Environment Variable”

33. Select the ConfigLocation Environment Variable(You may need to restart BIDS for it to Show)

34. Click Next Finish and Close

35. Click