How can I create a table with table name stored in a variable? - SSIS

Question

In SSIS Execute SQL Task, I have this code:

IF NOT EXISTS(
    SELECT * FROM sys.tables t INNER JOIN sys.schemas s on s.schema_id = 
        t.schema_id 
    WHERE t.name =  'tableName'  AND s.name = 'stg')
BEGIN
    CREATE TABLE [stg].[tableName] (
        Col1 nvarchar(255),
        Col2 nvarchar(1800)
    )
END

How can I make tableName a variable so that I can change the name easily?

See this answer on why it is a bad idea https://stackoverflow.com/questions/10877407/t-sql-how-to-create-tables-dynamically-in-stored-procedures/10877522#10877522 — Namphibian, Jul 16 '17 at 21:53
Is there no other way to easily create a table with same properties/columns as the first one, other than changing the SQL statement? — John Is No, Jul 16 '17 at 22:03
Why do you need the same structure copied over and over again? — Namphibian, Jul 16 '17 at 22:06
Because I will be inserting unique data that have similar datatypes for all columns in each table. Please help me. I have to create like 50 tables with same structures, and it would be tedious if I have to edit the SQL statement everytime. — John Is No, Jul 16 '17 at 22:15
why not add a column to one table called tableType or something — KeithL, Jul 17 '17 at 16:43

score 5 · Answer 1 · answered Jul 17 '17 at 07:20

Create a execute sql task then write this code in the SqlStatement with direct input type:

declare @sch nvarchar(10)   
        ,@tb nvarchar(50)
        ,@query nvarchar(1000)

Set @sch= ?
Set @tb= ?

IF NOT EXISTS(
    SELECT * FROM sys.tables t INNER JOIN sys.schemas s on s.schema_id = 
        t.schema_id 
    WHERE t.name =  @tb  AND s.name = @sch)
BEGIN
 set @query='CREATE TABLE '+@sch+'.'+@tb+' ( Col1 nvarchar(10),Col2 nvarchar(10) )'
 exec sp_executesql @query
END

Then map your variables in Parameter Mapping tab with Parameter Names 0 and 1 (in order of coming in the code)
Even you can create your columns dynamically with this solution.

score 0 · Answer 2 · answered Jul 16 '17 at 23:26

Here is what you can do to make it dynamic and flexible. Declare three variables -

sTableName,sSchemaName,sSQL_GenerateTable

Declare a string variable by the name sSQL_GenerateTable and give the following value as Expression -

"IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" +@[User::sTableName] + "' AND TABLE_SCHEMA = '" + @[User::sSchemaName]+ "') BEGIN 
CREATE TABLE [" + @[User::sSchemaName]+ "].[" +@[User::sTableName] +"] (
    Col1 NVARCHAR(255)
    ,Col2 NVARCHAR(1800)
)
END "

In the "Execute SQL Task", set the 'SQLSourceType' as 'Variable' and give the above variable, set 'ResultSet' as 'None'.

How can I create a table with table name stored in a variable? - SSIS

2 Answers2