使用SSIS中的脚本任务设置变量值。

huangapple go评论82阅读模式
英文:

Setting a variable value using a Script Task in SSIS

问题

使用脚本任务,我试图将变量A的值设置为变量B的值。它们都在同一个包中。
这是我使用的代码:

public void Main() {

    Dts.Variables["User::VariableA"].Value = Dts.Variables["User::VariableB"].Value;

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

这两个变量都是字符串类型,并且标记为可读/可写。我也尝试在脚本中使用ToString()方法将它们转换为字符串,但也没有成功。

有人知道我错过了什么吗?

英文:

Using Script Task, I am trying to set the value of a variable A the value of variable B. Both in the same package.
This is the code I used:

public void Main() {

              Dts.Variables["User::VariableA"].Value =     Dts.Variables["User::VariableB"].Value;


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

Both variables are string type, and marked as Read/Write. I tried to convert them to string in the script too using ToString() method, but it didn't work as well.

Anyone knows what did I miss?

答案1

得分: 2

你没有错过任何事情。或许,你在这里过于简化了问题,因为最小复现并未显示相同的行为。

我创建了一个如图所示的包。两个字符串变量,变量A和变量B。它们被初始化为"My Value is A"和"My Value is B."。我通过将当前变量打印到输出窗口来确认这一点。然后我使用你的代码来更新A的值。然后我重用相同的检查代码,看到了预期的最终值"My Value is B"对于两个变量。

SCR Echo Value

这是相同的脚本任务,在我更新值之前和之后。它只是将读/写变量发送到信息事件,以便它们显示在结果/输出选项卡上。

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_5e898ddfc3e24549a581a83a3cabab4d
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            bool fireAgain = false;
            foreach (var item in Dts.Variables)
            {
                Dts.Events.FireInformation(0, "SCR Echo Back", string.Format("{0}->{1}", item.QualifiedName, item.Value), "", 0, ref fireAgain);
            }

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

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

SCR Assign B to A

这是提供的代码

using System;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_3a3b3c73d21c472aba3c2ddbad1481b1
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            Dts.Variables["User::VariableA"].Value = Dts.Variables["User::VariableB"].Value;
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

还可能是什么呢?

看错地方了

变量窗口显示了包的“设计时”值。也许你认为变量没有被更新是因为你在看这个值。

使用SSIS中的脚本任务设置变量值。

相反,如果你设置了断点或者其他什么,看看“本地窗口”下的“变量”,它会显示当前的“运行时”值。

使用SSIS中的脚本任务设置变量值。

表达自己

有一个表达式在起作用。变量上的表达式将始终优先于分配的值。它不会引发尝试将值分配给Value属性的错误,但它不会“固定”,因为EvaluateAsExpression属性为true意味着始终根据Expression属性计算值。

在较新版本的BIDS/SSDT中,他们通过视觉提示使这一点更加明显。属性可以通过F4访问,但变量窗口现在显示了一个符号,f(x),位于变量名称旁边,以及淡灰色文本用于数据类型和值。

使用SSIS中的脚本任务设置变量值。

英文:

You missed nothing. Or perhaps, you've oversimplified the problem for here as a minimal reproduction does not show the same behaviour

使用SSIS中的脚本任务设置变量值。

I created a package as shown. Two string variables, Variable A and Variable B. They were initialized to "My Value is A" and "My Value is B." I confirm this by printing the current variables to the output window. I then use your code to update the value of A. I then reuse the same inspection code and see the expected final value of "My Value is B" for both variables.

SCR Echo Value

This is the same script task, before and after I update the value. It simply emits the read/write variables to the Information event so they show on the Results/Output tab

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_5e898ddfc3e24549a581a83a3cabab4d
{
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
		public void Main()
		{
            bool fireAgain = false;
            foreach (var item in Dts.Variables)
            {
                Dts.Events.FireInformation(0, "SCR Echo Back", string.Format("{0}->{1}", item.QualifiedName, item.Value), "", 0, ref fireAgain);
            }

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

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
	}
}

SCR Assign B to A

This is the supplied code

using System;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_3a3b3c73d21c472aba3c2ddbad1481b1
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
	    public void Main()
	    {
            Dts.Variables["User::VariableA"].Value = Dts.Variables["User::VariableB"].Value;
            Dts.TaskResult = (int)ScriptResults.Success;
	    }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

What else could it be?

Looking in the wrong place

The Variables window shows the Design-Time value of the package. Perhaps you're thinking that the variable isn't being updated because you're looking at that value.

使用SSIS中的脚本任务设置变量值。

Instead, if you have a breakpoint or something, look at the Locals window under Variables and that will show what the current Run-Time value is

使用SSIS中的脚本任务设置变量值。

Express yourself

There's an expression at play. An expression on a variable will always supersede an assigned value. It won't raise an error trying to assign to the Value property but it won't "stick" because the EvalateAsExpression property being true means the Value is always computed based on the Expression property.

In the newer versions of BIDS/SSDT, they make this much more readily apparent with visual cues. The Properties are available via F4 but the Variables window now shows a glyph, f(x), next to the variable name along with pale grey text for the Data Type and Value.

使用SSIS中的脚本任务设置变量值。

huangapple
  • 本文由 发表于 2023年1月8日 23:29:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/75049008.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定