Posted by gelangov on 08/22/07 12:22

On Aug 21, 5:24 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (gelan...@hotmail.com) writes:
> > I have 2 variables, one is the column name of the table and the other
> > one is the table name and I need to write this in the "Execute SQL
> > task" of a "For each loop" container in a ssis package like this:
>
> > Truncate table <tableName>
> > Insert into <tableName>
> > Exec (' select [' + ?+ '] from '+ ?)
>
> > It gives me error message when I try run the ssis. However if I put
> > the above statements into a stored procedure and wrote look this, it
> > works:
>
> > spStroedproc ?, ?
>
> > Do I need to change any of my settings for my "Exec" to work?
>
> I don't know SSIS per se, but you can never parameterise on a table
> name in SQL Server. You need to build the entire query string.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

Erland,

I think I did not write it clearly...May be I should have written it
like this:
Truncate table table1
Insert into table1
Exec (' select [' + ?+ '] from '+ ?)

The "?" are where the variables are. It works fine in the query
analyzer (of course, after changing the question mark to variable
names).

My question is:
Are there any settings I need to change in order for "Exec" to work in
SSIS?


Thank you,
Geetha

[Back to original message]