title: Table Variables in SSIS
body: <p>In one SQL Task can I create a table variable</p>
  
  <pre><code>DELCARE @TableVar TABLE (...)
  </code></pre>
  
  <p>Then in another SQL Task or DataSource destination and select or insert into the table variable?</p>
  
  <p>The other option I have considered is using a Temp Table.</p>
  
  <pre><code>CREATE TABLE #TempTable (...)
  </code></pre>
  
  <p>I would prefer to use Table Variable so that it remains in memory. But can use temp table if it is not possible to use table variable. Also I cannot use the record set destination as I need to preform straight SQL tasks on it later on.</p>
  
  <p>The use case that this is trying to solve is essentially performing a transformation in the stead of BizTalk. There is a very large flat file to flat file transformation that BizTalk has to transform unfortunately the data volume would produce unacceptable load on the BizTalk server so the idea is to off load it to SSIS. However, it is not a simple row to row transformation, there are different types of rows which have relations to each other. The first task in SSIS is to load the row into appropriate (temp) tables, then in the second data task a select is preformed with the correct format for output.</p>