Has anyone experienced a difference between SQL driver SQLNCLI11.1 vs 10.1?
I inherited a complex SSIS package where I had to make minor changes (package loads data from 18 files to a SQL database).
The package works well with 10.1. But gives an error with Unicode to non-Unicode conversion with 11.1. I didn't want to change the mappings in the whole SSIS package for my change. So I'm planning to use 10.1 provider explicitly in the connection string.
Do any of you see any issue with this approach?
Not sure if you are using OLE DB or ODBC but the following changes related to character conversions may be of interest to you:
ODBC Driver Behavior Change When Handling Character Conversions
In the short term no real issues abut eventually you'll need to address things. Or stop doing any upgrades. Forever 🙂
But that is often how people get burned - put off making those changes and then whatever they were using is no longer supported. Then you're in scramble mode when it stops working in production.
Sue
Thanks for your response, Sue. I am using OLEDB Native Client.
Usually, I use OLEDB native 11 by default for any development work. That's how I found this difference. Dev connection string (in SSIS config table) had explicit Provider=v10.1. Rest of the environments didn't have any provider mentioned. So, I assume SSIS chose to use the one from SSDT designer (which I use as v11.1 by default).
Apparently, Microsoft has changed how character conversions are done with new version of Native Client OLEDB.