title: SSIS Using Loop Container Through Report Ids to create separate csv files
body: <p>Let me preface this with saying I am still a newbie when it comes to SSIS.  I am trying to replace a current MS Access process that has 13 queries that get created in excel and emailed to a list of users. </p>
  
  <p>My SSIS package needs to get the report name, email dist list, report data, create a csv file and then send an email for each one.</p>
  
  <p>I have created the following in SQL Server:</p>
  
  <ol>
  <li>a table with the reports - each has a ReportId</li>
  <li>a table with the users email addresses</li>
  <li>a join table that links the user to each report</li>
  <li>Created a stored proc when I pass in the ReportId and it returns the Email Dist List as well as some report details for use when I send my emails</li>
  <li>Created a stored proc that I pass the ReportId to. In return I will get any of the values for that report.  Each report has a different query that returns different columns of data.</li>
  </ol>
  
  <p>I have also attempted to create an SSIS package that does the following:</p>
  
  <ol>
  <li>using an <code>Execute SQL Task</code> get the list of Active Report Ids</li>
  <li>a <code>Foreach Container Loop</code> set to <code>ADO Enumerator</code> to loop through each report Id</li>
  <li>an <code>Execute SQL Task</code> that will return my report details and email dist list for each report id.</li>
  </ol>
  
  <p>Then I tried placing a <code>Data Flow Task</code> inside of my loop and to run my stored proc to return the report data for each report id but I am getting an error message:</p>
  
  <pre><code>Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. 
  Error code: 0x80040E55. An OLE DB record is available.  
  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E55  Description: "Column does not exist.". 
  </code></pre>
  
  <p>I am guessing this error is because my columns are not the same on each report. If that is the case, then do you have any suggestions about how to proceed?  </p>
  
  <p>What steps should I use in my SSIS package to go through each report?  Do I need to create a separate SSIS package to run each report?  </p>
  
  <p>If I can run this in one package, do I need to create a flat file connection manager for each one of my csv reports that I generate?  </p>