Ok, I readily admit I don't know everything about SQL Server. But today I learned something that I would have thought I would have seen somewhere. I was having issues getting an SSIS package to build some offline cubes (on my 32 bit workstation, and tested initially on a 32 bit server) to work on a 64 bit server.
It worked great in BIDS. Then I tried it interactively by double clicking on it. That ran swell. Took the command line it provided me, nice (gave me a nice log of the activities that I set up, for which I modified a connection. Worked.) Then I moved it to my 32 bit server, everything went great. Lather, rinse, repeat.
So I go to do the final test on the server before sending out the customary "I did it!" email to the people who had been (nicely) hounding me for the past few days. I interactively tested it. Excellent. One last thing, command line and boom. I could not get it running. Urgh, I tried everything, especially permissions. So I search over most of the internet (it is amazing what you can find on the internet. Did you know Peter O'Toole has never won an Oscar?) looking for SSIS problems, and there are lots of little things, but nothing seemed to fit.
Then I look for 32 bit 64 SSIS in my search. IS considerations on 64 bit computers. Very interesting, and it references Program Files (x86). I had not seen this at all. How cool. So I simply executed the 32 bit version of dtexec and everything worked fine. What was interesting was that the package likely worked in the dtsexecui (the UI version) just because it is 32 bit only.
I wasn't sure if I should post this, but frankly no one that I work with new about this, and a coworker who recently left us had worked on this a while with no luck. Truthfully, I feel a bit "dirty" about this. It could be that the OLAP drivers that are being used don't work on a 64 bit platform (that is my guess, at least,) but without real knowledge of this, I feel like using the 32 bit versions are cheating.
But at least now I can research more without project managers asking when I will be done :)
A few other things I have found:
http://ssis.wiki.com/index.php?title=64-bit_Story&bc=1 (from Bob Beauchemin's comment)
crossposted to: http://drsql.spaces.live.com