SSIS Parallel loop

1
votes

Am trying to design a SSIS package where the first step gets data from a table and for each record it executes a VB script using execute Process task in parallel based on the output from Step 1.

I understand SSIS supports for loop and parallel processing for repetative tasks, but i cannot use for loop because itis not parallel and i cannot design parallel tasks so it will depend on input data. The records from step 1 could be 0,1,10(which have to be executed in parallel).

We dont have the ability to use Script component.

Any suggestions are much appreciated. thanks

1
ssis
I'm confused by your question. You are "executing VB script using executetask" What does that mean? Is that an Execute Process Task which calls a .vbs file? If so, how is that any different than a native Script component? - billinkc
Sorry if my Q was confusing. Yes am calling a VB Script using the Wscript exe. For some reason in our environment i cannot use native script component. - S..
What's with 'parallel'. Do you need to spawn multiple VBS processes in parallel from multiple records? Why can't you execute a single one sequentially? VBS is not renowned for it's performance so that can't be the reason. Why don't you just build a 'spawner' in VBS or powershell or something? Why do you need to use SSIS? - Nick.McDermaid
Yes i want to execute parallel process depending on the number of records fromt table. I understand i can make multiple calls to VB script in parallel using the command script or powershell in parallel, But trying to explore options using SSIS - S..

1 Answers

0
votes

SSIS is pretty restricted if it comes to parallel execution. If you can't use script components / script tasks, it's even worse.

However, you can still create a certain number of execute process tasks and steer via parameter / variable, how many of them are executed and which values are passed to them. But as you might guess, this leaves the bitter taste of the question "What if I need several more tasks?".

Maybe you might want to consider to purchase a third party component - there are several available on the net.