Skip to content

SSIS

Jump to bottom
Jeremy Bailey edited this page Nov 6, 2020 · 10 revisions

SSIS support

Create Variables

Create Package Variables for:

  • FTEnvName [String] (contains the Environment Name)
  • FTAppName [String] (contains the Application Name)
  • FTBaseURL [String] (contains the Base URL)
  • FTURL [String]
    • This should have the expression as: @[User::FTBaseURL] + "/api/public/getApplicationFeatureToggles?Environment=" + @[User::FTEnvName] + "&applicationName=" + @[User::FTAppName]

Note: these are Case Sensitive

If you are deploying the SSIS package to a SQL server and running multiple environments, then you should also create a parameter, and set the Expression for FTEnvName to be the paramater, ie @[$Package::FTEnv]

Next, Create User Variables for each Feature Toggle that you wish to access in your code. The name should match exactly. Each Variable should be setup as a Boolean.

Create Parameter (optional)

If you have enabled JWT security, then create a package parameter JWTKey, Data type = String, Sensitive = True This will be used to pass JWT Signing Token so that the value can be easily change. This should be marked as Sensitive, so that it is encrypted in the code. A value can be supplied for local testing, this value will come from the Moggles Server configuration file/JWT Settings.

Read Feature Toggles, Set Variables

  • Create a Script Task called "Get Feature Toggles" (this should be your first task in your flow)
  • Edit the Task
  • For ReadOnlyVariables, select User::FTUrl
    • (Required w/ JWT) select $Package::JWTKey
  • For ReadWriteVariables, select any other feature flag variable you wish to set Script Task

Next, edit the Script, modify ScriptMain.cs by adding the following code This will:

  • Create an http client
  • Access the API endpoint
  • Read and parse the JSON
  • Set any matching variables

First, add a reference to System.Web.Extensions

Then change the using statements to:

using System;
using System.Net;
using System.IO;
using System.Web.Script.Serialization;

The replace the existing Main() with this and the following code

        public void Main()
        {
            string wUrl = Dts.Variables["User::FTUrl"].Value.ToString();
            string tokenSigningKey = Dts.Variables.Contains("$Package::JWTKey") ? Dts.Variables["$Package::JWTKey"].GetSensitiveValue().ToString() : null;
            FeatureToggles[] featureToggles = GetFeatureToggles(wUrl, tokenSigningKey);
            foreach (FeatureToggles featureToggle in featureToggles)
            {
                if (Dts.Variables["User::" + featureToggle.FeatureToggleName] != null)
                {
                    Dts.Variables["User::" + featureToggle.FeatureToggleName].Value = featureToggle.IsEnabled;
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        private static FeatureToggles[] GetFeatureToggles(string wUrl, string tokenSigningKey)
        {
            var httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
            if(tokenSigningKey != null) { 
                httpWReq.PreAuthenticate = true;
                httpWReq.Headers.Add("Authorization", "Bearer " + GenerateJwtToken(tokenSigningKey));
            }
            var httpWResp = (HttpWebResponse)httpWReq.GetResponse();
            FeatureToggles[] jsonResponse = { };

            try
            {
                //Test the connection
                if (httpWResp.StatusCode == HttpStatusCode.OK)
                {

                    var responseStream = httpWResp.GetResponseStream();
                    string jsonString;

                    //Set jsonString using a stream reader
                    using (StreamReader reader = new StreamReader(responseStream))
                    {
                        jsonString = reader.ReadToEnd().Replace("\\", "");
                        reader.Close();
                    }

                    //Deserialize our JSON
                    JavaScriptSerializer sr = new JavaScriptSerializer();
                    //JSON string comes in with a leading and trailing " that need to be removed for parsing to work correctly
                    //The JSON here is serialized weird, normally you would not need this trim
                    jsonResponse = sr.Deserialize<FeatureToggles[]>(jsonString.Trim('"'));
                }
            }
            //Output JSON parsing error
            catch
            {
                return jsonResponse;
            }

            return jsonResponse;
        }

        //Placeholder for signing function
        private static string GenerateJwtToken(string tokenSigningKey)
        {
            return null;
        }


        //Class to hold our work group metrics
        class FeatureToggles
        {
            public string FeatureToggleName { get; set; }
            public bool IsEnabled { get; set; }
        }

Extra Steps with JWT

If you have enabled JWT, you need to take some additional steps In Addition to creating the parameter JWTKey, and passing that into the script as a ReadOnly variable, you will need to modify the C# code and register some DLLs.

Modify the code

Add these using statements

using System.IdentityModel.Tokens.Jwt;
using System.Text;
using Microsoft.IdentityModel.Tokens;

Change the method GenerateJwtToken() to:

        private static string GenerateJwtToken(string tokenSigningKey)
        {
            var tokenHandler = new JwtSecurityTokenHandler();
            var credentials = new SigningCredentials(new SymmetricSecurityKey(Encoding.UTF8.GetBytes(tokenSigningKey)), SecurityAlgorithms.HmacSha256Signature);

            var securityToken = new JwtSecurityToken(null, null, null, null, DateTime.Now.AddMinutes(1.0), credentials);

            return tokenHandler.WriteToken(securityToken);
        }

Register DLLs

In short, we need to register 4 dlls into the GAC (Global Assembly Cache)

  • Microsoft.IdentityModel.Tokens.dll
  • Microsoft.IdentityModel.JsonWebTokens.dll
  • Microsoft.IdentityModel.logging.dll
  • System.IdentityModel.Tokens.Jwt.dll

I did this by

  1. Adding some packages from NuGet
    1. Right Click on the solution and Manage NuGet Packages...
    2. Select the Browse tab
    3. Search for System.IdentityModel.Tokens.Jwt and install
    4. This should automatically install the other packages
  2. Locating the dlls
    1. Click on the Project Name
    2. Notice the Project Folder entry in the properties window, Copy the value
    3. Open FileExplorer to this path, and go up 1 folder, you should see a "packages" folder
  3. Running gacutil to install them
    1. Open a Visual Studio Developer Command Prompt in Administrator Mode
    2. Find the full path of the dlls list in each folder for the appropriate project type
      1. for .NET 4.5, then it is ...\packages\System.IdentityModel.Tokens.Jwt.6.8.0\lib\net45\System.IdentityModel.Tokens.Jwt.dll
      2. for .NET 4.61 abd abive, then it is ...\packages\System.IdentityModel.Tokens.Jwt.6.8.0\lib\net461\System.IdentityModel.Tokens.Jwt.dll
    3. Run gacutil /i <full path to dll>

Note: You will need to do this on any SQL server that you deploy the SSIS package too

Use Feature Toggles

Once the Feature Toggles are set, you can use them by attaching an expression to the Constraint. Assuming your SSIS package already had existing functionality setup, then create a second task for your alternate path (when the Feature Toggle has a different value). Drag a new constraint arrow from the previous task to the new option you created. You should now have branching options. You will also need to drag a new constraint arrow from the new option to the successor task.

Once the constraint arrows are in place, select one of them, and in the properties window change "EvalOp" to "Expression". Click off the constraint (this saves it), then select it again to edit the properties. In the "Expression" field, use something like @[User::TestFeatureToggle] == true where the variable name is equal to the Feature Toggle variable you created earlier. use the value of true or false depending on you want to the logic to flow. Repeat this process for your second option, using the same variable name, but the opposite boolean value. This will create branching logic in your SSIS Package. Your end result will look something like this:

Branching Flow

An assumption is made that the inputs and outputs (contracts) will be the same for both options, but the internal logic, query or task details will be different.

Clone this wiki locally