In SSIS there is two day to execute the T-sql.
- Execute SQL Task
- Execute T-SQL Statement Task
Execute T-SQL Statement Task
This task runs only T-sql
statement. It takes less memory, parse time and CPU time than the Execute SQL
task, but it is not flexible because we can neither parameterize the SQL nor
save the results to a variable. Both components are performing same
task. Now you are egger to know why SSIS is having two components to performing
same task. Both having its own advantage and disadvantages. Let us see.
If we want to
run parameterized queries, save the query results to variables, or
use property expressions, we should use the Execute SQL task instead of the
Execute T-SQL Statement task. Also, the Execute T-SQL Statement task
supports only the Transact-SQL version of the SQL language. In addition,
the Execute SQL task supports many connection types but the Execute T-SQL
Statement task supports only ADO.NET. So in the end, if you want a bit
more speed and don’t need the additional flexibility, use the Execute
T-SQL Statement task over the Execute SQL task.
Step by step to learn how to use Execute T-SQL Statement Task
Open the SSDT.
Double click on the task.
Create the new connection.
Click ok.
Now we need to write the T-SQL
statement. Generally we are using this task to insert some fix values in the
table. Or need to use truncate statement because we cannot use variable in this
task.
Click on ok.
Now package is ready to execute.
Package completed successfully.
Now I am seeing the result in SQL
Server.
