Despite the lack of a GetSensitiveValue method existing in the Script Component, I was able to access the value just fine.
What I did fumble with was my Package Protection level and how it interacts with Project Parameters that are marked as Sensitive.
I defined a Project Parameter named MySecretPassword and populated it with SO_71308161 and marked it as sensitive.
Script Source
I defined a single column output and my intention was to just push the password into the dataflow to confirm I was able to access it
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public string MySecret;
public override void PreExecute()
{
base.PreExecute();
MySecret = Variables.MySecretPassword;
}
public override void CreateNewOutputRows()
{
Output0Buffer.AddRow();
Output0Buffer.Column = this.MySecret;
}
}
I then routed the data to a derived column and dropped a data viewer between the two. My code did not throw an exception but it did show an empty string.
If I tried to access my sensitive parameter value in a script task using myVariable.Value it would throw an error as expected but I was getting my sensitive value back.
Package Protection Level
I love StackOverflow questions that teach me things. My default mode is to define projects that use a Project & Package protection level of DontSaveSensitive. Which is incompatible with using Sensitive project parameters. Not incompatible in that it will throw an exception, but when you run the package, accessing that value will be blanked out (for strings at least).
As soon as I change the Project and then Package's protection level to EncryptSensitiveWithUserKey (or anything that isn't DontSaveSensitive), both the Script Task's .GetSensitiveValue() and the Script Component's Variables.MySecretPassword worked.
