Dynamically decide the table name in execute SQL t

Question

I want to know if this is possible to do in SSIS Execute SQL task. I want to check for existence of a table, and if it doesn't exist, create the table. I want to keep the table name in a variable so I can execute the task against different databases and be able to change the table name.

As an example I have this code:

if not exists (select * from sysobjects where name='Table1' and xtype='U')
    Create Table Table1(
        Col11 varchar(10) primary key,
        Col12 varchar(10)
    );

For flexibility I want to store the value Table1 in a variable and keep changing it to create different tables each time I run the package.

Answer 1

Yes, This is possible. Here is how I would do it:

• Build another variable for your SqlStatement from your TableName variable. Depending on your version of SSIS this can bedone in two ways:

  In SSIS 2008 you need to use Script Task to do the string concatenations.

  In SSIS 2012 you can leverage the Expression Task.

• You can use the Exressions tab in Execute SQL Task and add an expression for SqlStatementSource and point it to your Variable. the variable should be the complete statement.