SSIS : The requested OLE DB provider SQLNCL10 is not registered. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
when we deployed the ssis package which is developed in sql 2008 r2 the connection string of Sql server OLEDB provider looks like
Data Source=Sever\xxxx;Initial Catalog=DbOdsPMT;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Packet Size=32000;Application Name=SSIS-Package-{866F41EB-2082-4344-B650-4BDDB0CFEC95}Sever\xxxx;
But when we call the package in SQL agent the connection string changed to
Data Source=Sever\xxxx;Initial Catalog=DbOdsPMT;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;Packet Size=32000;Application Name=SSIS-Package-{866F41EB-2082-4344-B650-4BDDB0CFEC95}Sever\xxxx;
Due to this the sql Job is Failing
Source: DbOdsPMT-DRA Connection manager "DbOdsPMT" Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider SQLNCL10 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: 2014-10-10 12:47:45.33 Code: 0xC0209302 Source: DbOdsPMT-DRA Connection manager "DbOdsPMT" Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider SQLNCL10 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: 2014-10-10 12:47:45.33 Code: 0xC020801C Source: DFT - Loading Data OLE_DST -Average Latency Table [66] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DbOdsPMT" 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 Error: 2014-10-10 12:47:45.33 Code: 0xC0047017 Source: DFT - Loading Data SSIS.Pipeline Description: component "OLE_DST -Average Latency Table" (66) failed validation and returned error code 0xC020801C. End Error Error: 2014-10-10 12:47:45.33 Code: 0xC004700C Source: DFT - Loading Data SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-10-10 12:47:45.33 Code: 0xC0024107 Source: DFT - Loading Data Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:47:44 Finished: 12:47:45 Elapsed: 1.077 seconds. The package execution failed. The step failed.
Solution :
Modify the Provider to Provider=SQLNCLI10.1 in sql job datasources then Job will pass .
Data Source=Sever\xxxx;Initial Catalog=DbOdsPMT;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Packet Size=32000;Application Name=SSIS-Package-{866F41EB-2082-4344-B650-4BDDB0CFEC95}Sever\xxxx;
But when we call the package in SQL agent the connection string changed to
Data Source=Sever\xxxx;Initial Catalog=DbOdsPMT;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;Packet Size=32000;Application Name=SSIS-Package-{866F41EB-2082-4344-B650-4BDDB0CFEC95}Sever\xxxx;
Due to this the sql Job is Failing
Source: DbOdsPMT-DRA Connection manager "DbOdsPMT" Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider SQLNCL10 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: 2014-10-10 12:47:45.33 Code: 0xC0209302 Source: DbOdsPMT-DRA Connection manager "DbOdsPMT" Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider SQLNCL10 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: 2014-10-10 12:47:45.33 Code: 0xC020801C Source: DFT - Loading Data OLE_DST -Average Latency Table [66] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DbOdsPMT" 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 Error: 2014-10-10 12:47:45.33 Code: 0xC0047017 Source: DFT - Loading Data SSIS.Pipeline Description: component "OLE_DST -Average Latency Table" (66) failed validation and returned error code 0xC020801C. End Error Error: 2014-10-10 12:47:45.33 Code: 0xC004700C Source: DFT - Loading Data SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-10-10 12:47:45.33 Code: 0xC0024107 Source: DFT - Loading Data Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:47:44 Finished: 12:47:45 Elapsed: 1.077 seconds. The package execution failed. The step failed.
Solution :
Modify the Provider to Provider=SQLNCLI10.1 in sql job datasources then Job will pass .
Comments
Post a Comment