Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concurrent processing in SSIS – Part 1


Concurrent processing in SSIS – Part 1

Author
Message
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
Comments posted to this topic are about the item Concurrent processing in SSIS – Part 1
ziangij
ziangij
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2905 Visits: 374
thanks Elliott Smile

i was looking for various concurrency options in SSIS package, your example has helped me... looking forward for Part 2.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21017 Visits: 18258
Nice article Elliott. It was a pleasure to read.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
Thanks guys, I hope it helps..

CEWII
pcannon
pcannon
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Nice article Elliott.
richard.maw
richard.maw
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 85
Interesting stuff but I tried adding "waitfor delay '00:00:01';" to each of the sample work SQL tasks in the OnePackageMethod (before the update statement). That way you can see what is happening because the work takes longer. On my workstation I observed that none of the tasks executed concurrently. They each execute in turn. Maybe on a server it would behave differently?
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
richard.maw (6/17/2010)
Interesting stuff but I tried adding "waitfor delay '00:00:01';" to each of the sample work SQL tasks in the OnePackageMethod (before the update statement). That way you can see what is happening because the work takes longer. On my workstation I observed that none of the tasks executed concurrently. They each execute in turn. Maybe on a server it would behave differently?
I've seen wildly varying performance on different machines, and while SSIS will try to run multiple items if the package is built to do that it can't always do it, on a single processor machine (no hyperthreading either) I doubt it will do it. On a server and even a laptop with multiple cores and a couple GB of memory you will see it thread..

Thanks for reading..

CEWII
richard.maw
richard.maw
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 85
I have dual core and 4GB. When I get the chance I'll try using a remote SQL Server that should help.
Mike Dougherty
Mike Dougherty
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 952
This works very well for centralizing a task comprised of steps on remote servers. A package to manage the remote calls has very little overhead on the machine running the package. In our usage there's a server at corporate that calls many colo-hosted SQL instances.
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
richard.maw (6/17/2010)
I have dual core and 4GB. When I get the chance I'll try using a remote SQL Server that should help.
Interesting, I have seen significantly less hardware run 3 threads at a time.

Mike Dougherty (6/17/2010)
This works very well for centralizing a task comprised of steps on remote servers. A package to manage the remote calls has very little overhead on the machine running the package. In our usage there's a server at corporate that calls many colo-hosted SQL instances.
Good to know, thanks..

CEWII
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search