Hadi Fadlallah
Advanced connection properties in the connection manager

SSIS: Execute T-SQL Statement Task vs Execute SQL Task

March 4, 2020 by

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:

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”.

Execute T-SQL Statement task description from SSIS toolbox

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:

Execute T-SQL statement task editor form

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:

Add new connection dialog

  • 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.

Advanced connection properties in the connection manager

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.

Showing how "View T-SQL" button acts.

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.

Task's hidden properties

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

SSIS OLE DB Source: SQL Command vs Table or View
SSIS Expression Tasks vs Evaluating variables as expressions
SSIS OLE DB Destination vs SQL Server Destination
Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types
Execute SQL Task in SSIS: Output Parameters vs Result Sets
SSIS Derived Columns with Multiple Expressions vs Multiple Transformations
SSIS Data types: Change from the Advanced Editor vs Data Conversion Transformations
SSIS Connection Managers: OLE DB vs ODBC vs ADO.NET
SSIS Flat Files vs Raw Files
SSIS Foreach Loop vs For Loop Container
SSIS: Execute T-SQL Statement Task vs Execute SQL Task
SSIS Lookup transformation vs. Fuzzy Lookup transformation
SSIS Pivot transformations vs. Unpivot transformations
SSIS Merge Join vs. Merge Transformation
Data Access Modes in SSIS OLE DB Destination: SQL Command vs. Table or View
SSIS XML Source vs XML task
SSIS Script task vs. Script Component
SSIS term extraction vs. term lookup
Hadi Fadlallah
ETL, Integration Services (SSIS)

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. 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

168 Views