如何遍历在 For 循环容器中存储为数字的日期值?

本文介绍了如何遍历在 For 循环容器中存储为数字的日期值?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个 For Loop Container 使用日期作为 InitExpression (@Load_Date) 但值是数字格式 (20120229) 和我需要那样做，因为我无法修改它.

I have a For Loop Container using a date as InitExpression (@Load_Date) but the value is in number format (20120229) and I need it that way because I cannot modify it.

现在，我想以这样一种方式设置 AssingExpression 值，即 @Load_Date 变量可以增加，因为它是一个日期.换句话说，分配给变量 @Load_Date 的数值 20120229 应该增加到 20120301，因为这是下一个逻辑日期.

Now, I would like to set the AssingExpression value in such a way that the @Load_Date variable can be incremented as it is a date. In other words the number value 20120229 assigned to the variable @Load_Date should increment to 20120301 because that is the next logical date.

如何在 SSIS 包的 For 循环容器中实现这一点?

How can I achieve this within the For loop container in SSIS package?

推荐答案

这是您可以执行此操作的一种可能方法.下面的示例使用 SSIS 2012.

Here is a possible way that you can do this. The sample below uses SSIS 2012.

假设您有两个包含最小和最大日期范围的变量，但它们以数字格式存储，并且您仍然希望这些值遵守日期规则.

Let's say that you have two variables that contain the minimum and maximum date ranges but they are stored in numerical format and you still want those values to obey the date rules.

声明以下变量:

MinInteger - 此数据类型为 Int32 的变量将以数字格式存储最小日期值.如果您使用的是 SSIS 2012，建议将其创建为参数，以便您可以在运行时轻松配置这些值.

MinInteger - This variable of data type Int32 will store the minimum date value in numerical format. If you are using SSIS 2012, it is advisable to create this as a parameter so you can easily configure the values during runtime.

MaxInteger - 此数据类型为 Int32 的变量将以数字格式存储最大日期值.如果您使用的是 SSIS 2012，建议将其创建为参数，以便您可以在运行时轻松配置这些值.

MaxInteger - This variable of data type Int32 will store the maximum date value in numerical format. If you are using SSIS 2012, it is advisable to create this as a parameter so you can easily configure the values during runtime.

MinString - 这个数据类型为 String 的变量会将数字格式的最小日期值转换为字符串，以便在拆分值以创建日期格式时更容易使用.将表达式设置为 (DT_WSTR, 10) @[User::MinInteger]

MinString - This variable of data type String will convert the minimum date value in number format to string so that it is easier to work with in splitting the values to create the date format. Set the expression to (DT_WSTR, 10) @[User::MinInteger]

MaxString - 这个数据类型为 String 的变量会将数字格式的最大日期值转换为字符串，以便在拆分值以创建日期格式时更容易使用.将表达式设置为 (DT_WSTR, 10) @[User::MaxInteger]

MaxString - This variable of data type String will convert the maximum date value in number format to string so that it is easier to work with in splitting the values to create the date format. Set the expression to (DT_WSTR, 10) @[User::MaxInteger]

MinDate - 这个数据类型为 DateTime 的变量将拆分最小日期的字符串值以制定日期值.将此变量的表达式设置为如下:

MinDate - This variable of data type DateTime will split the string value of minimum date to formulate the date value. Set the expression of this variable to the following:

(DT_DATE)(SUBSTRING(@[User::MinString], 1, 4) + "-" + SUBSTRING(@[User::MinString], 5, 2) + "-" + SUBSTRING(@[User::MinString], 7, 2))

MaxDate - 这个数据类型为 DateTime 的变量将拆分最大日期的字符串值以制定日期值.将此变量的表达式设置为以下内容:

MaxDate - This variable of data type DateTime will split the string value of maximum date to formulate the date value. Set the expression of this variable to the following:

(DT_DATE)(SUBSTRING(@[User::MaxString], 1, 4) + "-" + SUBSTRING(@[User::MaxString], 5, 2) + "-" + SUBSTRING(@[User::MaxString], 7, 2))

如果您使用的是 SSIS 2008 R2 或更早版本，则需要将上述最后四个变量的 EvaluateAsExpression 属性设置为 True.

If you are using SSIS 2008 R2 or previous versions, you need to set the EvaluateAsExpression property of the last four variables mentioned above to True.

Loop - 此数据类型为 DateTime 的变量将用于循环遍历 For 循环容器中的日期值.

Loop - This variable of data type DateTime will be used to loop through the date values in the For loop container.

如下图配置For循环容器:

Configure the For loop container as shown below:

InitExpression:@[User::Loop]=@[User::MinDate]
EvalExpression:@[User::Loop]<=@[User::MaxDate]
赋值表达式:@[User::Loop]=DATEADD("dd", 1, @[User::Loop])

示例将值增加 1 天，但您可以根据需要进行配置.您还可以将 DATEPART 和 Increment number 存储在另一个变量/参数中，以便于配置.

The sample increments the values by 1 day but you can configure this however you would like to. You can also store the DATEPART and Increment number in another variable/parameter for easier configuration.

我在 For 循环中放置了一个脚本任务来说明示例包的执行.脚本任务将变量 User::Loop 设置为 ReadOnlyVariables 并包含以下 C# 代码.

I have placed a Script Task within the For loop to illustrate the sample package execution. Script task has the variable User::Loop set to ReadOnlyVariables and contains the following C# code.

public void Main()
{
    MessageBox.Show(string.Format("Current loop variable value: {0}", Dts.Variables["Loop"].Value.ToString()));
    Dts.TaskResult = (int)ScriptResults.Success;
}

如果MinInteger设置为20120229，MaxInteger设置为20120302，包会显示如下执行期间的值.

If the MinInteger is set 20120229 and MaxInteger is set to 20120302, the package will display the following values during the execution.

希望有所帮助.

这篇关于如何遍历在 For 循环容器中存储为数字的日期值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！