Execute T-SQL Statement Task in SSIS

In SSIS there is two way to execute the T-sql.

1. Execute SQL Task
2.  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.