How do I configure a foreach loop container in SSIS to take defined start and end dates and run for each date in between?

Question

I'd like to define start_date and end_date parameters in my SSIS package, and have a foreach container that runs for each date in between these 2 (inclusive), which executes a SQL query taking in the current date value (ie starting at start_date) and using it as a parameter for the query.

I'm quite new to SSIS programming and I cannot find information on how to do this.

Answer 1

You can simply add a for loop container and use these variables as mentioned in the image below:

Where @[User:Loop], @[User:MinDate], @[User::MaxDate] are of type System.DateTime

image reference

• How do I loop through date values stored as numbers within For Loop container?

Passing parameters to Execute SQL Task

You can refer to the following posts for more details:

• Passing Variables to and from an SSIS task
• How to pass variable as a parameter in Execute SQL Task SSIS?

Answer 2

I used an Execute SQL Task to store the dates (results) as a Result Set in a user defined variable. Then, inside the foreach loop container, I used the foreach ADO Enumerator on the user defined variable which has the set of dates. Using the variable mapping in the foreach loop container, you can map the start_date and end_dates from the user defined variable and pass it to other variables.

For example: I have a SELECT statement which selects 2 rows with columns start_date and end_date. This will be stored as a result set in a variable called "main_dates". The foreach ADO Enumerator will enumerate on this "main_dates" variable (for each row in main_dates run the for loop). Then in the Variable Mapping section, you can create 2 new variables called u_start_date and u_end_date and map the columns 0 and 1 to these variables.

Inside the foreach loop whenever you execute a stored procedure, you can pass the u_start_date and u_end_date variables as parameters.

Answer 3

A For Loop would be the better option to do this. Assuming that the start and end dates as supplied as parameters to the package as indicated in your question, be aware that parameters cannot be updated in an SSIS package however variables can be. This, as well as an example of the process outlined in your question, is further detailed below.

• Create an SSIS datetime variable. As mentioned earlier, this will be used to store in initial value of the start date parameter.
• Next add a For Loop on the Control Flow. In the screenshot below, the variable @[User::vStartDate] is set to the same value as the package parameter @[$Package::pStartDate] in the InitExpression on the For Loop. Iterations of the loop continue while the start date variable is less than/equal to the end date parameter, which is specified in the EvalExpression field.
• After the Execute SQL Task (or however the SQL query is executed) add a Script Task. This will increment the value of the start date variable, so make sure this is the last task in the loop. An example C# script is below, which simply sets the value of the start date SSIS variable to a C# variable, increments the C# variable by one day, then writes that value back to the SSIS variable. Make sure to add the SSIS start date variable in the ReadWriteVariables field on the Script Task. This will go in the Main method of the script as follows. Although there’s just an increment of the date and update of the variable done in the Script Task, having this in place will allow for easier sustainability in the long term in case more logic needs to be added to this as C# provides much more functionality.

Script Task:

public void Main()
{
//get value in current iteration of loop
DateTime currentIterationValue = Convert.ToDateTime(Dts.Variables["User::vStartDate"].Value);

//increment by one day
currentIterationValue = currentIterationValue.AddDays(1);

//update SSIS variable
Dts.Variables["User::vStartDate"].Value = currentIterationValue;

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