And so I’m back to designing SSIS packages. Its a fun activity really. SQL Server Integration Services is a really powerfull platform to move data around. It can read anything and write anywhere (and even if it can’t directly access something you can always call some 3rd party program to do it – but this is not the point of this post).
Back in SQL 2005 days I used to develop in a 32 bits machine, and the servers were also 32 bits. Not anymore: now everything is 64 bits. Well, except for Office. Office doesn’t like 64 bits (and neither does some Oracle clients, but again, not the point now). If you try to use an Excel connection it will work just fine while you design the package because BIDS run as a 32bits program at design-time, but at run-time it runs as 64bits and this may cause some weird errors when reading/writing to excel files.
There are some workarounds to make it work (in 2008R2 you have a flag inside the project/job properties named “Run64bitRuntime” / “Use 32 bit runtime”), but even doing this you may have some trouble. If you are just writing an output file the easiest and safest way would be to use a Flat File Destination and save the contents as a .CSV file.
Here is a cryptic message you may receive when using an Excel connection when you try to run your package:
[Excel Destination ] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
And here some more links on this subject: