December 21, 2018 at 8:56 am
I created a package last week. Source was excel and dest. was SQL Server. I also changed the 'Run64BitRunTime' option to false. Moved the package on the server, scheduled a job and the job has been running successfully, Now I was asked to changed the source file which I did and now when I run the package from my local machine, it runs successfully. But when I move this new package on the server, execute the job, it doesn't run and throws an error 'The requested OLE DB provider Microsoft.ACE.OLEDB.16.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode.' I also enabled the execution option "run in 32 bit" by going to job properties, steps, edit, general, execution option but still no luck. whether this execution option is enabled or disabled, it throws an error.
Error when run in 32 bit is enabled.
Executed as user: LE\svc.sqlcluster. Microsoft (R) SQL Server Execute Package Utility Version 13.0.5153.0 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 9:51:04 AM Error: 2018-12-21 09:51:05.50 Code: 0xC0209303 Source: SocialServices Connection manager "Excel Connection Manager" Description: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 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: 2018-12-21 09:51:05.50 Code: 0xC001002B Source: SocialServices Connection manager "Excel Connection Manager" Description: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816 End Error Error: 2018-12-21 09:51:05.50 Code: 0xC020801C Source: Data Flow Task Excel Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2018-12-21 09:51:05.50 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: Excel Source failed validation and returned error code 0xC020801C. End Error Error: 2018-12-21 09:51:05.50 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2018-12-21 09:51:05.50 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:51:04 AM Finished: 9:51:05 AM Elapsed: 0.625 seconds. The package execution failed. The step failed.
Error when run in 32 bit is disabled.
Executed as user: LE\svc.sqlcluster. Microsoft (R) SQL Server Execute Package Utility Version 13.0.5153.0 for 32-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 9:51:25 AM Error: 2018-12-21 09:51:26.61 Code: 0xC0209302 Source: SocialServices Connection manager "Excel Connection Manager" Description: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-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: 2018-12-21 09:51:26.61 Code: 0xC001002B Source: SocialServices Connection manager "Excel Connection Manager" Description: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816 End Error Error: 2018-12-21 09:51:26.61 Code: 0xC020801C Source: Data Flow Task Excel Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" 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: 2018-12-21 09:51:26.61 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: Excel Source failed validation and returned error code 0xC020801C. End Error Error: 2018-12-21 09:51:26.61 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2018-12-21 09:51:26.61 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:51:25 AM Finished: 9:51:26 AM Elapsed: 0.657 seconds. The package execution failed. The step failed.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 21, 2018 at 9:05 am
was the Microsoft Access runtime installed on the server? https://www.microsoft.com/pt-pt/download/details.aspx?id=10910 and corresponding sp1 https://www.microsoft.com/en-us/download/details.aspx?id=26602. Not sure if SP2 gives same functionality - newer versions removed some functionality from the driver.
install the same bit version as that of your SQL Server install - do not mix 32 and 64bit versions
December 21, 2018 at 9:06 am
Now I was asked to changed the source file
From what, to what?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 21, 2018 at 9:09 am
The ACE provider wouldn't be on the server by default.
It is on your machine because you have Office installed.
Search for "Microsoft Access Database Engine Redistributable" and you should be able to find a download link from Microsoft. Looks like there are 2010 and 2016 (Office 365) flavors now.
Keep in mind the engine comes in 32-bit and 64-bit flavors, and you can only have one of them installed at a time (supposedly, without some funky fiddling). You'll want to decide which way you want to run your packages and install the appopriate bit version.
December 21, 2018 at 9:12 am
Seeing as you get the error in both 32 and 64 mode, this suggests you have neither the 32 or 64 bit drivers installed. I'm also guessing that, maybe, the file was previously a xls and now is an xlsx? If it was an xls before, then you would have been using the JET drivers, which would explain why it worked before as those are/were likely installed on your SQL Server.
Your title, and question text, mention "Microsoft.ACE.OLEDB.16.0", however, both the error state "OLE DB provider Microsoft.ACE.OLEDB.12.0". Which driver(s) are you actually trying to use? OLEDB.12.0 as the 2012 drivers, and (unsurprisingly) OLEDB.16.0 are the 2016 drivers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 21, 2018 at 9:15 am
Phil Parkin - Friday, December 21, 2018 9:06 AMNow I was asked to changed the source file
From what, to what?
Sorry, source is still the same, but the file name has changed. So I am still using the complete path I was using before but I now have a different excel file and that's it.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 21, 2018 at 9:18 am
Thom A - Friday, December 21, 2018 9:12 AMSeeing as you get the error in both 32 and 64 mode, this suggest you have neither the 32 or 64 bit drivers installed. I'm also guessing that, maybe, the file was previously a xls and now is an xlsx?Your title, and question text, mention "Microsoft.ACE.OLEDB.16.0", however, both the error state "OLE DB provider Microsoft.ACE.OLEDB.12.0". Which driver(s) are you actually trying to use? OLEDB.12.0 as the 2012 drivers, and (unsurprisingly) OLEDB.16.0 are the 2016 drivers.
Excel file I was using before was .xls and now I am using .xlsx.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 21, 2018 at 9:23 am
Syed_SQLDBA - Friday, December 21, 2018 9:18 AMThom A - Friday, December 21, 2018 9:12 AMSeeing as you get the error in both 32 and 64 mode, this suggest you have neither the 32 or 64 bit drivers installed. I'm also guessing that, maybe, the file was previously a xls and now is an xlsx?Your title, and question text, mention "Microsoft.ACE.OLEDB.16.0", however, both the error state "OLE DB provider Microsoft.ACE.OLEDB.12.0". Which driver(s) are you actually trying to use? OLEDB.12.0 as the 2012 drivers, and (unsurprisingly) OLEDB.16.0 are the 2016 drivers.
Excel file I was using before was .xls and now I am using .xlsx.
This is not a change in file name, it is a change in file type.
You will need to install the ACE drivers on the server to be able to load files of this type.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 21, 2018 at 9:25 am
Syed_SQLDBA - Friday, December 21, 2018 9:18 AMThom A - Friday, December 21, 2018 9:12 AMSeeing as you get the error in both 32 and 64 mode, this suggest you have neither the 32 or 64 bit drivers installed. I'm also guessing that, maybe, the file was previously a xls and now is an xlsx?Your title, and question text, mention "Microsoft.ACE.OLEDB.16.0", however, both the error state "OLE DB provider Microsoft.ACE.OLEDB.12.0". Which driver(s) are you actually trying to use? OLEDB.12.0 as the 2012 drivers, and (unsurprisingly) OLEDB.16.0 are the 2016 drivers.
Excel file I was using before was .xls and now I am using .xlsx.
2 crystal balls in one day! Whoop.
Anyway, back on topic, you'll need to download the ACE drivers. You can find the ACE 2016 drivers here, and the 2010 drivers (I was wrong, OLEDB 12 is 2010, not 2012) here. Download the version(s) you need and install on your SQL Server. Your package should then work after that. Note that you can't install the 32 and 64 bit versions of the same distribution (so you can't have OLEDB 12 32 and 64 bit versions installed), but you can install the 32 and 64 version of different distributions; so you could install OLEDB 12 32bit and OLEBB 16 64bit.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 21, 2018 at 10:02 am
just to clarify.
Access runtime 2016 installs both 2012 and 2016 drivers. However there is functionality on the old 2010 + sp1 that has been removed from teh 2016 install - as such and unless the code on the server is making use of specific ACCESS functionality that is only available on the 2016 driver I still advise, and have given the link to, using the 2010 runtime instead.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply