Package deployment error - change connection string to SQLNCLI10 or find and install support for SQLNCLI.1?

Question

I tried to run a package under SQL server agent. Most of my error messages indicate that I have to - change connection string to SQLNCLI10 or find and install support for SQLNCLI.1.

I need to know the meaning of this error. Is it as simple as installing a driver and registering it or is it more than just drivers. Could there be non-driver related reasons for this problem ? I changed the provider as mentioned in the error and now i get new errors, which are given after 1st error.

Thanks.

Full error -

Code: 0xC0209302     Source: MyPackage Connection manager "MyOleDBConnection"     Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider SQLNCLI.1 is not registered. 
Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  
End Error  

Code: 0xC020F42A     Source: MyPackage Connection manager "MyOleDBConnection"     Description: Consider changing the PROVIDER in the connection string to SQLNCLI10 or 
visit http://www.microsoft.com/downloads to find and install support for SQLNCLI.1.  
End Error  

Code: 0xC020801C     Source: MyPackage Log provider "SSIS log provider for SQL Server"     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  
The AcquireConnection method call to the connection manager "MyOleDBConnection" failed with error code 0xC0209302.  
There may be error messages posted before this with more information on why the AcquireConnection method call failed.  
COM error object information is available.  Source: "MyPackage"  error code: 0xC0209302  
Description: "Consider changing the PROVIDER in the connection string to SQLNCLI10 or 
visit http://www.microsoft.com/downloads to find and install support for SQLNCLI.1.  ".  
End Error  

Code: 0xC0209302     Source: MyPackage Connection manager "MyOleDBConnection"     Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  
The requested OLE DB provider SQLNCLI.1 is not registered. Error code: 0x00000000.  
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  
End Error  

Code: 0xC020F42A     Source: MyPackage Connection manager "MyOleDBConnection"     Description: Consider changing the PROVIDER in the connection string to SQLNCLI10 or 
visit http://www.microsoft.com/downloads to find and install support for SQLNCLI.1.  
End Error  

Code: 0xC020801C     Source: DFT Insert Execution Log OLE_DST Insert SSIS Execution Log [25]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  
The AcquireConnection method call to the connection manager "MyOleDBConnection" failed with error code 0xC0209302.  
There may be error messages posted before this with more information on why the AcquireConnection method call failed.  
End Error  

Code: 0xC0047017     Source: DFT Insert Execution Log SSIS.Pipeline     Description: component "OLE_DST Insert SSIS Execution Log" (25) failed validation and returned error code 0xC020801C.  
End Error  

Code: 0xC004700C     Source: DFT Insert Execution Log SSIS.Pipeline     Description: One or more component failed validation.  
End Error  

Code: 0xC0024107     Source: DFT Insert Execution Log      Description: There were errors during task validation.  
End Error  

DTExec: The package execution returned DTSER_FAILURE (1). 

One more error -

Code: 0xC0202009     Source: MyPackage Connection manager "MyOLEDBConnection"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'MyUser'.".  
End Error  

Code: 0xC020801C     Source: MyPackage Log provider "SSIS log provider for SQL Server"     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection 
method call to the connection manager "MyOLEDBConnection" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method 
call failed.  COM error object information is available.  Source: "MyPackage"  error code: 0xC0202009  Description: "SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'MyUser'.".  ".  
End Error  

Code: 0xC0202009     Source: MyPackage Connection manager "MyOLEDBConnection"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'MyUser'.".  
End Error  

Code: 0xC020801C     Source: DFT Insert Execution Log OLE_DST Insert SSIS Execution Log [25]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  
The AcquireConnection method call to the connection manager "MyOLEDBConnection" failed with error code 0xC0202009.  There may be error messages posted before this with more information 
on why the AcquireConnection method call failed.  
End Error  

Code: 0xC0047017     Source: DFT Insert Execution Log SSIS.Pipeline     Description: component "OLE_DST Insert SSIS Execution Log" (25) failed validation and returned error code 0xC020801C.  
End Error  

Code: 0xC004700C     Source: DFT Insert Execution Log SSIS.Pipeline     Description: One or more component failed validation.  
End Error  

Code: 0xC0024107     Source: DFT Insert Execution Log      Description: There were errors during task validation.  
End Error  

Answer 1

Understanding the problem

The SQL Server Native Client provider is SQLNCLI* in an OLE DB Connection String.

• 2005 uses SQLNCLI.1
• 2008 uses SQLNCLI10
• 2012 uses SQLNCLI11

As of this posting, there is not a SQLNCLI12 included with the CTP2 release of SQL Server 2014.

These providers can generally talk to previous versions of SQL Server but version forwardness is less probable (with the exception of SQLNCLI11 communicating with 2014).

Root cause

You have an OLE DB Connection string using a provider that does not exist on that machine. Perhaps you built the package in a 2005 format but are running it on a 2008 instance. At any rate, your connection strings in the format of

Provider=SQLNCLI.1;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

would need to be updated to match the provider on the machine

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

This can generally be addressed through judicious use of Configurations