Calling Microsoft.Jet.OLEDB From Inside a Script Task

Question

Calling Microsoft.Jet.OLEDB From Inside a Script Task

James Riehl

Ten Centuries

Points: 1154
More actions
May 8, 2008 at 9:35 am

#190181

Here the situation, hopefully I'm asking it in the right place.
I've got a service that watches for files being dropped in one location. This service determines what file type was dropped and then calls the appropriate ssis package to process it.
One of the filetypes that is dropped is an excel file and I've build the package with a Script task that uses Jet to open an excel file, parse it and write the parts that i'm interested in to a csv file for easier processing with in the ssis package.
The package works like a charm on my dev box, and even works well when I'm explicitly double click on the .dtsx file, however, it doesn't work at all when it's being called by the service.
I'm getting the following:
"The script threw an exception: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine."
Which would normally imply that the Jet engine isn't being referenced properly or wasn't installed on the box to begin w/.
It's my understanding that it's installed when you install ssis, and the fact that it has no problems opening the xls file when called explicitly leads me to believe that the problem has nothing to do w/ the lack of Jet.
here's the code i use to open the file:
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;"
Dim objConn As New OleDbConnection(sConnectionString)
objConn.Open()

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

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You said it works from your dev box - which I will assume is the server your service is on.

My first guess would be an Excel issue. Log into your server as the NT account the service runs under and make sure you can open Excel under this login context.

James Riehl Ten Centuries Points: 1154 More actions · Answer 2

Sorry not to specify this further, here are some more details...

Excell is not installed on the server, that is why I am using the oledb drivers.

Development is my machine, 32 bit windows xp, and the package runs when executed through bids.

Production is a 64 bit windows server 2k3. I can run this by navigating to where the .dtsx file is, and double clicking on it. However, when the package is called through my service It complains that the Microsoft.Jet.OLEDB.4.0 provider is not registered on the local machine.

But this can't be the case b/c it gets registered when SSIS is installed and seems to be perfectly well registered when it's executed by me through double clicking the .dtsx file.

Also for the sake of sanity, i set the service to run as my user, so priv's are the same in both cases.

Anyone ever come across this?

Thanks in advance.

James Riehl Ten Centuries Points: 1154 More actions · Answer 3

Anyone have any thoughts on this? Even confirmation of my assumptions would help.

Thanks.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

One more clarification.

When you navigate to the .dtsx file, are you logged into the "Production 64 bit Win2k3 server" (either via rdp, or sitting at the server)?

This production server is both the server that your service is installed and running on as well as the SSIS server?

Finally, I think the Jet OLEDB Driver for Excel needs to have Excel installed. Can anyone else confirm?

James Riehl Ten Centuries Points: 1154 More actions · Answer 5

When I log into the production box i use my domain log in through remote desktop.

I then can navigate to the .dtsx file. and execute it successfully.

for the sake of permissions arguement i set the service to log on and run as my user to ensure all credentials were the same.

Yes, the ssis, and the service are running on the same production box.

My assumption is that loading through oledb is different than opening the file programitically using excel. And that the OLEDB drivers are what SSIS uses to read and write to files. Therefore, i'm making the leap of faith that the oledb drivers are there for me to use when SSIS is installed.

Can anyone confirm this?

Thanks for your response.

michael_baker SSC-Addicted Points: 490 More actions · Answer 6

James,

I've got the same issue as you have in trying to run script tasks that access an Excel file. My environments are identical, 32bit XP dev and 64 bit Win2k3 production with 64 bit SSIS running and no Excel installed, but the msjet40.dll are present on the box (just not registered).

In my tracking down of this issue I've seen some post that seem to indicate that there is not a 64 bit version of the JET database engine (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125207&SiteID=1) and this may indeed be the root of the issue. The work around (according to the post) is to set the application compiler to run as 32 bit mode, but that's not possible within BIDS I don't think for this one script task,the entire SSIS application would have to be downed to 32bit if that is the case.

So I guess I'm looking for clarification here from some of the other experts. Is there 64bit version of the msjet40.dll and can it be accessed from SSIS script task and script components?