ODBC Connections vs MS OLE DB Connections 3

[mojoT]

I'm creating a DTS package to import data from one SQL Server to another.

My approach was to add two Microsoft OLEDB Provider for SQL Server connections, but looking at my predecessor's work, he's chosen to make the source connection an ODBC one.

Is this necessary, seeing that both source and destination are SQL Servers, or is there a benefit of using an ODBC connection that I don't know about?

There are no standards where I'm working that I have to adhere to, so would appreciate any input on best practices, or things I should take into account for determining what kind of connection I use.

 

[yksvaan]

I would use OLEDB by all means. Can't think of a benefit of ODBC.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[pkailas]

The "only" advantage of an ODBC connection over an OLEDB connection is that if you move the database to a different server and the application runs on a different machine from the sql server, it's just a simple matter of editing the DSN setting rather than recoding the application.

Of course, you could have some kind of an .ini file for the app that defines the connection.

 

[dbomrrsm]

http://www.sitepoint.com/forums/archive/index.php/t-15056.html

[blue]DBomrrsm[/blue]

 

[yksvaan]

pkailas, good point if the server name is the only thing that's different, but if database names are different I think one also has to fix the source and destination tables of Transform Data Tasks (actually DataPump Task) anyway. Of course, one can use global variables and so on..

I think one doesn't have to worry about performance, I think it depends on lots of other things than just which connection type to choose. Of course, performance testing in real environment should be done as always.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[mojoT]

Cool, thanks guys.