I have an SSIS data flow that contains a script Component as a source. I'd like to generate the source data by running a script on an SQL Server database. The connection string to be used to connect to the database is set to be sensitive. How can I read this sensitive parameter inside the script component using C#?
In a Script Task, usually it is read for example as the following:
string mySecretPassword = Dts.Variables["$Project::MySecretPassword"].GetSensitiveValue().ToString
The Variable class in a Script Task has a GetSensitiveValue method. However, the Script Component Variable implements the interface Microsoft.SqlServer.Dts.Runtime.Wrapper.Variable which has no GetSensitiveValue method defined.
Let's assume the connection string is a project parameter for now.
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.
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.
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.