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

Question

I tried to run the package under the SQL server agent. Most error messages indicate what I need to do is change the connection string to SQLNCLI10 or find and install support for SQLNCLI.1.

I need to know the meaning of this error. It is as simple as installing a driver and registering it, or it is more than just drivers. Could there be any driver-related reasons for this problem? I changed the provider as indicated in the error, and now I get new errors that are given after the first error.

Thanks.

The complete mistake is

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). 

Another mistake -

 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 of the problem

SQL Server SQLNCLI Client SQLNCLI * in OLE DB connection string.

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

Due to this publication, SQLNCLI12 is not included in the CTP2 release of SQL Server 2014.

These providers can usually talk to previous versions of SQL Server, but the likelihood of a version is less likely (with the exception of SQLNCLI11, which is associated with 2014).

Root cause

You have an OLE DB connection string using a provider that does not exist on this computer. You may have created the package in 2005 format, but run it on the 2008 instance. Anyway, your connection strings are in the format

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

will need to be updated according to the provider on the computer.

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

This can usually be resolved by using the Configurations wisely.