I have table example Month12_2010, month11_2010, month10_2010 ....etc
and i want using EXECUTE SQL TASK to insert data from table month to new table ALLMONTH
how to use SQL TASK to insert data
this is how my query looks like
insert into ALLMONTH FROM (Variable Month12_2010) as Month
without changing the query but just only change the variable?
First create another variable in SSIS which is a String Datatype and give it an appropriate name (I'm going to assume SQLStatement). Then, instead of setting it a value, set it's value to be an expression with the follow expression, obviously correcting the part in braces ({}) and changing the statement to be what you need:
"SELECT * FROM dbo." + @[User::{YourVariable}] + " AS MyTable;"
Then in your Execute SQL task, change the value of SQLSourceType to Variable and then the value of SourceVariable to User::SQLStatement. Then SSIS will run the statement in the variable instead.
An alternative (and safer) approach would be to use a "parametrised" statement instead; instead of injecting the value into a variable statement in SSIS. In the value of SQLStatement (the property of the Execute SQL Task, not the variable) enter the statement as:
DECLARE @TableName sysname = ?;
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT * FROM dbo.' + QUOTENAME(@TableName) + N' AS MyTable;';
EXEC sp_executesql @SQL;
Then, in the Parameter mapping Pane, pass your variable (for the dynamic table name) as a parameter to the statement.
