SSIS Forloop：根据迭代次数为变量名赋值 [英] SSIS Forloop: assign value to variable name based on iteration number

问题描述

var1
var2
我试图使用ForLoop容器为多达10个不同的变量赋值。
。
。
var10

基于迭代次数。

我需要循环来迭代一定数量的时间基于一个变量已被分配一个整数值的基础上查询的结果集...这部分工作正常。

我在一个损失如何告诉forloop哪个变量用来分配值....即如果我在迭代＃1然后分配值给var1，迭代＃2然后分配值给var2 ....等等。

任何指导都将不胜感激。

解决方案

我不是100％确定为什么你想要这种方法，回答这个问题，你可以走2条路线在2005-2008 R2的环境中，你的控制流程将会可能看起来像这样

我定义了11个变量，都是Int32类型。计数器，然后从变量1到变量10。

My For Loop配置为

将当前值分配给一个变量将在脚本任务。我已经将Counter变量设置为只读变量，而Variable1-10是读取写入。然后在那里有一个基本的Switch语句来处理映射。

$ public $ Main $
{
int counter =（Int32）Dts.Variables [User :: Counter]。

switch（counter）
{
case 1：
Dts.Variables [User :: Variable1]。Value = counter;
break;
案例2：
Dts.Variables [User :: Variable2]。Value = counter;
break;
情况3：
Dts.Variables [User :: Variable3]。Value = counter;
break;
案例4：
Dts.Variables [User :: Variable4]。Value = counter;
break;
case 5：
Dts.Variables [User :: Variable5]。Value = counter;
break;
案例6：
Dts.Variables [User :: Variable6]。Value = counter;
break;
案例7：
Dts.Variables [User :: Variable7]。Value = counter;
break;
case 8：
Dts.Variables [User :: Variable8]。Value = counter;
break;
案例9：
Dts.Variables [User :: Variable9]。Value = counter;
break;
case 10：
Dts.Variables [User :: Variable10]。Value = counter;
break;
默认值：
break;
}
Dts.TaskResult =（int）ScriptResults.Success;

$ / code>

SQL Server 2012版本提供了一个开箱即用的表达式任务。如果你喜欢这种方法，并且不在2012年，示例项目有一个表达任务，你可以建立并安装在你的服务器上。

无论如何，你可以使用优先约束表达式和约束然后在那里测试你的值。

即使我有2012年的安装，我仍然可能会使用代码路由，因为我认为这是一个更清晰的方法。尽管两种方法都使用硬编码的VariableN映射。 @ STLRick（密苏里州代表！）认为将一个复杂的变量存储在一个Object类型的变量中可能是一个更好的方法，虽然我承认我不太满意为什么你想要存储一个For循环的值为你已经知道你的范围（即使基于动态的初始值和终值）

I am trying to use a ForLoop container to assign values to up to 10 different variables....

var1 var2 . . . var10

based on the iteration number.

I need the loop to iterate a certain number of times based on a variable that has been assigned an integer value based on a result set from a query...that part is working just fine.

I am at a loss as how to tell the forloop which variable to use to assign the value....ie if I'm at itereation #1 then assign value to var1, iteration #2 then assign value to var2....and so on.

Any guidance would be much appreciated.

解决方案

While I'm not 100% sure on why you'd want this approach, to answer the question, you could go 2 routes

In a 2005-2008R2 environment, your control flow would probably look something like this

I've defined 11 variables, all of type Int32. Counter and then Variable1 through Variable10.

My For Loop is configured as

The assignment of the current value to a variable will be handled in a Script Task. I have set the Counter variable as a read only variable, and Variable1-10 are read writes. I then have a basic Switch statement in there to handle the mapping

    public void Main()
    {
        int counter = (Int32) Dts.Variables["User::Counter"].Value;

        switch (counter)
        {
            case 1:
                Dts.Variables["User::Variable1"].Value = counter;
                break;
            case 2:
                Dts.Variables["User::Variable2"].Value = counter;
                break;
            case 3:
                Dts.Variables["User::Variable3"].Value = counter;
                break;
            case 4:
                Dts.Variables["User::Variable4"].Value = counter;
                break;
            case 5:
                Dts.Variables["User::Variable5"].Value = counter;
                break;
            case 6:
                Dts.Variables["User::Variable6"].Value = counter;
                break;
            case 7:
                Dts.Variables["User::Variable7"].Value = counter;
                break;
            case 8:
                Dts.Variables["User::Variable8"].Value = counter;
                break;
            case 9:
                Dts.Variables["User::Variable9"].Value = counter;
                break;
            case 10:
                Dts.Variables["User::Variable10"].Value = counter;
                break;
            default:
                break;
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }

The 2012 release of SQL Server provides an out of the box Expression Task. If you prefer this approach and are not on 2012, the samples project had an Expression Task you could build and install on your server.

At any rate, you make use of precedence constraints of Expression and Constraint and then test for your value in there.

Even if I had a 2012 installation, I'd probably still go with the code route as I think it's a cleaner approach. Both approaches though leave you with hard coded mappings of value to VariableN. @STLRick's (Missouri represent!) thought of storing a complex variable in a variable of type Object would probably be a preferable approach although as I admitted I don't quite grok why you'd want to store off the values of a For Loop as you'd already know your ranges (even if based on dynamic initial and terminal values)

这篇关于SSIS Forloop：根据迭代次数为变量名赋值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！