Not able to recognize a table variable in ssis sp

Question

I have created a variable in ssis called Vpop_summary_table.

I have used this variable in a sql statement.

my variable

Vpop_summary_table

is expected to give value

[dbo].[2019-02-02_pop_table]

It is fine in ssis.

DECLARE @Vpop_summary_table VARCHAR(100)


    SET  @Vpop_summary_table= ?
    SELECT 
          ,[Age Range]
          ,[Gender]
         ,[2023 Population]
     INTO [@Vpop_summary_table]
      FROM [dbo].[2018_Population_Table_CLARITAS]

My table is created as [@Vpop_summary_table] instead of [dbo].[2019-02-02_pop_table]

Object names (tables, columns, databases, functions, etc.) can't be referenced from variables. You will have to use Dynamic SQL for this. — EzLo, May 10 '19 at 09:13

score 2 · Answer 1 · answered May 10 '19 at 07:57

2

Table names and column names need to be static.

If you want to do this create the full SQL and then use sp_executesql to execute it although in terms of good practices having a table in a variable doesn't look so great...

answered May 10 '19 at 07:57

Carlos Alves Jorge

1,919
1
13
29

score 1 · Accepted Answer · answered May 11 '19 at 23:30

You cannot pass table name as parameter, you have to use expressions to achieve that:

Open Execute SQL Task editor, Go To Expressions Tab, add an expression for the SQLStatementSource property as following:

"SELECT 
       [Age Range]
      ,[Gender]
      ,[2023 Population]
 INTO " + @[User::Vpop_summary_table] + "
 FROM [dbo].[2018_Population_Table_CLARITAS]"

More info at:

Common Examples of SSIS Expressions and Variables (check Working with SSIS expressions section)

Not able to recognize a table variable in ssis sp

2 Answers2