I had a problem getting an SSIS package to write correctly to an Excel destination.
The first issue was that the Excel Connection Manager couldn't work in 64 bit mode, which my operating system and the SQL server running the package are.
This required a little fix in both the dtsx package, and the SQL server agent job running it.
The first step of fixing the dtsx package came from:
http://merlecarr.wordpress.com/2011/02/12/the-excel-connection-manager-is-not-supported-in-the-64-bit-version-of-ssis/
1: Open the project properties window (Not the package properties, but its parent project).
2: Go to Debugging beneath Configuration Properties.
3: Set Run64BitRuntime to False.
Then in your Server Agent job's properties, open to edit the step(s) running any such package and:
1: Open Advanced beneath the "Select a page" menu on the left.
2: Choose the "Exectution options" tab.
3: Check "Use 32 bit runtime".
Now that this worked, the new problem was that by default, the Excel destination just gets appended to, not overwritten. If you need to always refresh the destination file data, you have to first run a task to wipe the slate clean before importing the new data.
Ultimately this thread provided most of the answer:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e26692b8-8044-4813-b643-0c181a1ea4fe/deleting-records-in-an-excel-sheet-using-ssis
1: Create a blank template of your destination Excel file to preserve your column setup.
2: Place a File System Task ahead of your Data Flow Task that imports the destination data.
3: In that file system task, set Operation to "Copy File".
4: Set your Destination Connection to the destination file, with OverwriteDestination set to True.
4: Set Source Connection to your template file.
That should be all it takes, but wouldn't it be amazing if MicroSoftInTheHead would update their @#$%^&* Jet Engine to provide a simple checkbox for overwriting existing data in the Excel Destination data flow? Yeah, right, like we'll ever see that in our lifetime.
