SSIS Package Configuration - Environment variable

Question- Explain 'Environment variable' type package configuration in SSIS?

Answer-   For better understanding about 'Environment variable' type package configuration, Let's first understand about 'Environment variable'.

Environment variable- A system environment variables are defined by Windows and apply to all computer users.Changes to the system environment variable are written to the registry, and usually require a restart of the SSIS package to become effective. 

To complete environment variable package configuration, there are two main points to complete - 
1. Add an environment variable in user system
2. Access of the environment variable from SSIS package

[Add an environment variable]
In order to add an environment variable follow the below steps -
Step 1- Go to 'My computer' icon --> right click and select 'Properties'.
Step 2- Click on 'Advance system settings' from the left.
(Refer the below screen shot)

Step 3- Click on environment variables button under system properties.
(Refer the below screen shot)

Step 4- Click on 'New' to create a new system environment variable.
(Refer the below screen shot)

Step 5- Enter the variable name and variable value and Click on 'Ok' button.
(Refer the below screen shot)
Let's say the variable name is 'ConnectionString' and value is 'Sample connection string'.

[Access of the environment variable from SSIS package]
Follow the below steps to access the enviornment variable from SSIS package -
Step 1- Close and reopen the SSIS package otherwise newly changes in enviornment variable will not reflect into the SSIS package.
Step 2- Create a package variable to hold the value of enviornemnt varible, Let's say 'StrCon'
(Refer the below screen shot)

Step 3- Right click on SSIS package body and select Package Configurations...
Step 4- Click on 'Add' button in 'Package Configuration Organizer'
(Refer the below screen shot)

Step 5- Select 'Enviornment variable' as configuration type from package  configuration wizard.
(Refer the below screen shot)

Step 6- Choose the proper enviornment varible name from the dropdownlist which was created earlier.
(Refer the below screen shot)

Step 7- Click on Next, select package variable(StrCon) and select value after expanding it as target property then click on Next.
(Refer the below screen shot)

Step 8- Click on Next and give proper name to the current configuration then click on finish.
(Refer the below screen shot)

Step 9- Now it's time to fetch the value of enviornment variable through SSIS package for testing reasons, Select a script task into package body

Step 10- Provide read only variable as 'StrCon' in script task editor and click on 'Edit Script' button.
(Refer the below screen shot)

Step 11- Write the below line of code under 'Main' method in script task then build and close the script task editor.
MessageBox.Show(Dts.Variables["StrCon"].Value.ToString());

Step 12- Run the package now, you should see the below popup.
(Refer the below screen shot)


If the above popup is coming than it mean your enviornment variable configuration has been comfigured successfully.

Note - 
1. It is required to close and reopen the SSIS solution otherwise enviornment variable will not reflect into SSIS package.
2. User should have admin rights in order to add an enviornment varible.

I hope this article will make sense to the readers, Please mark your comments.

No comments:

Post a Comment