January 24, 2012 at 4:46 am
Hi
When creating a Datasource within SSIS there are so many to choose from. Depending on your source or destination, choosing some Connection Managers is easy ie Excel, Oracle etc. However choosing the best connection manager for moving data from SQL Server to SQL Server seems to be harder to agree on.
From my own research the following are the top two choices, but I've no idea which is the best nor the pros or cons over one or another, or indeed if my research results are correct.
ADO.Net
SQL Server Native Client 10.0
Therefore my question is what is the best Connection Manager to use when moving data from SQL Server 2008 to SQL Server 2008. Also if you know, could you explain why is it better than other ones.
Many thanks
Darren
January 24, 2012 at 5:22 am
I would go with the OLE DB connection manager, using the native client as provider.
Reason: it is a bit faster if you use the fast load option in the destination.
I've tested it for a dataset, and the OLE DB was a few seconds faster then the ADO.NET one.
There are a few cases where ADO.NET configures a bit better, but since you mentioned SQL Server to SQL Server, I would stick with native client.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 24, 2012 at 11:45 am
+1 for OLEDB on both sides for SQL to SQL transfers. It's simple to setup, easy to use, little difficulty in transferring it to others, and uses the native dll which gives you more options.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 25, 2012 at 5:02 am
Hi,
Koen Verbeeck and Evil Kraig F, thank you both for taking the time to reply. You have put my mind at ease. OLE DB it shall be.
Much appreciated and many thanks
Darren
January 25, 2012 at 5:05 am
darrenkelly (1/25/2012)
Hi,Koen Verbeeck and Evil Kraig F, thank you both for taking the time to reply. You have put my mind at ease. OLE DB it shall be.
Much appreciated and many thanks
Darren
No problem, glad to help.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 25, 2012 at 6:33 am
In terms of the source or destination of a data flow task, I'd definitely choose the OLE DB connection. However, if executing a stored procedure (for example) in an execute SQL task, I tend to find ADO.Net is a bit nicer to work with in terms of handling parameters.
I also seem to remember that there's a small gain in terms of either the validation time or execution time when using ADO.Net for Execute SQL tasks, but I can't dig up the article now.
January 26, 2012 at 11:17 am
HowardW (1/25/2012)
In terms of the source or destination of a data flow task, I'd definitely choose the OLE DB connection. However, if executing a stored procedure (for example) in an execute SQL task, I tend to find ADO.Net is a bit nicer to work with in terms of handling parameters.I also seem to remember that there's a small gain in terms of either the validation time or execution time when using ADO.Net for Execute SQL tasks, but I can't dig up the article now.
It's validation time, it doesn't wait (iirc) for the target server to validate the syntax and code for the parameters, it just ships it down the ODBC pipe. That said, I'm not an ADO.NET guy. 😉
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply