How to dynamically set a user parameter in a REST Connection.

How to dynamically set a user parameter in a REST Connection.

Create a REST Connection with a custom configuration to use for our example.

Let’s start by creating a REST Connection configuration that has authentication with two user parameters. We will set their values dynamically from variables using an expression.
1. Right click in “Connection Managers” and choose “New Connection”.

2. Choose type “REST” in Connection Manager type and click the “Add” button.

3. Choose “{Custom}” for the Configuration and then click the wrench icon that appears to the right of the configuration drop down.

4. Choose to “Start from scratch (using the default template)” and then click the “OK” button.

5. Fill in the following values appropriately and then click on “0 authentication” to create our first authentication.

  1. Configuration Name: MyTestConfiguration
  2. Base URL:

6. Choose “Basic Authentication” in the “Create a new authentication using template:” drop down.  Then click the “OK” button.

7. We now have two User Parameters and one Parameter that were created from the new Basic authentication from a template. Click “2 items” link to see the two User Parameters

8. Here we see the two User parameters that were created in the Basic Authentication template.  These are the User Parameters we are going to use in our example to set User Parameters dynamically from a variable using an SSIS expression.

Once you have viewed the two User Parameters, click on “Basic Authentication” in the wizard breadcrumb navigation to return to the “Basic” authentication.

9. Now for informational purposes only, click the “1 item” link to view the Parameter that was created from the template.

10. You can see the “Authorization” parameter.  Now click it to see it’s details.

11. From here you can see how the two User Parameters are passed to the one Authorization parameter in order to complete the value string for the parameter.

12. We now have a REST Connection that is ready to receive values for the “Username” and “Password” User Parameters.

You could now enter the values for the Username and Password directly in the connection, but what if we want to set these values dynamically from package variables? Click the “OK” button to close the REST Connection and Read on!

Dynamically update the user parameters Name and Password.

1. Let’s create two package variables named MyUserNameVariable and MyPasswordVariable. Right click the “Control Flow” canvas and choose variables.

2. Click the "Add Variable" icon three times to create three new variables.

3. Change the variable names, data type and values to look like the screen capture below.

So you don’t have to retype it, here is the expression to enter for “MyUserConfigurationVariable”. ("Username=" +  @[User::MyUserNameVariable] + "\r\nPassword=" +  @[User::MyPasswordVariable])

4. Evaluating the expression for “MyUserConfigurationVariable” looks like this: (You can see that passing values for more than one User Parameter into UserConfiguration requires you to separate the assignments of the values with the “\r\n” newline character string.)

5. We now have two variables that can be used in an SSIS Expression to set the values of the two User Parameters in our REST Connection. (You could go another step further here and set the values for the variables from outside the package by "Adding SSIS package configurations for the Package Deployment Model". We won’t go into that here because it is a function of SSIS and not specific to COZYROC.)

Right click on the REST Connection “REST Connection Manager 1” and choose “Properties”.

6. Next to the “Expressions” property, click the three dots (“…”) to create a new expression.

7. Under “Property expressions:” choose UserConfiguration for the “Property” and then click the four dots (“….”) next to the corresponding “Expression” column.

8. Drag the "MyUserConfiguration" variable to the "Expression" text area and then click the "Evaluate Expression" button.  The result is hidden because the property is configured as sensitive because it may contain sensitive information.

9. The configuration is complete.