0
  • Windows 10 Pro 64-bit (Version 1903)
  • Sql Server 2019.
  • VS 2010.
  • MS Office 2016 64-bit.

Moving a legacy SSIS package from an older 64-bit environment.

Unable to create an Excel Source or Destination in VS. When attempting Excel 2007, then VS complains that ACE.OLEDB is not registered. When attempting Excel 97, VS complains about JET. The messages have not changed either before or after installing Access Database Engine end Microsoft Access Runtime 2010. Beginning to wonder if this is supported at all.

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
FormerSailor
  • 1
  • 1
  • Is the older environment still running? Use this method http://www.wadewegner.com/2007/04/creating-a-universal-data-link-udl/ and compare what is in the _Provider_ tab between the old server and the new server - see if you have ACE installed. Keep in mind that VS2010 is _probably_ a 32 bit app and can therefore only see 32 bit drivers. Yes there is no issue installing 32 bit drivers on a 64 bit OS. – Nick.Mc May 04 '20 at 00:59
  • The old environment shows OLEDB providers for Analysis Services, Indexing Service, ODBC Drivers, Sql Server, Simple Provider, and Microsoft Directory Services. The new environment has all those plus Microsoft Office 12 Access Database Engine, Microsoft Office 16 Access Database Engine.and Sql Server Integration Services. – FormerSailor May 04 '20 at 02:26
  • So it’s saying the 32 bit ACE driver is not installed. I guess the 64 bit one is. Maybe this will help https://www.google.com.au/amp/s/datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/amp/ – Nick.Mc May 04 '20 at 02:49
  • Thank you Nick. So I uninstalled the 64 bit Access Database Engine. The UDL provider list shows no Access sources. Attempting to install 32 bit Access Database Engine yields a message that the product is already installed, though it does not show anywhere in Settings - Apps. In VS, the error message for attempting to use an Excel 97 - 2003 destination is "Microsoft JET Database Engine" Hresult:0x80004005. For an Excel 2007 destination, the message is ACE.OLEDB.12.0 is not registered. Argh! – FormerSailor May 04 '20 at 05:02
  • I think it is probably not possible. – FormerSailor May 04 '20 at 22:58
  • Possibly. It's just a matter of getting the 32 bit ACE driver installed. ACE and JET are two different drivers and JET should be much easier to install – Nick.Mc May 04 '20 at 23:19
  • 64 and 32 bit drivers are very confusing. You just have to remember: VS2010 is a 32 bit app so it can only see 32 bit drivers. So you should only install 32 bit drivers. – Nick.Mc May 04 '20 at 23:20
  • Does your error message say the _64 bit_ version is already installed? Because there are various workarounds to get the 32 bit and 64 bit installed at the same time. This is a real nightmare and MS have really stuffed this up. https://stackoverflow.com/questions/12270453/ms-access-db-engine-32-bit-with-office-64-bit – Nick.Mc May 04 '20 at 23:26
  • Thank you Nick. MS did make a mess of this. Seems like the 32-bit versions are required to do development with VS. It was a huge relief when I got those installed and VS finally was able to read an existing Excel 2007 file. However, without the 64-bit drivers, the task would not execute from Sql Server Agent. – FormerSailor May 06 '20 at 00:09
  • Using the tricks that are described in various postings on the subject of ACE.OLEDB.12.0, I installed the 64-bit drivers beside the 32-bit drivers. That satisfied Sql Server Agent and the tasks began executing correctly just in time to keep my head from exploding. I appreciate your continuing assistance. This issue is resolved. – FormerSailor May 06 '20 at 00:14
  • Yes that's what I meant by _VS2010 is a 32 bit app so it can only see 32 bit drivers_. This is the case for everything, not just database drivers. An EXE can only use DLL's of the same "bitness". You can actually make the packages run by SQL Agent run in 32 bit mode, then you only need the 32 bit driver and not the 64 bit one. – Nick.Mc May 06 '20 at 00:48
  • Wish I had stumbled on this earlier. It is the clearest explanation of this situation that I have seen. https://support.microsoft.com/en-us/help/934653/how-to-use-the-64-bit-sql-server-agent-to-schedule-a-sql-server-2005-i – FormerSailor May 16 '20 at 14:15
  • You can now just tick “run as 32 bit” rather than having to go find the exe. – Nick.Mc May 16 '20 at 23:48
  • I think that is one of the features they left out (I mean deprecated) in SSMS 18.5. – FormerSailor May 18 '20 at 05:54
  • This is a screenshot of what I mean. When you set up the package to run as a job you can specify that it should run as 32 bit. I would be very suprised if it is n't there in 18.5. I can't test on mine as I have no packages. http://3.bp.blogspot.com/--4lgVUakA3E/UEyD1r8uYsI/AAAAAAAAA6k/hnG54fcFJUw/s1600/32bit03.jpg – Nick.Mc May 18 '20 at 09:54
  • I was surprised too, but the option is not present in my installation of SSMS 18.5. Possibly the option is hidden if the 32 bit runtime is not installed. I find no mention of the option in the release notes. – FormerSailor May 19 '20 at 14:46

0 Answers0