title: SSIS - using VB to dynamically create ole db source where the SQL source is too dynamic
body: <p>I'm very new to SSIS, but have this week been completely burried in turorials of how to use it, this has been very fruitful so far but now I'm stuck with what seems a more complicated task. </p>
  
  <p>I have two source SQl tables, which are dynamically created using the TSQL pivot function - so the numebr of columns in each will vary. </p>
  
  <p>I feed a list of criteria into a for each loop whic his set to use an ADO enumerator to go through this data set and has a script task to set the string variable SQL_DOWNLOAD_STRING to point to one of the two poential tables using an IF statement. TABLEA or TABLEB.</p>
  
  <p>I tried using the standard OLE DB source in the data flow, but the associated meta data was generated based on TABLEA at the time of build. I then set the SQL statement to be based on the variable SQL_DOWNLOAD_STRING and when I execute, the meta data of the source has changed as obviously it is a differnt table. That said even if itwas the same table there is no guarentee that the columns would be the same. </p>
  
  <p>So my question is - how do I create the OLE DB Source dynamically so that it reconfigures to the new structure of the table? </p>
  
  <p>Then I'm trying to export the data to an Excel workbook (which again I can do for a static source) but in this instance the mapping would need to change too &amp; I imagine I'll need to rebuild the destination table to match the source from above.</p>
  
  <p>If I can get a working example - then finishing the project should be pretty staright forward from there - examples for this sort of thing seem pretty limited though :( </p>
  
  <p>Please help! (PS I'm working in VB)</p>
  
  <p>Thanks</p>