Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Connecting to Excel via Agent job Issues Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 4:34 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 315, Visits: 768
Hi all,

Im guessing this is a simple one but im missing it somehow.

We are trying to import data from an excel file with SSIS 2012. From what i understand you just need to run the Agent Job in 32bit (Go into the Step that uses SSIS and tick "Use 32bit bit runtime" in the Execution Options.

But its still failing, with the error:

The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

Devs are suggesting installing Excel onto the server. But surely you dont need to do that?!

Hope someone can offer some assistance.
Post #1491706
Posted Thursday, September 5, 2013 4:47 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, January 31, 2014 2:56 AM
Points: 483, Visits: 256
Installing Excel on the server is not necessary.

Can you execute the package in SSDT?
Post #1491710
Posted Thursday, September 5, 2013 6:26 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:09 AM
Points: 532, Visits: 447
Did you try installing this ?
Microsoft Access Database Engine
Post #1491748
Posted Thursday, September 5, 2013 3:16 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 315, Visits: 768
hi, sorry for the delay in getting back to you!

so it looks like its actually an environmental variable issue. The agent job fails with the jet engine problem, but looking at the data config section they are pointing to the wrong place. (these are packages that have been set up in a dev environment and moved to a new server for the next level of testing

trying to find a solution. I saw a couple saying to do service restarts after the vars are set. But so far no joy (even tried restarting the whole server). Not sure, checked and double checked that the variables are right on the server and all paths are correct, but its just not picking them up..

Any ideas? and apologies for the wild goose chase.



Post #1491966
Posted Friday, September 6, 2013 12:30 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:49 PM
Points: 4,973, Visits: 11,660
Restarting the SSIS server would definitely do the job, if everything is configured correctly.

So there must be something in there which is not quite right ...



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1492048
Posted Friday, September 6, 2013 3:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 315, Visits: 768
well this is frustrating!

So, i decided to re-create the the dts.config file that enviro var pointed to, in a new location and re-set the var. Re-started the machine. used SET in command and it was pointing to the new location. Opened up SSMS in SSIS mode and looked at the msdb packages and joy of joys it was now showing the correct paths.

Since we have 2 enviro vars i then did the same to the other one. And after the reset. The original packages and the second set are both failing again..

This is Windows 2012, SQL 2012 SP1 CU5,

The folder the files are in, has permissions for all the SQL services to modifty (seems vastly more than it needs to read the file, but clutching at straws now)

Ive tried both C:\config files\TeamA.dtsconfig
and \\servername\config files\TeamA.dtsconfig (config files is a share)

Im doing re-starts between each envi var update to make sure that everything is getting the latest info.

Any suggestions?
Post #1492110
Posted Friday, September 6, 2013 6:52 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 315, Visits: 768
Well another restart has it working again but the second set of env are still not working. (scared to re-start it as it seems to change with every reboot!


On an excel related note.

If you have to run packages that use excel in 32bit i take it I will need dtexec 32bit. Its apparently only installed if you add the client tools or SSDT to server!

http://technet.microsoft.com/en-us/library/ms162810(v=sql.105).aspx

"On a 64-bit computer, Integration Services installs a 64-bit version of the dtexec utility (dtexec.exe). If you have to run certain packages in 32-bit mode, you will have to install the 32-bit version of the dtexec utility. To install the 32-bit version of the dtexec utility, you must select either Client Tools or Business Intelligence Development Studio during setup."

Post #1492203
Posted Friday, September 6, 2013 7:48 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:49 PM
Points: 4,973, Visits: 11,660
I'll give the same advice I give all Excel importers:

Use CSV files if you can instead - you will find that it all works better.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1492243
Posted Friday, September 6, 2013 9:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 315, Visits: 768
haha, yep.. starting to see that as the way forward in the next review!

Installing SSDT has installed dtexec 32bit. It says that the client tools will do it as well but not sure what it means. I tried the client connectivity tools and that failed to install it. and has them working..

Just need to find out why the environment variables arent updating now and my world will once again be at peace!

Thanks for the help!

Post #1492294
Posted Monday, September 16, 2013 4:02 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 4:26 PM
Points: 386, Visits: 622
+1 for Phils suggestion. Keep Excel a long long way away from SSIS.

32/64 bit compatability issues (made more complicated when you develop on 32bit laptops and deploy on 64 bit servers)

Differences between .xls .xlsx and .xlsm

sensitivity to sheet names, columns types and all other crap that users change in spreadsheets when they don't know what they are doing (sorry - I mean don't appreciate the knock on effects of their actions). The last one I had lost me half a days debugging: a user had protected part of a worksheet which meant that the .xlsx was encrypted and could not be read by the parser, even though the spreadsheet itself was not protected with a password.
Post #1495315
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse