Using foreach loop container to create new columns in a table- can't solve "incorrect syntax near '@P1'" error

Question

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:

alter table /databasename/.dbo.cp_hh_foo..." failed with the following error: "Incorrect syntax near '@P1'.

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.

My data flow looks like this:

Basically what's going on is this:

1. First Execute SQL Task creates the new table

2. Second Execute SQL Task selects a table with full result set going into an object-type variable "AllocItems"

3. 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

4. Execute SQL Task within foreach loop container alters the table. The SQL query: alter table MIT_Client_Profitability.dbo.cp_hh_footprint add ? varchar(255)

Things I've tried:

• within the final execute sql task, adding parentheses around the parameter: "(?)" instead of "?"
• within final execute sql task, changing parameter name to "Param1"
• within final execute sql task, changing parameter size
• within second execute sql task, changing "result name"
• 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)"

Thanks in advance for any suggestions!

Answer 1

To build on David's answer

Create a new SSIS variable, @[User::Sql] of type String and in the Expression box, add the following syntax

"alter table MIT_Client_Profitability.dbo.cp_hh_footprint add " + @[User::AllocItemSQL1] + " varchar(255);"

The nice thing about this approach is that you can put a breakpoint on the Execute SQL Task and see what the statement looks like prior to the task attempting to execute it. And then modify the Execute SQL Task to use the new variable and remove the parameter.

Otherwise, the dynamic tsql approach ought to have worked, just you needed to modify the syntax. The token replacement won't work inside the string. Something more like this should work according to my mental model

declare @newcolumn varchar(255) = ?;
declare @SQL varchar(255) = 'alter table MIT_Client_Profitability.dbo.cp_hh_footprint add ' + @newcolumn + '  varchar(255)'; 
exec(@SQL);

Answer 2

This

alter table MIT_Client_Profitability.dbo.cp_hh_footprint add ? varchar(255)

is a Data Definition Language (DDL) statement. DDL cannot be paramterized. You'll have to create the statement with string concatenation.