Parallel execution of SSIS in SQL Server Agent

Question

Post reply

Parallel execution of SSIS in SQL Server Agent

Analyst

Ten Centuries

Points: 1063
More actions
April 24, 2008 at 5:00 am

#121830

I have say 10 packages all are loading the dimension tables, there is no dependency in each of these 10 packages.
While using the scheduler in SQL Server Agent, am able to create steps for each of the packages in a single job but am able to do only sequential loading of the tables.
Please let me know if there is some other solution for the above.
Thanks,
Janani

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Job steps run sequentially.

You either need to create 10 jobs, or you need to create one package that calls the other 10 packages in parallel.

Analyst Ten Centuries Points: 1063 More actions · Answer 2

Thanks for the suggestion Michael. I was wondering if it could be done in the scheduler level itself.

For the solution suggested below: Can you please give me more light how to create a single package for all the 10 packages?

Note: Each package has 2/3 tasks in the control flow task (execute sql task;data flow task...)

Waiting for your reply!

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Create a new package.

Add 10 Execute SSIS tasks to the control flow - don't connect the arrows together.

Point each of these at the 10 packages you want to run in parallel.

Now, if you have connection information saved in your packages and your encryption level is using your user key, you may have some security issues - using package configurations and not saving connection information will solve this.

Analyst Ten Centuries Points: 1063 More actions · Answer 4

Thanks a lot Michael. Will try it right away.

One quick question, 10 Execute Package tasks needs to be pulled into the control flow ,rite?

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

Yes, I just looked, that is the correct task type.

It just runs another SSIS package.

Analyst Ten Centuries Points: 1063 More actions · Answer 6

Thanks once again.. I too checked , it seems fine...

The other clarification is say there is the same execute sql task in all the 10 packages. the execute sql inserts and updates the same table in the 10 packages.

Now when I run parallely, only one of the packages executes correctly the execute sql task. the rest are null.

One more question regarding the Sql server agent, is it possible to trigger another job within a job?

Say once Job1 (5 packages) finishes can it execute the Job2 (another 2 packages)?

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 7

I am not sure exactly what you are saying regarding some packages getting a NULL, but if you have 10 packages all updating the same table, they may conflict with each other.

As far as a job starting another job, you could call msdb..sp_start_job from a job step to launch another job, but why not just create two steps in one job?

Analyst Ten Centuries Points: 1063 More actions · Answer 8

Yes its trying to update the same table.. It is a processcontrol table.

Currently I have all my packages in sequential execution in the scheduler. I would like to change the scenario ,as even in the tables without depency of other packages are running sequentially.

I was looking for an option like, in informatica we can have a parent workflow with n number of child workflows (which can run in parallel or sequential)and n number of sessions to run parallely under each workflow.

The execute package will help in parallel execution but its having a conflict while trying to insert and update into the same table.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 9

The insert and update conflicts are probably just bad SQL somewhere. Make sure your processes do not lock the same records.

zak_willis SSC Enthusiast Points: 105 More actions · Answer 10

If it was me, I would consider writing a small C# class to handle this rather than the very expensive using a package to run 10 other packages concurrently. Unless, the executing of an additional package is run out of process? This makes it more dynamic as you could not just limit yourself to 10 packages. Just execute in threads.