How to change SSIS variable values in C#

Question

I'm trying to set variables in code on an SSIS package I'm kicking off from C#.

Package pkg = app.LoadPackage(ConfigurationManager.AppSettings["SsisPkg"].ToString(), null);
pkg.Variables["User::FolderPath"].Value = Path.GetDirectoryName(e.FullPath);
pkg.Variables["User::FileName"].Value = Path.GetFileNameWithoutExtension(e.FullPath);

While debugging, I inspect the values directly after setting them, but nothing has changed. I can drill into the value (hover, navigate to it, edit value) so it doesn't seem to be that they aren't editable.

Any ideas what I'm doing wrong here?

Update: Thanks to billinkc, I'm comfortable what I'm doing should work so long as possibly other conditions are met. I've found that direct assignments in code fail (no error, just don't "take") and I cannot edit the values in the watch window. I can edit values when I'm inspecting them.

Could the actual issue be that there's a setting flagging these as read-only?

Answer Found: Needed to make the variables writable before setting the value:

        pkg.Variables["User::FileName"].EvaluateAsExpression = false;

while debugging, what's inside `Path.GetDirectoryName(e.FullPath)` and `Path.GetFileNameWithoutExtension(e.FullPath)`. Are those the actual values? — Daniel Sh., Oct 02 '13 at 23:00
e is a FileSystemEventArgs parameter that indeed has information about a newly created file. — John Spiegel, Oct 03 '13 at 15:38

score 1 · Accepted Answer · edited Oct 03 '13 at 16:36

You're doing it right. I created a basic SSIS package. Has one Variable, FolderPath, type string. There is a single script task that fires an Information event and the contents of that expose the value of the FolderPath variable

enter image description here

I then created a basic C# console app like this

public class InformationListener : DefaultEvents
{
    public override void OnInformation(DtsObject source, int informationCode, string subComponent, string description, string helpFile, int helpContext, string idofInterfaceWithError, ref bool fireAgain)
    {
        //base.OnInformation(source, informationCode, subComponent, description, helpFile, helpContext, idofInterfaceWithError, ref fireAgain);
        Console.WriteLine(string.Format("{0} {1}", subComponent, description));
    }

}

class Program
{
    static void Main(string[] args)
    {
        string sourcePackage = string.Empty;
        string path = string.Empty;
        string variableName = string.Empty;
        string designValue = string.Empty;
        string newValue = string.Empty;
        InformationListener listener = null;

        sourcePackage = @"J:\Src\SO\SSIS\Package.dtsx";
        path = @"J:\runtime";
        variableName = "User::FolderPath";
        listener = new InformationListener();

        Application app = new Application();
        Package pkg = null;
        Variable ssisVariable = null;
        pkg = app.LoadPackage(sourcePackage, null);

        ssisVariable = pkg.Variables[variableName];
        designValue = ssisVariable.Value.ToString();

        Console.WriteLine(string.Format("Designtime value = {0}", designValue));

        ssisVariable.Value = path;

        newValue = ssisVariable.Value.ToString();
        Console.WriteLine(string.Format("new value = {0}", newValue));

        DTSExecResult results = DTSExecResult.Canceled;

        results = pkg.Execute(null, null, listener, null, null);

        Console.WriteLine("Press any key to continue");
        Console.ReadKey();

    }
}

As you can tell from the variable inspection

enter image description here

and from my print statements

enter image description here

the design-time value of C:\designTime goes to J: because I forgot to escape my string above but we can pretend it shows J:\runtime.

All this said, unless we serialize the package with a call to the SaveToXml method, the value of User::FolderPath resets to the design time value once the object goes out of scope. Permanently updating would look like

app.SaveToXml(sourcePackage, pkg, null);

OP EDIT this discussion and examples led me to the answer: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dad8e218-1fe0-49db-89da-5715fb6d4b21/sql-2008-r2-ssis-c-script-task-not-setting-variable

Thanks for validating the approach is okay. It now leaves me with why the variables are semi-read-only. My in-code value assignments fail as do attempts to edit through the watch window, but drilling in while inspecting the variables allows me to edit. — John Spiegel, Oct 03 '13 at 15:22
If you create a super simple example like the above, do you still get the same result? Could it be that there is an expression assigned to the Variable or a configuration that is preventing your assignment from coming through? — billinkc, Oct 03 '13 at 15:26
Thanks again. The discussion led me to find the answer I've added. Don't know how many hours of beating my own head against the wall it would've taken otherwise. — John Spiegel, Oct 03 '13 at 16:36
It did indeed. What I still find really odd is I could "back-door" around it while inspecting the values, but not in the Watch window or code. Seems to be a useful nugget in there somewhere around how to circumvent/troubleshoot issues and how VS actually works internally. (I'll leave that to smarter people, though) — John Spiegel, Oct 03 '13 at 16:41

How to change SSIS variable values in C#

1 Answers1

Linked