How can I iterate files for multiple years in SSIS using a For Loop container and generate CSV files in YYYYMM format?

Question

How to Iterate Files for multiple years using For Loop container in ssis?

I want to generate the csv files in YYYYMM format for Multiple years using forloop container in ssis, so i have used variables as Startyear(INT32) and EndYear(INT32)=202012. Inside the container calling another package which generates the files. Iam able to generate for one year but not for multiple years. For example I want to generate from 202001 to 202101 files, but it is generating wrong format as 202013 after completion of 202012(dec) file. Could you please help me with this. Condition used in for loop is: Startyear=202001 Startyear<=Endyear Startyear=Startyear+1

Answer 1

And so 202012 + 1 = 202013 which is valid for the variable's domain (Integer) but you're then using it as a date and we don't have a 13th month.

How I'd do it

Create 4 Variables in SSIS

CurrentDate, StartYear, EndYear - these are all Data Types of DateTime. I initialized them to 1/1/2020, 1/1/2020 and 1/1/2023 respectively.

The fourth variable, CurrentDate_YYYYMM is of type String because you want a 4 digit year and 2 digit month for whatever you're doing in the subpackage. In the Expression, click the ellipses ... and use the following

(DT_WSTR, 4)YEAR(@[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2) MONTH(@[User::CurrentDate]),2)

That is, Cast to string of length 4 the year value of the variable current date. Concatenate to that, the last two characters of the following: a string of two zeros concatenated with the a 2 digit string of the month value of current date. In 9/12 of the values, we'll end up with a two digit string so the rightmost 2 is also the original string. For October-December, we'll end up with 3 digits 010, 011, 012 but as we're taking the last 2 characters, no harm, no foul.

It should show a Value of 202001. If you need this to be an integer value, then change your data type to Int32 and update the expression to the following

(DT_I4)((DT_WSTR, 4)YEAR(@[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2) MONTH(@[User::CurrentDate]),2))

For Loop Container

Instead of writing the logic for looping months and all that, since we have started with the correct data types, we can use the existing DATEADD method to iterate through our dates.

• InitExpression: @[User::StartYear] = "2020-01-01"
• EvalExpression: @[User::CurrentDate] < @[User::EndYear]
• AssignExpression: @[User::CurrentDate] = DATEADD("month", 1, @[User::CurrentDate])

Now, the terminal/eval might be off by one as I don't know what you need specifically but this will produce values for CurrentYear_YYYYMM from 202001 to 202212

SCR Echo Back

This is just my tried and true method for printing values to the Output/Progress window. I selected CurrentDate and CurrentYear_YYYYMM as my readonly variables.

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

namespace ST_e8ec1fddbe33468eb86c3d4a6fe37e71
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            bool fireAgain = false;

            foreach (Variable 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
        };
    }
}