When connecting to a database with SQL Server Integration Services, you will most likely use ADO.NET, OLE DB, or ODBC. Until I find reason not to, I’ll be standardizing on ADO.NET connection managers as much as possible. Even if the other database, such as mySQL, has only an ODBC connection, I’ll use ADO.NET instead and wrap that around mySQL’s ODBC connector. My reason for this is simple: I use a lot of script tasks and script components in my SSIS packages. It is far easier to work with connection managers in script tasks/components when those connection managers are ADO.NET. Consider the following:
- OLE DB and ODBC, from what I can tell, do not enlist the current transaction. What this means is that if you, for example, have an Execute SQL Task that starts a database transaction (assuming the database connected to supports it), the script task/component will not handle it’s steps in that transaction. The most common occurrence is your package crashes.
- OLE DB and ODBC when used in the script task/component do not support the “Retain Same Connection” setting.
- The code to acquire an OLE DB connection is not as elegant as the ADO.NET connection. This is the number one reason why I am standardizing on ADO.NET.
I’ll have more to say on the third part later on – which is the most important to me. Now that Microsoft has finally fixed the Script Component such that you can debug into it starting with SQL Server 2012, the Script Component is now very useful.