T-SQL (Transact-SQL) is a set of SQL language programming extensions developed by Sybase and Microsoft. These extensions are adopted in Microsoft SQL Server and it provides a powerful set of functions to execute analytics and administrative commands; also it is very helpful during data wrangling.
When it comes to SSIS, executing commands over SQL Server is mainly done using “Execute SQL Task” which is one of the most popular SSIS task, it is also used to run SQL commands over different engines such as SQLite, MySQL, Microsoft Access and Excel and any source that has a supported connection manager. We have previously published two articles in this series where we illustrated how to use and configure this task:
- Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types
- Execute SQL Tasks in SSIS: Output Parameters vs Result Sets
While looking in the SSIS toolbox, you will see that there is another similar task called “Execute T-SQL Statement Task”. In this article, we will give an overview of this Task, and we will make a small comparison with the Execute SQL Task which is more popular.
Execute T-SQL Statement Task
Referring to the SSIS toolbox, this task is only used to “run Transact-SQL statements”.
After adding this task to your control flow and opening the task editor, you will see that it is very simple. It contains the following components:
Connection
This task uses only ADO.NET Connections, which requires that .NET Framework is installed, you can simply select an existing connection manager or you can add a new one by clicking on the “New…” button.
When attempting to add a new connection, this task opens a simplified window that only contains the main connection information:
- Connection name: which is the connection manager name
- Server name: where you should enter the SQL Server instance that you need to connect with
- Authentication configuration: where you should select whether you need to use Windows or SQL authentication (user and password should be provided)
From the new connection form, you can see that it only accepts SQL Server connections (not all ADO.NET connections) since it is mentioned that the user should “specify the following to connect to SQL Server data”.
After creating a new connection, you will see that a new connection manager is created. If you open this connection manager, you will see that it is of type ADO.NET and it uses “.Net Providers/SqlClient Data Provider”. Now, you can check other connection settings when you click on the “All” tab.
Execution time out
The execution time out refers to the time (in seconds) to wait for statement completion before timing out (terminating task). If the value is set to zero (0), then it will be infinite. Note that this property differs from the connection time out property that can be configured from the connection manager since the second refers to the time needed to establish the connection.
T-SQL statement
In this field, you should write the SQL statement you need to execute on the connected server. You can check the command that will be sent to the SQL Server instance by clicking on the “View T-SQL” button.
Many times I was asked about the need for this button since it shows the same command written in the main form. The answer is that this button is found within all the tasks that are supported in the SQL Server maintenance plans such as “Back up database task”, “History cleanup task” and others where it will show the statement generated based on the configuration you have made within the task.
Expressions and hidden properties
If you close the editor and click on the task within the control flow then press F4 to show the properties tab, you will see that there are many properties that are not shown within the task editor such as CodePage, ResultSetType, SqlStatementSource, SqlStatmentSourceType.
You may note that many of these properties are related to the Execute SQL Task, which means that both tasks are built using the same template. But these properties may be invalid, such as ResultSetType, since you will not be able to select the variable where the result set is stored.
On the other hand, you can use SqlStatmentSourceType and SqlStatementSource properties to force the task to read the SQL statement from a variable or a file connection. Note that you should write the variable name or file connection name in the statement field (or SqlStatementSource property).
Also, it is worth mentioning that you can use Expressions similar to other tasks in order to evaluate these task properties using SSIS expressions. But in the official documentation, they recommended using the Execute SQL Task when you need to use expressions.
Comparison with Execute SQL Task
In general, if we need to compare the Execute T-SQL Statement Task with Execute SQL Task, we can say that the latter is more generic since it gives the user the ability to execute statement over SQL Server and other supported sources while the former is only dedicated to SQL Server (ADO.net) connections. In addition, there are other points worth to mention:
SQL agent maintenance plans
If you are building SSIS packages to be used as SQL Agent maintenance plans, you cannot use the Execute SQL Task since it is not supported.
Result Sets
If you need to generate result sets from your statement and to store them within SSIS variables, you should use Execute SQL Task since this feature is not supported within the other task.
Parameterized queries
If you need to use parameterized queries, then you should use Execute SQL Task.
.NET framework installation
The Execute T-SQL Statement task uses ADO.NET connections only, which means that it requires that the .NET framework should be installed on the machine.
Online resources
You may note that Execute SQL Task is more popular and it is widely used by developers; you will find hundreds of related topics, tutorials online. While you may not find ten articles related to the Execute T-SQL Statement.
Statement Parse Time
Since the Execute SQL Task supports multiple SQL connection types and languages, it will require additional time to parse and validate the statement and other parameters. But the amount of time is mostly negligible.
Project connection issue
In his personal blog, James Serra has mentioned the following issue related to project connection manager:
“One problem I found with the Execute T-SQL Statement task: When you create an ADO.NET project connection in the Connection Manager, it will automatically create a package connection that is linked to the project connection and will have a “(project)” prefix. In the Qualifier property for the package connection, you see “System.Data.SqlClient.SqlConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”. However, when using an Execute T-SQL Statement task you won’t see that package connection listed in the “Connection” drop-down on the task’s properties. To see it, you must change the Qualifier property in that package connection to “SQL”. You do not need to do this when using the Execute SQL task.”
Conclusion
After showing the main difference between these two tasks, we can see that it is more preferable to use Execute SQL Task since it guarantees flexibility and based on my personal experience, the only place to use the Execute T-SQL Statement task is within SQL agent maintenance plans.
Table of contents
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023