September 5, 2013 at 4:34 am
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.
September 5, 2013 at 4:47 am
Installing Excel on the server is not necessary.
Can you execute the package in SSDT?
September 5, 2013 at 6:26 am
Did you try installing this ?
September 5, 2013 at 3:16 pm
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.
September 6, 2013 at 12:30 am
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 ...
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 6, 2013 at 3:29 am
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? 🙁
September 6, 2013 at 6:52 am
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."
September 6, 2013 at 7:48 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 6, 2013 at 9:12 am
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!
September 16, 2013 at 4:02 pm
+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.
September 17, 2013 at 7:06 am
hey,
thanks for the reply,
yeah excel just seems a horrid pain, ive been reading up on SSIS frameworks and such, and wondering what people do to make sure the data gets in there.. (most of our Excel etls fail at least once a week due to column widths.)
if you cant garuntte the source file, do you make alterations to the load table? say make every col varchar(max) so you wont get errors loading it in then clean up after?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply