Looping through list of users in SSIS by using SSIS “Object Variables” and “Recordset Destination”

Looping through list of users in SSIS by using SSIS “Object Variables” and “Recordset Destination”
One of the question that came up for me was how to get a list of users & emails and email them the work list in Excel, the second part of the question was they wanted to keep a archive of the work list by user and date, a kind of a back-up list, so in this case I will be showing how to make the folders by user by date, and I will be using objects such as “Recordset Destination” and ADO.NET in a Loop object.
Criteria
1. ---
Over all View


Defining some variables in the package
One of the most important part of a SSIS package are the variables, because some variables will be defined in the Configuration File(s), so that you can use the package dynamically.


1.“uVar_ArchiveFolder” This is where you want to have the root of the Archive folder.
2.“uVar_ArchiveFolderCurrUser” a variable that will save the current user name and folder, e.g. C:\SSIS\Archive\Smith\2010-04\2010-04-15.
3.“uVar_CurrentUser” An object that will contain the list of Users (Record of users).
4.“uVar_CurrentUserEmail” The name of the current Email.
5.“uVar_CurrentUserName” The name of the current UserName.

Defining Connection Manager
We need 1 connection.
1. Excel connection manager


Adding Object (controls)
Now we can add the objects as seen in the “Over all View”
1. DFT----CreateUserlistToLoopThrough


In this section we are retrieving the list of users and saving them into the SSIS Object variable, which is basically playing a role of a recordset.
The order of the select statement is very important because it will be used in the ForLoop. You can filter the SELECT statement dynamically.

2. FLC----Loop through Users list in SSIS Object using ADO


Setting the LOOP section, this is the part that is related to the order of the fields in the select statement.
3. SCR----Make Folder name by User as a String


A script to make the FOLDER string.

4. FSYS----Create archive folderSetting a “File System task” to make the folders


Testing Scenarios
Before testing make sure that you have set the variables according to the snap shot of the variables and have the “Criteria” checked.

Scenario 1:
1- Run the package and check the folder “C:\SSIS\ArchiveFolder” and its sub folders


Scenario 2:
1- Set the Variable “uVar_ArchiveFolder” = “C:\MyFolder\BackUpFolder”
2- Run the package and check the folder “C:\MyFolder\BackUpFolder” and its sub folders