ssis sql
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.
T-SQL（Transact-SQL）是由Sybase和Microsoft开发的一组SQL语言编程扩展。 这些扩展已在Microsoft SQL Server中采用，它提供了一组强大的功能来执行分析和管理命令。 这在数据整理期间也非常有帮助。
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:
对于SSIS，通过SQL Server执行命令主要是通过“执行SQL任务”完成的，这是最流行的SSIS任务之一，它还用于在不同的引擎（例如SQLite，MySQL，Microsoft Access和Excel和具有受支持的连接管理器的任何源。 我们之前在本系列中已经发表了两篇文章，其中说明了如何使用和配置此任务：
- Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types 在SSIS中执行SQL任务：SqlStatementSource表达式与可变源类型
- Execute SQL Tasks in SSIS: Output Parameters vs Result Sets 在SSIS中执行SQL任务：输出参数与结果集
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.
在SSIS工具箱中查看时，您将看到另一个名为“执行T-SQL语句任务”的任务。 在本文中，我们将概述此任务，并将与比较流行的“执行SQL任务”进行一些比较。
执行T-SQL语句任务 (Execute T-SQL Statement Task)
Referring to the SSIS toolbox, this task is only used to “run Transact-SQL statements”.
参考SSIS工具箱，此任务仅用于“运行Transact-SQL语句”。
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.
该任务仅使用ADO.NET Connections，这需要安装.NET Framework，您可以简单地选择一个现有的连接管理器，也可以通过单击“新建…”按钮来添加一个新的连接管理器。
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 服务器名称 ：您应该在其中输入与之连接SQL Server实例的位置
- Authentication configuration:认证配置 ： where you should select whether you need to use Windows or SQL authentication (user and password should be provided) 您应在此处选择是否需要使用Windows或SQL身份验证（应提供用户名和密码）
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”.
从新的连接表单中，您可以看到它仅接受SQL Server连接（不是所有的ADO.NET连接），因为提到用户应该“指定以下内容以连接到SQL Server数据”。
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.
创建新的连接后，您将看到创建了一个新的连接管理器。 如果打开此连接管理器，将会看到它的类型是ADO.NET，并且使用“ .Net Providers / SqlClient Data Provider”。 现在，您可以在单击“所有”选项卡时检查其他连接设置。
执行超时 (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.
执行超时是指在超时（终止任务）之前等待语句完成的时间（以秒为单位）。 如果该值设置为零（0），则它将是无限的。 请注意，此属性不同于可从连接管理器配置的连接超时属性，因为第二个是指建立连接所需的时间。
T-SQL语句 (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.
在此字段中，您应该编写在连接的服务器上需要执行SQL语句。 您可以通过单击“查看T-SQL”按钮来检查将发送到SQL Server实例的命令。
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.
很多次有人问我是否需要此按钮，因为它显示了与主窗体中相同的命令。 答案是，此按钮位于SQL Server维护计划支持的所有任务中，例如“备份数据库任务”，“历史记录清理任务”，以及其他将在其中显示根据您所拥有的配置生成的语句的按钮。在任务内完成。
表达式和隐藏属性 (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.
如果关闭编辑器并单击控制流中的任务，然后按F4键显示“属性”选项卡，您将看到任务编辑器中有许多未显示的属性，例如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.
您可能会注意到，其中许多属性都与Execute SQL Task相关，这意味着这两个任务都是使用同一模板构建的。 但是这些属性可能无效，例如ResultSetType，因为您将无法选择存储结果集的变量。
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).
另一方面，可以使用SqlStatmentSourceType和SqlStatementSource属性来强制任务从变量或文件连接读取SQL语句。 请注意，您应该在语句字段（或SqlStatementSource属性）中写入变量名称或文件连接名称。
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.
另外，值得一提的是，可以使用与其他任务类似的表达式来使用SSIS表达式评估这些任务属性。 但是在官方文档中， 他们建议您在需要使用表达式时使用Execute SQL Task。
与执行SQL任务的比较 (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:
通常，如果需要将“执行T-SQL语句任务”与“执行SQL任务”进行比较，可以说后者更为通用，因为它使用户能够通过SQL Server和其他受支持的源执行语句，而前者是仅专用于SQL Server（ADO.net）连接。 此外，还有其他几点值得一提：
SQL代理维护计划 (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.
如果要构建要用作SQL Agent维护计划的SSIS包，则不能使用“执行SQL任务”，因为它不受支持。
结果集 (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.
如果需要从语句生成结果集并将其存储在SSIS变量中，则应使用“执行SQL任务”，因为其他任务不支持此功能。
参数化查询 (Parameterized queries)
If you need to use parameterized queries, then you should use Execute SQL Task.
如果需要使用参数化查询，则应使用“执行SQL任务”。
.NET Framework安装 (.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.
“执行T-SQL语句”任务仅使用ADO.NET连接，这意味着它要求在计算机上安装.NET框架。
在线资源 (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.
您可能会注意到，执行SQL任务更为流行，并且被开发人员广泛使用。 您会在网上找到数百个相关主题和教程。 尽管您可能找不到与Execute T-SQL语句有关的十篇文章。
语句解析时间 (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.
由于“执行SQL任务”支持多种SQL连接类型和语言，因此将需要更多时间来解析和验证语句和其他参数。 但是时间量几乎可以忽略不计。
项目连接问题 (Project connection issue)
In his personal blog, James Serra has mentioned the following issue related to project connection manager:
James Serra在他的个人博客中提到了与项目连接管理器有关的以下问题：
“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.”
“我在执行T-SQL语句任务中发现了一个问题：在连接管理器中创建ADO.NET项目连接时，它将自动创建一个链接到项目连接的程序包连接，并具有一个（（project） “ 字首。 在程序包连接的Qualifier属性中，您可以看到“ System.Data.SqlClient.SqlConnection，System.Data，Version = 4.0.0.0，Culture = neutral，PublicKeyToken = b77a5c561934e089”。 但是，使用Execute T-SQL Statement任务时，您不会在该任务的属性的“连接”下拉列表中看到该程序包连接。 要查看它，必须将该程序包连接中的Qualifier属性更改为“ SQL”。 使用执行SQL任务时，您不需要这样做。”
结论 (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.
在展示了这两个任务之间的主要区别之后，我们可以看到使用Execute SQL Task更为可取，因为它可以确保灵活性，并且根据我的个人经验，使用Execute T-SQL Statement任务的唯一位置是在SQL代理内维护计划。
目录 (Table of contents)
翻译自: https://www.sqlshack.com/ssis-execute-t-sql-statement-task-vs-execute-sql-task/
ssis sql
1643
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
