SSIS variable lookup of existing subfolders by name

Question

I have added a File System Task to an SSIS package for the purpose of creating 7 new subfolders in 24 existing directory subfolders based on a purchase order number.

The first var titled MainFolderPath is to the main system where the existing and new subfolders reside. Additionally I have created a var called FolderName that creates a subfolder with the PO #, and then creates 7 new subfolders in the PO # subfolder.

The part I am struggling with is creating a var that will create the PO and 7 new subfolders in each of the 24 existing subfolders at the same time. Below is my example:

Var 1: var.MainFolderPath Type – String

\\10.xxx.xx.xxx\sales\subsystem

Var 3: var.FolderName Type – String

+ [@User::var_PurchaseOrder] + "1. Estimates" + "2. Approval" + "3. Legal"  + "Processing" + "5. Fulfilment" + "6. Shipping" + "7. Billing"

In the subsystem are 24 existing subfolders, e.g. engines, tires, electrical, etc. The missing var finds those existing subfolders by name for the var.PurchaseOrder to create the new PO # folder and 7 new subfolders.

I hope this structure makes sense, and would appreciate any assistance on how I can make this work.

Answer 1

I would store the names of the folders in a SQL Server table and then use an Execute SQL Task to select these into an object variable (set ResultSet to 'Full result set' and then map the result set to the object variable [set the Result Name to "0"]). (You're essentially putting a single-column result set into an object variable.)

You can then use a Foreach Loop to iterate over this collection using a Foreach ADO Enumerator, mapping each result to a string variable, which can then be included as part of your Create Directory path.

Then simply add the File System Task for Create Directory into the Loop.

I'm not sure this is what you need in its entirety, as you also mentioned 24 sub-directories, but this design pattern will be able to handle any level of complexity in that regard, e.g., you may end up with a Loop (for the 7 folders) within another Loop (for the 24 sub-directories).