title: SSIS data flow task does not write records to excel file when executed after tasks that empty the file
body: <p>I am writing an SSIS package to extract data from a DB and write it to an excel file on a weekly basis. The excel file has to be re-generated from scratch every time data is offloaded.
  I realize i cannot override the existing file if i use an excel destination in my data flow task, so i tried doing the following:</p>
  
  <ol>
  <li><p>add an Execute SQL task before the data offload to recreate the existing sheet in the file using the following sql</p>
  
  <p><code>DROP TABLE Users$</code></p>
  
  <p><code>GO</code></p>
  
  <p><code>CREATE TABLE Users$ (
  Ref NVARCHAR(10),
  Username NVARCHAR(20),
  Active INT,
  Absent INT,
  Allow Reconciliation NVARCHAR(4),
  Prefix NVARCHAR(10),
  First Name NVARCHAR(25),
  Middle Name NVARCHAR(25),
  Last Name NVARCHAR(40),
  Suffix INT,
  Email NVARCHAR(33),
  Work Phone NVARCHAR(10),
  Required Approvals NVARCHAR(1),
  Division/Section Ref NVARCHAR(15),
  Grade Ref NVARCHAR(9),
  Cost Centre Ref NVARCHAR(15),
  Location Ref NVARCHAR(5),
  Last Hire Date NVARCHAR(10),
  Original Hire Date INT,
  Manager Ref INT,
  Approver Ref NVARCHAR(6),
  Gender NVARCHAR(6),
  Ethnicity NVARCHAR(25),
  Language NVARCHAR(5),
  Expiry Date NVARCHAR(10),
  Staff Category NVARCHAR(50),
  Source of Funding NVARCHAR(4),
  BAC Number INT,
  Title NVARCHAR(50)
  )</code></p>
  
  <p><code>GO</code></p></li>
  <li><p>keep an empty file template with only the column headers to make sure the column mapping is preserved and, using a script task before the data flow task, replace any existing output file with it.</p></li>
  </ol>
  
  <p>in both cases, the package successfully executes and i see in the log it says records were written to the excel file but when i open the file i only see the header row, no data.
  my impression is that the column mapping gets messed up when the file is replaced or the sheet recreated, i am not sure how i can proceed.</p>
  
  <p>EDIT: i must mention that executing the data flow task alone ends up writing the records.
  please advise
  thanks</p>