I created a view that displays all the needed fields that I want exported in Excel. My DTS contains a Transform Data task that uses the view as the source. In the destination tab, I used the created button to format the data in the order that I want it and named it as "LiveLabels".
Once the DTS is saved, I can execute the DTS without any problems and could also see the generated excel output. The problem arises when I run the DTS again. the DTS appends the data into the excel file instead of overwriting it. Also, if I delete the Excel file and re-ran the DTS, I get an error in the destination tab of the Transform Data Task saying:
Error Source: Microsoft Jet Database Engine
Error Description: The Microsoft jet database engine could not find the object "LiveLabels". Make sure the object exists and that you spell its name and its path correctly.
Another thing, when I used the "create" button in the Destination tab and I click on the table name, I always get 2 versions of the table, one is just plain LiveLabels and the other has a $ sign appended to it.