Quick question.In DTS when you had multiples tasks connecting to the same Database, it was reccomended that you create multiple connections for the same database and let each task use a different connection, thereby making a new connection for each one.
In SSIS if i define 1 OLE DB connection to a database, but i have say 6 tasks using that connection that run simultaneously, does eachone open a new connection to the database to make it more efficient? Or do i still need to define multiple connections to the same database and assign each task a different connection?
Thanx
SeanDL wrote:
Hi There Quick question.In DTS when you had multiples tasks connecting to the same Database, it was reccomended that you create multiple connections for the same database and let each task use a different connection, thereby making a new connection for each one.
In SSIS if i define 1 OLE DB connection to a database, but i have say 6 tasks using that connection that run simultaneously, does eachone open a new connection to the database to make it more efficient? Or do i still need to define multiple connections to the same database and assign each task a different connection?Thanx
Sean,
In that situation each of the 6 tasks will use the same connection as far as I know. I guess this is why the connection managers are so called - its because they manage a single connection thereby enabling many tasks to use it.
I'm no DBA but I suspect that this is more efficient than opening multiple connections.
-Jamie|||Hi Jamie
In DTS it was definately better to use multiple connections when extracting large amounts of data from each connection.But no mention has been made of it in SSIS, i guess i could do some tests myself to see if it helps performance?
Thanx|||I'd be interested in seeing any metrics you get.
I don't claim to know much about DTS but I suspect that there are different reasons that multiple connections are recommended that outweigh the benefit of only having 1 connection to the server.
I also suspect that even if one connection object were used each task that used it would still have its own connection (I stand to be corrected here). This is not the case with SSIS.
-Jamie|||Sean, the reason connection managers are called that is to distinguish them from connections as named in DTS. The reason it was recommended to use multiple connections in DTS is because workflow would be serialized if pointing to the same connection. Connection managers actually generate a new connection everytime you call AcquireConnection so the workflow isn't serialized. Each task or transform etc. that calls AcquireConnection on the connection manager receives a new physical connection.
So, for SSIS, just use one connection manager. Also, consider using Data Sources and Data Source views for your OLEDB connections. I blogged about it a bit recently. That simplifies matters even more and makes your packages that much more portable.
Thanks,|||Kirk,
Right then, I'm a wee bit confused. I'll tell you why.
I was recently working on a package where I had an Execute SQL Task followed by a data-flow which extracted from a SQL Server DB. Both tasks used the same connection manager.
In the Execute SQL Task there was a SET ROWCOUNT 20000 statement. When I ran the package, the data-flow only processed 20000 rows - obviously because it was affected by the SET ROWCOUNT command in the previous task.
Now, if both tasks had seperate connections that wouldn't happen. Or would it? I've done a quick scan of google to find out if I could find out the scope of SET ROWCOUNT but couldn't find anything (and its getting late :)
Any ideas?
-Jamie|||Hi Kirk
Thanx for the info, that is what i thought but i just could not find any documentation to verify it.
Although Jamies issue is a bit worrying because set rowcount's scope is definately within a connection/transaction?
Thanx|||Hi Kirk
Ok i am also still confused, my concurrent connections (5 of them) using the same conenction manager we all executing in parrellel, then i changed the connection manager to use the OLE DB for SQL Server NOT the native client that iw as using), now only 3 of the 5 tasks execute concurrently when 1 finishes the next one starts.Therefore it seems only 3 concurrent conenctions are allowed, i have checked BOL , but i cannot find where this setting is as i would like all tasks to run in parallel, if you know it would be greatly appreciated.
Thanx|||Check the MaxConcurrentExecutables property of the package by clicking on the package design surface and then looking at the properties. If it's set to -1, the heuristic used to determine max executables is n+2, where n is the number of processors.
Change it to a higher number and watch them all fly together. :)
K|||Thanx Kirk
Spot on , i did see that setting but i thought -1 was unlimited.Thanx A million works like a bomb !|||
Kirk,
Right then, I'm a wee bit confused. I'll tell you why.I was recently working on a package where I had an Execute SQL Task followed by a data-flow which extracted from a SQL Server DB. Both tasks used the same connection manager.
In the Execute SQL Task there was a SET ROWCOUNT 20000 statement. When I ran the package, the data-flow only processed 20000 rows - obviously because it was affected by the SET ROWCOUNT command in the previous task.Now, if both tasks had seperate connections that wouldn't happen. Or would it? I've done a quick scan of google to find out if I could find out the scope of SET ROWCOUNT but couldn't find anything (and its getting late :)
Any ideas?
-Jamie
OK, I've just built a simple mock-up to demo this and lo and behold it doesn't happen anymore. Setting rowcount in the Excute SQL Task didn't affect the dataflow.
I swear that when I did this before though it DID affect the data-flow...and I have witnesses to prove it. It occurred on April CTP whereas I am now using June CTP. perhaps that's why!!!
For now, ignore this. If it happens again I'll raise it.
Weird!!
-Jamie|||Did you have retain same connection set to true?
I don't have a good answer here. Without looking at the package, it's hard to know for sure.|||Yes, did so myself. Couldn't repro it either...
