This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 10%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hello.
We have multiple SSIS packages that import data from Excel into SQL and export results back to Excel. All those SSIS packages were designed in VS 2012, connecting to SQL 2012 and Excel 2013, and we can run it successfully as a part of SQL job or as stand-alone package in Integration services. We have recently migrated some of those packages to a new SQL 2022 server, upgrading those to the latest version of VS 2022, for excel we are upgraded to Excel 2016. Upgraded package doesn't give us any errors, can connect to all sources from Visual Studio within the packages. We are able to run packages that import data from excel into sql 2022 - so all components are in place and functioning, but when we try to run SSIS that exports data into Excel - get no errors, package just fails. Tried to run it from Integration - same, no errors; tried logging errors - get message that package starts and then it bombs off without any reason. If I run the test package on the old server, pointing to a new server (exporting data from SQL 2022)- it runs fine under Integration or as a SQL job (runs under the same account, exports to the same location on a new server - so it can't be security/access issue). I had tried to export manually and record it as a new SSIS package - export run manually once, but when I try to run newly created package - get the same locking up, then bombing off with no errors on that package as well. What could be missing from SQL 2022 server or specific to VS2022 or Excel 2016? I tried every suggestion that I found on the web - nothing works. We had re-installed Integration and Excel multiple times already.
One difference that we noticed - on old server we can run package with browsing to the package in File Explorer, right click and run it with Integration Services Package Execution Utility, on new server that option doesn't work (utility is installed). Thanks!
Hi @Alla,
Have you check the destination to make sure it could connect to excel file successfully?
Yes, of course. I can connect to destination from the package - no errors. As I mentioned - I run the same package from older server, exporting to same path, running under the same service account - all works fine. It's something with SQL 2022/VS2022/Excel 2016 that isn't working correctly.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(41.6, 44%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETF%3C/text%3E%3C/svg%3E)
I have had a similar problem with a SQL Server 2022 install using a DTSX file. When creating the DTSX file to export to Excel the Wizard assumes that I am creating a table in SQL Server even though I have told it that I am exporting to Excel. Attempting to run the DTSX file just results in a black (DOS?) window that appears and disappears immediately.
Hello, Tim. Were you able to solve it? We ended up re-installing all components in 64 bit and it runs fine 90% of the time; yet one of the SSIS packages would fail occasionally and yet works fine 1 minute later, when I re-run it. Frustrating! No such issue on 32 bit installation on older server.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
Hi @Alla,
Please check your Microsoft ACE Provider.
The 'Microsoft.ACE.OLEDB.12.0' became deprecated. You need to start using its 'Microsoft.ACE.OLEDB.16.0' version.
Yes, using version 16.0.5044.1000; forgot to mention that we are running it as 32 bit. Thanks!
There is no need to run the 'Microsoft.ACE.OLEDB.16.0' on the SSIS run-time server in a 32-bit mode. Just in VS2019 and earlier on a developer workstation.
Hello Yitzhak, we had re-installed everything in 64 bits (Office, VS), now the SSIS package works (most of the time, occasionally - not) when run as SQL job, but if I try to run it on its own under Integration Services connection in SQL Management studios - getting an error that Microsoft.ACE.OLEDB.16.0 isn't registered on local machine. We do have both 32 bit and 64 bit installed on the server. If I try to manually import or export excel through the wizard within SQL Server management - get the same 'not registered' error. Seems manual import/export is looking for 32 bit, but doesn't see it. We re-installed it again with /quiet option - didn't help. Any ideas what else can we do to resolve it? As I mentioned - under sql jobs the same SSIS package would run as 64 bit most of the times, occasionally failing. Appreciate your help!
The same story is applicable to SSMS like to Visual Studio.
SSMS 20.* and earlier is 32-bit app. That's why it is trying to launch 32-bit ACE OLEDB driver.
Though SSMS 21.* preview is 64-bit app. for the first time.
There is one more parameter that is affecting what driver version wizard is using.
It is Excel version: Depending on what version is selected, ACE 12.0, 15.0, or 16.0 is picked up.
Please see below.
I would suggest to run it via T-SQL command in SSMS, along the following:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
'Excel 12.0 Xml; HDR=YES;
Database=c:\Users\ykhab\OneDrive\Documents\dada.xlsx',
[Sheet1$]);
Sorry, confused. Are you saying that Ace.Oledb.16 doesn't work in VS 2022? Wasn't it your original question if we were using ver 16?
PLease use Comments for not Answers!
The 'Microsoft.ACE.OLEDB.16.0' is working well everywhere.
My point was about 32-bit vs. 64-bit.
Thanks. Which version should we be using with VS 2022/SQL 2022/Excel 2016?
Hi @Alla,
**'Microsoft.ACE.OLEDB.16.0' 64bit，**VS2022 is a 64bit app, so is SQL Server2022.
There is a 32 bit version of Microsoft.ACE.Oledb.16 as well. Do both versions have to be installed for it to work in 32 bit? Is it being discontinued? I can't find a clear recommendation if we should switch all projects to 64 bit or OK to use 32 bit?
Thank you