Saturday, 13 June 2015

Implementing Registry entry configuration in SQL Server Integration Services

Scenario - Registry entry configuration is used very rarely. In my experience I never got the situation in which it is compulsory to use Registry entry configuration. I also did not see any particular advantage to have the Registry entry configuration in SQL Server Integration Services. According to few people Registry entry configuration may useful to create the system dependency in which it can store any code value into Value data in Registry key and later be validated for running the SSIS Package.

Implementing the Parent package configuration -

Let's create a simple and short implementation for Registry entry configuration. To verify this configuration let's create a key in the windows registry and fetch it from the SSIS package using a script task.

Step 1 - Press    + R and type Regedit then press Enter, It will open the Windows Registry 


Step 2 - Expand the HKEY_CURRENT_USER then right click Go to New then choose Key in order to create a new key. We could also use any existing key but for clear understanding let's create a new one.

Step 3 - Let's name of the key SSIS_Reg. Right click on it and choose New then choose String Value. Provide name Value as name for the String Value

Step 4  Right click on Value and select Modify... then we will provide some value into it. 

Step 5 - Let's provide the value as 'This is registry entry configuration in SSIS' in the Value data then click on OK button. 

Step 6 - Registry entry value has been created till the Step 6. Now from then Step 7 on wards we will fetch this Registry entry value from a SSIS package. Now  open SQL server Business Intelligence Development Studio and create a new Integration Services Project -

Step 7 - Open the default package and create a Variable of string type. let's say Var_Regedit.

Step 8 - Now we need to integrate this variable with the Windows Registry key (SSIS_Reg) which is created at Step 3

Step 9 - Open the package and select Package configurations by right clicking on the package.
Check on Enable package configuration and click on Add button -

Step 10 - Select Registry entry from the Configuration type dropdown list and specify the Window Registry Key name "SSIS_Reg" which we have already declared at the Step -3 then click on Next button.

Step 11 - Now it's time to integrate the Registry entry key with the package variable Var_Regedit which is created at Step 7. To do that select it's Value  and click on Next button.

Provide the Configuration name and click on Finish button,

Step 12 - Let's drag and drop a Script task from the toolbox. Provide Var_Regedit as ReadOnlyVariables then click on Edit Script button.

Step 13 - Provide the below lines of code into script editor -
public void Main()
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;

Close the script task editor and click on OK button into the script task.

Step 14 -  Now we are done with all the changes. Let's run the package, It should populate the message box containing the value of Windows Registry Entry which we have created at Step 5.

As per all the above configuration, Message box should contain the value as per below screen shot.

So in this way we have completed successfully the Registry entry configuration in SQL Server Integration Services.

Important points -

1. Registry entry configuration value can be stored either in an existing registry key or in the new registry key created.

2. This configuration type in SSIS used very rarely.

3. It should not use to store the database name or any user credentials if there is any security concern.

4. At the time of deployment same registry entry key must be available with the appropriate value data.

5. Only a single value can be stored in one registry key.

Please provide your comments if the above post is useful for you,

No comments:

Post a Comment