Upgrading SSIS 2008 to SSDT 2016: connection provider is not registered and cannot change Platform to x86

Question

Upgrading SSIS 2008 to SSDT 2016: connection provider is not registered and cannot change Platform to x86

David Data

SSCrazy

Points: 2965
More actions
July 25, 2016 at 3:05 pm

#315591

I'm upgrading an SSIS package from SQL Server 2008 + BIDS to SQL Server 2016 + SSDT. The environment is SQL Server 2016 + Visual Studio 2015 Community Edition + SSDT 14.0, running on Windows 10 x64.
I did the package update with no apparent problems, and some tasks run, but the script task which imports Excel files, doesn't - which makes the package useless as that is the first task and sets up things for the rest. The connection was using Provider=Microsoft.Jet.OLEDB.4.0; so I updated it to Provider=Microsoft.ACE.OLEDB.12.0 and installed the 2007 Office System Driver as instructed in https://www.microsoft.com/en-us/download/details.aspx?id=23734, but I still get the error 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The full string I'm using in that script task is
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Client.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=0;"""
Furthermore, when I set breakpoints to use the debugger, the debugger window fails to open.
Googled pages suggest both problems may be associated with running in 64-bit mode, and I should set Platform=x86 to run in 32-bit mode (as BIDS used to do). The old '2008' code was set to Platform: AnyCPU, and it still is. It should be easy to change that to x86 using Configuration Manager, but I cannot do so. The Platform field is greyed out and won't let me enter anything - see below. As you can see, in Configuration Manager the Active Solution Platform is shown as 'Default'. If I click on that I'm show an option <New...>, but if I then click on that it just goes back to Default. It's supposed to let me chose between AnyCPU, X86 and i64, but it never shows those options.
I even tried global-editing the Package.dtsx file content from AnyCPU to x86, but this seemed to make no difference, and still ran in 64-bit mode.
I tried to install the 64-bit Office 10 drivers (AccessDatabaseEngine_X64.exe), but they won't install unless I remove all my existing 32-bit Office products, which I can't do as I use my existing Office Pro a lot for other work.
How can I change my code to run in 32-bit mode - or solve how to fix these two problems in 64-bit mode?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Data SSCrazy Points: 2965 More actions · Answer 1

Typical - I've spent 3 days trying to fix this problem, including much use of Google before posting here for your erudite investigation, and shortly after posting I found a partial solution in a comment on a long discussion on another forum. To save anyone else my long search, it's here: http://www.codeproject.com/Tips/417397/OLEDB-Provider-is-Not-Registered-on-the-Local-Mach?msg=5000948#xx5000948xx

The trick is to install the 64 bit Office 2010 driver with the Passive flag, so it just does it and doesn't make you uninstall previous (32 bit) versions. Which still work fine (yes I've tested them).

In a command window, type AccessDatabaseEngine_x64.exe /passive

I can now import Excel files while running in 64-bit mode. But I still can't use the Debugger, and still need a way to run in 32-bit mode aka 'x86'. Any ideas?

Phil Parkin SSC Guru Points: 246711 More actions · Answer 2

Try clicking on the 'Debugging' node. You'll find a property there called 'Run64BitRuntime'. Set that to false & then your debug sessions should be in 32-bit mode.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

David Data SSCrazy Points: 2965 More actions · Answer 3

Thanks. I thought I had already set that, but it must have been on a different test. Then I couldn't find how to set it; I was looking at the package I was testing, but I see now it has to be set at the Project level in Solution Explorer, and the Debugging tab is in Configuration Properties.

Having set 64bit to False, debugging works rather better - but I'm still getting these messages which state that it's running in 64 bit mode. Very odd.

David Data SSCrazy Points: 2965 More actions · Answer 4

I've added code to my application that logs whether it's running in 32 or 64 bit mode (example shown below, using Msgbox in place of my logging function).

Setting Run64BitRuntime to False to force 32 bit mode as Phil suggests works (Thanks!), but doesn't seem to stick; when I run Visual Studio again it reverts to True and hence 64 bit mode. However, having now installed both 32 and 64 bit versions of the Office 2010 driver the application runs either way.

' If the Integer Pointer size is 4 then its 32 Bit and 8 is 64 Bit - could also use an Environment property

Dim BitMode As String = If(IntPtr.Size = 8, "64", "32")

Dim Server As String = Environment.MachineName

Dim CPUCount As Integer = Environment.ProcessorCount

Msgbox (String.Format("Started at {0} in {1} bit mode on {2} with {3} CPU cores", Now(), BitMode, Server, CPUCount)

Phil Parkin SSC Guru Points: 246711 More actions · Answer 5

Well done, David, both for solving your issues and for posting your findings – this will be of help to others, I am sure.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Phil Parkin SSC Guru Points: 246711 More actions · Answer 6

Hello again. I am currently going through some of the hell which you went through, and unfortunately, your solution does not seem to work for me.

I suspect that some of this may be down to the version of Office installed on my PC (2016, 64-bit).

May I ask which version of Office you had installed?

--Edit: no need to answer that. I see you mentioned it in your initial post.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.