At a client site, everything was ticking along nicely using SQL Server OLEDB connections in SSIS packages. Then came the bad news – disaster recovery testing had thrown up a few issues (multi-subnet failover yadda, yadda) and we were going to have to switch over to using ODBC for our SQL Server database connection – cue groaning.
Why groaning you ask? Well, the SSIS packages use ExecuteSQLTasks that pass values around using package variables and result sets. In order to accommodate the change to the different connection, we will need to modify the mapping of the parameters and result sets. For example, OLEDB uses a 0-based labelling when mapping parameters, but ODBC uses a 1-based approach. This also applies when mapping result sets to package variables (see https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task for full details). Another fun thing is that the data types are different and in some cases (I’m looking at you datetimeoffset) aren’t supported. Not to mention that juggling varchar and nvarchar source/targets with ODBC is quite interesting. In terms of data flow tasks, this can be quite a pain and require a lot of troubleshooting or re-thinking of an approach.
But enough belly-aching for there is light at the end of the tunnel. OLEDB is being “undeprecated” according to this blog post (https://blogs.msdn.microsoft.com/sqlnativeclient/2017/10/06/announcing-the-new-release-of-ole-db-driver-for-sql-server/) and it is going to have multi-subnet failover capabilities. So hopefully that means back to OLEDB in the future.
Until de next de time – bork, bork, bork.