(Well first....the company Konesans has something similar prebuilt in the form of a custom task. You might want to have a look at that)

Second....your most recent example is missing a declare statemnt....hence the error.

Then.....even if you had a declare statement.....in sql you can't say

insert into tablename

(@list_cols)

values('a','b') etc.

The above wouldn't work. Another similar example,

in sql you can't do :

select * from tablename where emp_name in (@list_names). This wouldn't work.

The correct way to do this is by using dynamic sql. ie build your query as a string in sql and then use exec()

or maybe execute_sql(please look up the help on this).

so basically, in your example, you can do :

declare @cols varchar(max)

declar @sql varchar(max)

declare @tblname varchar(50)

declare @insert_vals varchar(max)

set @cols = 'fname, lname, age, sex, empid)' etc

set @insert_vals = 'john, doe, 30,115)' etc

set @sql = 'insert into + @tblname + '('

set @sql = @sql + @cols + ')'

set @sql = @sql + ' values(' + @insert_vals + ')'

Now, your @sql contains the whole sql. Now you just say :

exec(@sql) ---> This will execute your sql string like a sql statement.

ok, so with that sorted out, your next problem at hand :

dynamically inserting values into table (ie table being decide at runtime) :

Please rethink about questions like :

are the cols in src the same as the corresponding dest table in each case

It looks as if the tbl name are also the same....if not then its not a simple insert stmnt....you would be applying some logic.... like for dest tbl1, src colA goes to srccolb etc

is it a one to one mapping / transform....no derived col, no logic etc.

What about the datatypes on each side(do they exactly match etc)

I would attempt this in a a script task like so....

Get the src cols dynamically and put that in a variable

Get the src values dynamically and put that in a variable

Get the dest tblname in a var

Get the dest cols in a var

Form the sql as a var(as shown above) (keep in mind the datatypes, lengths, conversions, null value etc....not an easy task)

EXEC the sql.

Run this whole thing in a foreach loop (either ssis foreach loop or a c# foreach loop inside the script task)

I am hoping this would give you some clearer picture of the solution.