SSIS ForEach Item Enumerator to Create Folders for each Month by Quarter for Current Year

How to create Monthly Archive folders by Quarter for Current Year and Copy the Template to Each Month Folder using SSIS File System Task with ForEach Item Enumerator
The For Each Item Enumerator is of the Enumerator option available in Foreach Loop Container. It will use to enumerate through a list of static items declared at the For Each Container level.

Scenario :

I want to create Monthly Archive folders by Quarter for Current Year and Copy a report Template to Each Month Folder as shown below...

Now lets design the Package to fulfill the above scenario.

-------------------------------------------------------------------------------------------------------------------
Phase-1 : Create the required Variables for the Package
-------------------------------------------------------------------------------------------------------------------

Here, we create below variables..

Template : It is a Static variable where we declare the Report Template which we want to copy to the destination folders.

T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\Template\ReportTemplate.xlsx

OutPutFolder : It is a Static variable where we create the Monthly archive Folders and copy the Report Template.

T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Desti\

MonthName : It is a Dynamic variable used in ForEach loop to store the Month Name during the run time. (Eg : Jan)

QuarterName: It is a Dynamic variable used in ForEach loop to store the Quarter Name during the run time. (Eg : Q1)

Year : It is a Dynamic variable used to store the Current Year calculated using the below expression.(Eg : 2017)

(DT_WSTR, 4) YEAR(Getdate()) 

NewFoderName :It is a Dynamic variable used to create the New destination Folder path, which will calculate using the below expression.

@[User::OutPutFolder]+(DT_STR,4,1252) @[User::Year] +"\\"+ @[User::QuarterName] +"\\"+@[User::MonthName]

Eg: T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Desti\2017\Q1\Jan

-------------------------------------------------------------------------------------------------------------------
Phase-2 : Add the ForEach Loop to Control Flow and Set the Properties
-------------------------------------------------------------------------------------------------------------------

A) Add the ForEach Loop to Control Flow

B) Next select the Enumerator from ForEach Loop Editor > Collections Tab

Here we select Enumerator as "ForEach Item Enumerator"

C)Next Click on Column to add Columns one for Month another for Quarter

D)Next declare the Static values Month(Colum 0) another for Quarter(Colum 1)

D)Next do the variable mappings for ForEach Loop Editor > Variables Tab

Here we map the MonthName and QuarterName variables to store the Month Name and Quarter Name during run time.

Next Say OK.

-------------------------------------------------------------------------------------------------------------------
Phase-3 : Connect the File System Task to ForEach Loop and Set the Properties
-------------------------------------------------------------------------------------------------------------------

A) Add the File System Task inside the ForEach Loop

B) Next set the properties in the File System Task Editor

Operation = Create Directory ; it will create the New Folder for each Month Name during run time

Source Variable = User:NewFolderName ; The Name of the Folder which will be created in the OutFolder location.

Next Say OK.

------------------------------------------------------------------------------------------------------------------
Phase-4 : Connect a another File System Task to existing and set Properties
-------------------------------------------------------------------------------------------------------------------

A) Add a another File System Task and Connect to Existing. 

The new File System Task will be used to Copy the Report Template to the Month Folders which created by first File System Task.

B) Set the Properties to the new File System Task

Operation = Copy File; it will Copy the Template from Source to Destination.

Source Variable = User:Template
Destination Variable = User:NewFolderName

Next Say OK.

C) Finally run the Package

--------------------------------------------------------------------------------------------------------

Output :

Now the Folders for each Month name has been created under respective Quarter Folder of the Current Year Folder.

Next the ReportTemplate has been Copied to each Month Folder.

Note :

If Else Expression to Calculate the Quarter Names from Month Names

( @[User::MonthName]=="Jan"||@[User::MonthName]=="Feb"||@[User::MonthName]=="Mar" ? "Q1":
@[User::MonthName]=="Apr"||@[User::MonthName]=="May"||@[User::MonthName]=="Jun" ? "Q2":
@[User::MonthName]=="Jul"||@[User::MonthName]=="Aug"||@[User::MonthName]=="Sep" ? "Q3":"Q4")

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------