Feedback requested for SSIS Master package design

Question

Overall, I am looking for feedback regarding two different design options of running a master package.

I have one package that Agent calls that runs a bunch of packages that process data (I think we are up to about 50 now).

The original design was to group packages into smaller chunks called directorates which call the actual packages. Sample below:

A few perceptions I see (and experienced) with this approach is that: 1. Every package has to open (even if it is unnecessary to run ie no file present) 2. #1 adds so much time for the process to complete 3. Runs in parallel for sure

So I developed a new approach which will only run the packages that have the necessary files and logs the attempt if not. It is so much cleaner and you don't need all the file connections for each package to run since you are iterating through them.

I am not sure it runs in parallel (I actually doubt it).

I am adding the dataflow that populates the ADO Object that is being iterated in foreach to demonstrate the files being processed.

Note: Usually in DEV environment there are not many files to be processed, however, when deploying to TEST and PROD there will be most files present to be processed.

Can I get some feedback on these two different approaches?

Anyone that provides productive feedback will recieve upvotes!!!

Answer 1

I will go with 2nd approach as its cleaner and easy to debug.

Here are the suggestions to improve 2nd approach :

• Create a Control table with all package Names, Enable/Disable flag, FileAvailable Flag
• Create a Poll package which will go through files and sets flag and package flag accordingly
• Loop through this Control table and run only those are enabled and having file.

Answer 2

I would go with modified first approach ie something like Inside package, use Script task to check if files are present in destination or not.

For instance :

1. Create a Script task and a variable.

---

2. Inside script task, write a code similar to the image below(Logic is, if file is found then flag it as true, else flag is false) :

---

3. Now constraint the execution of DFT by using this flag as shown below :

Only con is, you'll have to make changes in 50 packages, but this is a one time activity. Your parallel execution will remain intact.