title: SSIS Dynamic Connection String from SQL Table
body: <p>I have to connect to many Foxpro databases that have a connection string like:
  Data Source=\\All Users\\DB0009\db1.dbc;User ID=xxx;Password=yyy;Provider=VFPOLEDB.1;Persist Security Info=True;</p>
  
  <p>where the folder DB0009 can be any integer from 0000 to 0100 and the db1.dbc can be either db1.dbc or db2.dbc for each folder.</p>
  
  <p>For each connection string, I need to build a simple SQL statement that is identical for every database except for a hardcoded year.  So the sql is:  select *, '2012' from table</p>
  
  <p>I'd like to be able to store both the connection string AND the year for each connection string in a sql table that can be looked up at run time.  </p>
  
  <p>I am using SSIS 2008.  I'm guessing based on what i've seen that i can use the foreach loop with the enumerator set as the ADO Enumerator (though i wouldn't mind confirmation there), but how can i pull the year off of that same table and put it into the sql query i have inside a data flow task?</p>
  
  <p>Thanks for any help!
  Kary</p>