The requested OLE DB provider SQLNCLI10.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode

Question

I just installed sql 2012 to our development environment. We are wondering without upgrading/modifying if the existing packages we created in sql 2008 sp2 will work on sql 2012. I am getting this error when I try to run the package from sql agent. I prefer not setting the packages to 32 bit more since some of them consume over 16GB of memory.

What is the reason of this error and is there a workaround around it (ex can I search and replace SQLNCLI10.1 to SQLNCLI11.1 in the packages and will this error go away?

Thank you

Date 3/9/2012 9:30:10 AM
Log Job History (Diablo.DataLoad.LoadFlagFiles)




Message
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.2100.60 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.


Started:  9:30:10 AM
Error: 2012-03-09 09:30:45.99
   Code: 0xC0209303
   Source: LFG Connection manager "XXXX"
   Description: The requested OLE DB provider SQLNCLI10.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".
End Error
Error: 2012-03-09 09:30:45.99
   Code: 0xC020F42A
   Source: LoadFlagFiles Connection manager "_DiabloStg"
   Description: Consider changing the PROVIDER in the connection string to SQLNCLI11 or visit http://www.microsoft.com/downloads to find and install support for SQLNCLI10.1.
End Error
Error: 2012-03-09 09:30:45.99
   Code: 0xC00291EC
   Source: Create Temp Table Execute SQL Task
   Description: Failed to acquire connection "_DiabloStg". Connection may not be configured correctly or you may not have the right permissions on this connection.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  9:30:10 AM
Finished: 9:30:46 AM
Elapsed:  35.147 seconds

---

Gokhan Varol

Answer 1

Hello Gokhan,

I think as the message implies, it does not find the driver it needs, why, I guess after the installation the PATH environment variable got changed so the path to the 32 Bit driver supersedes the one to 64 now.

Naturally, to fix, just flip the paths in the PATH around.

---

Arthur My Blog

Answer 2

This is my current path, what am I supposed to do?

Path=C:\Program Files\Alchemy Solutions\NeoBatch Server\;C:\Program Files\Alchemy Solutions\NeoSort\;C:\Program Files (x86)\EMC\PowerPath\;C:\Program Files (x86)\EMC\RSA\CST\lib\;C:\Program Files (x86)\EMC\RSA\Rkm_Client\lib\;C:\Program Files\HP\NCU;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\
100\Tools\Binn\;C:\Program Files\Microsoft Network Monitor 3\;C:\Windows\idmu\common;C:\Program Files\TortoiseSVN\bin;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn
\
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC

---

Gokhan Varol

Answer 3

Yeah, I think it is the case. You see, the C:\Program Files\Microsoft SQL Server\100\DTS\Binn\; appears in front of C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn? Flip every one such instance around.

---

Arthur My Blog

Answer 4

but we want the 64 bit to be preferred not 32 bit, that looks right to me isn't it?

---

Gokhan Varol

Answer 5

You are right, it must be the driver then.

---

Arthur My Blog

Answer 6

DId you open the package in the new version of BIDS and convert it to SQL 2012?  That will update the provider strings to the right version.

Answer 7

You are right, it must be the driver then.

---

Arthur My Blog

how was this problem solved?

Answer 8

Not sure. This switching of paths to get the (x86) entries in front did not work for me.

Ken

Answer 9

In my case, I had package with hardcoded provider in the connectionstring.

It was used in expression (connectionstring as property) and the value was SQLNCLI.10.

I changed the expressions to use "ServerName" and "Instance Name" instead connectionstring property and let SSIS to decided which provider it needed to use.

Answer 10

You can search and replace SQLNCLI10.1 to SQLNCLI11.1 in the packages, this error go away.

Mostly, it must be the driver issue.