We have a custom report that utilizes MS Excel to import data into Tessitura. We recently upgraded Tessitura to Win 2K8R2 and SQL 2K8R2, and now this report no longer works. We are wondering if it is because of incompatibilty with Excel and SQL server:
http://msdn.microsoft.com/en-us/library/ms140052.aspx
Money quote:
"On a 64-bit computer, Integration Services installs the 64-bit version of the SQL Server Import and Export Wizard (DTSWizard.exe). However, some data sources, such as Access or Excel, only have a 32-bit provider available. To work with these data sources, you might have to install and run the 32-bit version of the wizard. To install the 32-bit version of the wizard, select either Client Tools or Business Intelligence Development Studio during setup."
Our workstations are using Excel 2007 (32-bit), and of course our new Tessitura is 64-bit. When I investigated the above article further, I noticed that we have installed something called "Client Tools Backward Compatibility":
http://msdn.microsoft.com/en-us/library/ms143786.aspx
Documentation is pretty sparse, since this software is still relatively new. But to me it sounds like that should resolve any incompatibility issues. I can always uninstall the Client Tools feature in our instance of SQL, but since this is a production server, that option would incur downtime. Do-able, but not preferable.
We've also thought of purchasing a license of Excel 2010 and installing that on one of our 64-bit machines. Maybe 64-bit to 64-bit would work. Or maybe this is a red herring, and we're on the wrong path entirely...is there any way to verify?
We used to do this manually, but that was a major pain...
Has anybody encountered this before? Any suggestions before we plow forth? : )
Thanks,
Britt