SSIS parallel execution of same package which is depend on one parameter

Question

Please see the attached picture for better understanding of my question.

Some questions for clarity. 1) are you wanting to replace the function in Set 1 with the function in set 2? 2) Is Set 2 a more generalised form of Set 1? 3) Do you know how many parallel execution paths you will have in Set 2? — Marcus D, Feb 26 '16 at 07:46
1->Yes I want to replace the function with function I set 2 2->yes it's a generalised form of set1 3->there will be 4 times parallel execution in set2 — Avijit Das, Feb 26 '16 at 08:27
Exactly what type of Object is Set-1, and what type of Object is A? — Tab Alleman, Feb 26 '16 at 14:16

score 0 · Answer 1 · answered Feb 26 '16 at 10:15

We do something like this in our data framework. It gives more complexity to set it up initially, but once set up, it is all meta-data driven.

The concepts you have are:-

Create a set of meta-data driving tables that list all the tasks with parameters that you have. This should include degree of parallelism and dependencies
Create a 'Controller', for us it is a stored procedure but could be a SSIS task, that runs every minute and monitors currently executing jobs, when should be running and the completion state of jobs. It will start execution of an 'instance' of the 'SSIS Master' as indicated by the meta-data.
Create a 'SSIS Master' package that can be called a number of times by the 'Controller'.
Ideally all jobs that are called by the 'SSIS Master' are all Stored Procedures, again the name of the SP is just meta data passed to the job.

The advantage with this is once it is setup, to add a new parallel task is just one row in your metadata.

1 Answers1