title: Using foreach loop container to create new columns in a table- can't solve "incorrect syntax near '@P1'" error
body: <p>I'm trying to use a foreach loop container to take row values and make them into fields. But for some reason I can't get it to work without running into this error:</p>
  
  <blockquote>
    <p>alter table /databasename/.dbo.cp_hh_foo..." failed with the following error: "Incorrect syntax near '@P1'.</p>
  </blockquote>
  
  <p>The problem appears to be in the final execute SQL statement. The first two execute SQL statements work fine. I think I've made a mistake in my variable/parameter mappings, but I'm not sure.</p>
  
  <p>My data flow looks like this: <img src="https://i.stack.imgur.com/kyrDF.png" alt="data flow"></p>
  
  <p>Basically what's going on is this:</p>
  
  <ol>
  <li><p>First Execute SQL Task creates the new table </p></li>
  <li><p>Second Execute SQL Task selects a table with full result set going into an object-type variable "AllocItems"</p></li>
  </ol>
  
  <p><img src="https://i.stack.imgur.com/M7KbU.png" alt="general"></p>
  
  <p><img src="https://i.stack.imgur.com/WRK4V.png" alt="result set"></p>
  
  <p><img src="https://i.stack.imgur.com/kAgGL.png" alt="variables pane"></p>
  
  <ol start="3">
  <li>Foreach Loop container (configured as an ADO enumerator) maps specific row from "AllocItems" onto variable "AllocItemsSQL1". These are the strings which should become field names in the table I'm creating</li>
  </ol>
  
  <p><img src="https://i.stack.imgur.com/zGzCO.png" alt="collection"></p>
  
  <p><img src="https://i.stack.imgur.com/8nBjO.png" alt="variable mappings"></p>
  
  <ol start="4">
  <li>Execute SQL Task within foreach loop container alters the table. The SQL query: <code>alter table MIT_Client_Profitability.dbo.cp_hh_footprint add ?  varchar(255)</code></li>
  </ol>
  
  <p><img src="https://i.stack.imgur.com/490Px.png" alt="parameter mapping"></p>
  
  <p>Things I've tried:   </p>
  
  <ul>
  <li>within the final execute sql task, adding parentheses around the parameter: "(?)" instead of "?"</li>
  <li>within final execute sql task, changing parameter name to "Param1"   </li>
  <li>within final execute sql task, changing parameter size    </li>
  <li>within second execute sql task, changing "result name"    </li>
  <li>within final execute sql task, changing query to "declare @SQL varchar(255) set @SQL = 'alter table MIT_Client_Profitability.dbo.cp_hh_footprint add ?  varchar(255)' exec(@SQL)"</li>
  </ul>
  
  <p>Thanks in advance for any suggestions!</p>