SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Connecting to Excel via Agent job Issues


Connecting to Excel via Agent job Issues

Author
Message
Staggerlee
Staggerlee
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 1128
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.
SQL Surfer '66
SQL Surfer '66
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 328
Installing Excel on the server is not necessary.

Can you execute the package in SSDT?
Tee Time
Tee Time
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 Visits: 465
Did you try installing this ?
Microsoft Access Database Engine
Staggerlee
Staggerlee
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 1128
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.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18307 Visits: 20410
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Staggerlee
Staggerlee
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 1128
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? Sad
Staggerlee
Staggerlee
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 1128
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."
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18307 Visits: 20410
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Staggerlee
Staggerlee
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 1128
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!
aaron.reese
aaron.reese
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1039 Visits: 902
+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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search