如何在 C# 中更改 SSIS 变量值 [英] How to change SSIS variable values in C#

问题描述

我正在尝试在从 C# 开始的 SSIS 包的代码中设置变量.

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?

更新:感谢 billinkc，只要满足其他条件，我所做的事情就应该可以正常工作.我发现代码中的直接赋值失败(没有错误，只是不要接受")并且我无法在监视窗口中编辑值.我在检查值时可以编辑它们.

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;

推荐答案

你做得对.我创建了一个基本的 SSIS 包.有一个变量，FolderPath，类型字符串.有一个触发信息事件的脚本任务，其内容公开了 FolderPath 变量的值

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

然后我创建了一个像这样的基本 C# 控制台应用程序

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();

    }
}

从变量检查中可以看出

来自我的打印声明

C:\designTime 的设计时值转到 J: 因为我忘记对上面的字符串进行转义，但我们可以假装它显示 J:\runtime.

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.

综上所述，除非我们通过调用SaveToXml 方法来序列化包，否则一旦对象超出范围，User::FolderPath 的值将重置为设计时的值.永久更新看起来像

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 这个讨论和例子让我得到了答案:http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dad8e218-1fe0-49db-89da-5715fb6d4b21/sql-2008-r2-ssis-c-script-task-not-setting-variable

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

这篇关于如何在 C# 中更改 SSIS 变量值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！