SSIS 64bit JET driver

[SQL_Hell]

Hi,

So.... I just spent most of my morning developing a SSIS package to export some data into an excel document, do a bit of filecopying and send some happy email informing the users that the joyous export has been completed, praise the lord!!

This SSIS package worked fine in my 32bit machine, but when I tried to schedule this package via a sqlserver job on my 64bit server, I was getting connection errors. The connection that was failing was a connection to a local drive on the server.

After a bit of headscratching, I found out that there is no 64bit Jet driver for Excel.... grr how annoying!!

Does anyone know if 64bit JET drivers will ever exist?

Anyone know of a way round this?

If I cant solve this, I will have to output .csv instead..

 

[Banana]

Unfortunately, no. There will be an 64-bit driver for Access... 2010. Which of course isn't shipping just yet... (Well, there's public beta, but do you really want to use beta in production??)

 

[SOS]

You really need the 32 bit driver since you don't have 64 bit Excel installed (it isn't out yet).

So, the fun thing is that you have to go into the Windows directory and find the 32 bit odbc manager to open it up and there should be a driver there. I am not where I can find the file exactly (my 64 bit machine is at home) but you just search the WOW directory for odbcad32.exe

 

[Banana]

Well, yeah, but I _think_ he'd have to then go back using 32-bit SSIS, and I don't think this is possible against a 64-bit SQL Server? I may be wrong, though, but the way I understand is that only 32-bit applications can use 32-bit drivers... 64-bit applications (e.g. SSIS) can't use the 32-bit drivers.

Would like to know if 32-bit SSIS can be used on a 64-bit OS (I'm sure that's doable) against a 64-bit SQL Server (this I'm not too sure about...)

 

[SOS]

I use 32 bit SSIS on a 64 bit machine.

 

[Banana]

Right. I didn't have doubts of running 32-bit SSIS on a 64-bit machine. What I'm uncertain about is whether 32-bit SSIS can work with 64-bit SQL Server.

 

[SOS]

It doesn't matter - really. The ODBC driver needs to be 32 bit to connect to 32 bit Excel. Just like if you had Windows 3.1 and then moved to Windows 95 you could still connect to things using the 16 bit driver (UNTIL they had the newer 32 bit apps and then you needed a 32 bit driver to connect to those apps).

 

[SQL_Hell]

Been doing a bit of reading up....

On a 64bit SQL server installation, the agent will run the SSIS package in 64bit mode by default.

In the following article it says you can change the execution options to 32bit execution mode on the new job step dialog box, but I cannot see this option.

Here is the article I was reading (main bits are towards the end of the article)


http://msdn.microsoft.com/en-us/library/ms141766.aspx

P.s thanks for the replies so far

 

[SOS]

Again, I will state -

You do not need a 64 bit ODBC driver to run on a 64 bit SSIS system. You only need a 64 bit ODBC driver if you have a 64 bit program that you are connecting to.

So, give it a try and see what happens. You will need to get to the ODBC manager like I said before.

 

[Banana]

Awesome news.

As for you not seeing the options... Could that be because 32-bit parts aren't installed? The doc say it's an optional installment...?

 

[Banana]

Again, I will state -

You do not need a 64 bit ODBC driver to run on a 64 bit SSIS system. You only need a 64 bit ODBC driver if you have a 64 bit program that you are connecting to.

So, give it a try and see what happens. You will need to get to the ODBC manager like I said before.

Hmm. I'm confused now. In a recent discussion somewhere in other group, I was led to understand that the significance of bitness depends on the client, not the server... in case of SSIS, SSIS is the client so it must use matching x-bitness of the driver in questions... Since there are no 64-bit drivers for Access, SSIS has to run in 32-bit in order to use Access ODBC drivers... ???

 

[SQL_Hell]

I am not really sure what you are getting at SOS, yeah sure I can open up the folder and see the 32driver.

But my SSIS package wont be able to use to it, the package fails running in 64bit mode. If you read the article I posted it also supports my theory

 

[SOS]

Awesome news.

As for you not seeing the options... Could that be because 32-bit parts aren't installed? The doc say it's an optional installment...?

No, remember that you can't get to the ODBC drivers on a 64 bit machine by going to the usual location. You can't open CONTROL PANEL > ADMINISTRATIVE TOOLS > DATA SOURCES (ODBC)

You have to go find the actual 32 bit odbc manager under the Windows 64 WOW folder in C:\Windows or something thereabouts. Like I said, I don't have my 64 bit machine with me so I can't look there but that's what you would need.

And that article doesn't mean you have to run this package as a 32 bit package. You should STILL be able to connect to the 32 bit data source and/or destination using a 64 bit package.

 

[SQL_Hell]

From the article:

Designing Integration Services Packages on 64-bit Computers
You cannot design packages in BI Development Studio or debug scripts in the Script task on Itanium-based operating systems.
When you run a package in 64-bit mode, you might not be able to connect to as many data sources as you can when you run a package in 32-bit mode. Some .NET Framework Data Providers and native OLE DB providers might not be available in 64-bit versions. For example, the Microsoft OLE DB Provider for Jet, which connects to Access databases and Excel spreadsheets, is not available in a 64-bit version. Also, the SQL Server Compact Provider, which connects to SQL Server Compact data sources, is not available in a 64-bit version

 

[SOS]

We're not talking about OLE DB providers here. We're talking ODBC - there is a difference.

 

[Banana]

Just for benefits of other readers:

To get to 32-bit ODBC administrator:

%systemdrive%\Windows\SysWoW64 folder\odbcad32.exe

To get to 64-bit ODBC administrator:

%systemdrive%\Windows\System32\odbcad32.exe

Note that both are named the same but in different folder. Why they didn't use "System64\odbcad64.exe" is beyond me, though. WOW64 = "Windows on Windows 64" or maybe more clearer if one thinks of it as "W32OW64". At least that's what works for me.

 

[SQL_Hell]

I realise the difference between OLE DB and ODBC, and I understand that you think I can use the 32bit ODBC driver, but how?

I develop my package in my 32bit environment using an "excel destination" and its works fine, move it over to 64bit and it doesn't work.

How can I tell it to use the 32 driver in a 64bit installtion, I have already told it to use the 32bit driver when developing it on a 32bit machine.

 

[SQL_Hell]

Finally got it to work,

I forced 32bit execution of the package using the 32bit version of DTEXEC.

I did this by changing the job step type to Operating system command, and then executed the following string:

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "D:\test.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

Thanks for the replies

 

[Banana]

SQL_Hell,

If the article is anything to go off, it says that 32-bit drivers and 64-bit drivers have the same ID in the registry so when you build a package on 32-bit machine using 32-bit drivers and move it to a 64-bit machine that happens to have 64-bit driver, it will automatically use 64-bit driver (it appears to be identical as far as the client is concerned). Since there's no 64-bit drivers to be had, you only have 32-bit drivers so the package should use it. SOS says that you should be able to do this, but you said you couldn't? If this is the case, I'd try and running the SSIS in a 32-bit mode... you may have to install the 32-bit SSIS as it seems to be an optional install...