THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

The Parallel Loop Task in SSIS

I really don’t like to use the blog as an advertising tool, but sometimes I feel the need to mention third party products because I thing they are interesting enough.

Today I discovered that CozyRoc implemented the Parallel Loop Task in SSIS. This is a feature I requested more than 3 years ago on Connect and today it is available. Well, not for free, but in a library which contain several interesting tools (including the TableDifference and FlowSync components originally developed by Alberto Ferrari).

Why you should use the Parallel Loop Task? Well, just imagine you have to process many files in a directory, loading them in SQL Server. You have to enumerate them and to process them. Without the Parallel Loop Task it is really hard to leverage the parallelism in SSIS. If only we had that component three years ago…

Published Friday, August 6, 2010 12:52 PM by Marco Russo (SQLBI)



Dave Ballantyne said:

Similar to the task i have created here...

August 6, 2010 6:18 AM

Marco Russo (SQLBI) said:

It is interesting - have you a measure of the overhead involved by executing an external package for every loop? It seems you pay validation cost for each iteration in this way.

August 6, 2010 7:07 AM

Dave Ballantyne said:

Overhead seems minimal, i created a million row input and it ran through subsecond.  I have to admit , its not as polished as i would have like but is 'proof of concept'.

August 6, 2010 7:33 AM

Marco Russo (SQLBI) said:

Nice. Thanks

August 6, 2010 7:52 AM

Isaac Carter said:

CozyRoc has not kept their product up to date, Parallel Loop tasks does not work with SQL Server 2012.  I asked them about it and they said that Microsoft will not fix their code for them.

Does anyone know of any new options?

April 22, 2013 11:57 AM

Marco Russo (SQLBI) said:

I'm sorry to know that.

Have you tried the Dave's solution in the link above?

April 22, 2013 6:02 PM

Trent York said:

Isaac's post is not entirely true.  

You can use SSIS 2012 with the Parallel Loop task - we have a few fully functioning SSIS projects leveraging this technology.

The caveat is you cannot leverage most of the beneficial new features of SSIS 2012, like parameters (project or package, it does not like the leading $ in the the parameter reference, even if you try to make the reference indirect by binding a local variable to the parameter), and project package references (SQL Server or File System package references work, haven't tried SSIS catalog yet but I expect it will work).

If you want to continue to use the older deployment and development model, you can reap the benefits of the development environment and performance improvements in SSIS 2012, but if you want to fully leverage SSIS 2012 then the Parallel Loop task - at least for now - is not going to work for you.

May 20, 2013 7:20 PM

Marco Russo (SQLBI) said:

Trent, thanks for the update!

May 20, 2013 7:25 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement