How do I loop through date values stored as number

Question

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.

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.

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

Answer 1

Store your loop driver as a date so that it can do date arithmetic then use a variable that is driven by an expression based on that date to put it in the format you need for the internal usage.

Answer 2

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.

Declare the following variables:

• 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 - 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 - 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 - 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 - 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 - 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))

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 - This variable of data type DateTime will be used to loop through the date values in the For loop container.

Configure the For loop container as shown below:

• InitExpression: @[User::Loop]=@[User::MinDate]
• EvalExpression: @[User::Loop]<=@[User::MaxDate]
• AssignExpression: @[User::Loop]=DATEADD("dd", 1, @[User::Loop])

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.

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;
}

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

Hope that helps.