Package upgraded from ssis2005 to ssis2008r2 not executing from sql agent job

I upgraded a set of ssis packages from 2005 to 2008r2. the corresponding database has ben upgraded from sql2005 to sql2014. The packages run fine from BIDS. But when i execute them from new sql server agent job ( i have checked the use-32 bit runtime check box)., the package fails with error message as

Executed as user: ADMIN\SvcSQL13DVMDDEW03. Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:57:06 Error: 2017-07-27 11:57:08.96 Code: 0xC0209302 Source: CBSBankRec50244961Transform Connection manager "TempRBGLINT" Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider SQLNCLI10.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 Error: 2017-07-27 11:57:08.97 Code: 0x00000008 Source: Check Database Connection Description: The script returned a failure result. End Error Error: 2017-07-27 11:57:24.55 Code: 0xC0202009 Source: CBSBankRec50244961Transform Connection manager "RBGLINT" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [2]. ". End Error Error: 2017-07-27 11:57:24.57 Code: 0xC00291EC Source: Log Package 'Started' Execute SQL Task Description: Failed to acquire connection "RBGLINT". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2017-07-27 11:57:40.21 Code: 0xC0202009 Source: CBSBankRec50244961Transform Connection manager "RBGLINT" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [2]. ". End Error Error: 2017-07-27 11:57:40.22 Code: 0xC00291EC Source: Log Package 'Failure' Execute SQL Task Description: Failed to acquire connection "RBGLINT". 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: 11:57:06 Finished: 11:57:40 Elapsed: 34.102 seconds. The package execution failed. The step failed.

The provider used in the config files for the packages is SQLNCLI10.1 . in the ssis package there is a ole db connection manager - RBGLINT. Just for information - this was automatically upgraded from sql native client \oledb provider to sql native client \ ole db provider 10.0. in the connection manager. I do not see this causing the issue. But just for information.

Any inputs?

From the error message you provided the main error is:

The requested OLE DB provider SQLNCLI10.1 is not registered

From the link below you there is 2 methods for solving this issue:

The requested OLE DB provider SQLNCLI10.1 is not registered

1. you need to do is to open the connection manager/connection string and edit it to use Native Client 11.0 which is what comes as default with SSIS 2014

2. Another alternative is to install the Native Client 10.0 from SSIS 2008 installable:

   • https://blogs.iis.net/rickbarber/installing-sql-server-native-client-10-0
   • http://www.keepitsimpleandfast.com/2013/04/the-sqlncli10-provider-is-not.html