执行 SQL 任务

项目
12/15/2008

更新日期： 2007 年 9 月 15 日

执行 SQL 任务从包中运行 SQL 语句或存储过程。此任务可以包含单个 SQL 语句，也可以包含按顺序运行的多个 SQL 语句。可以将执行 SQL 任务用于下列用途：

截断表或视图，以便为插入数据作准备。
创建、更改和删除数据库对象（如表和视图）。
在向事实数据表和维度表加载数据之前，重新创建这些表。
运行存储过程。
将查询返回的行集保存到变量中。

可以按照下列方式配置执行 SQL 任务：

指定用于连接到数据库的连接管理器的类型。
指定 SQL 语句返回的结果集的类型。
指定 SQL 语句的超时值。
指定 SQL 语句的源。
指明任务是否跳过 SQL 语句的准备阶段。
如果使用 ADO 连接类型，则必须指明 SQL 语句是否为存储过程。对于其他的连接类型，该属性为只读且其值始终为 false。

执行 SQL 任务可以与 Foreach 循环和 For 循环容器一起组合使用，以运行多个 SQL 语句。这些容器在包中实现重复运行控制流，并可重复运行执行 SQL 任务。例如，包可以使用 Foreach 循环容器来枚举文件夹中的文件，并重复运行执行 SQL 任务来执行存储在各个文件中的 SQL 语句。

连接数据源

执行 SQL 任务可使用不同类型的连接管理器来连接到在其中运行 SQL 语句或存储过程的数据源。此任务可使用下表中列出的连接类型。

连接类型	连接管理器
EXCEL	Excel 连接管理器
OLE DB	OLE DB 连接管理器
ODBC	ODBC 连接管理器
ADO	ADO 连接管理器
ADO.NET	ADO.NET 连接管理器
SQLMOBILE	SQL Server Compact Edition 连接管理器

创建 SQL 语句

此任务使用的 SQL 语句的源可以是包含语句的任务属性、到包含一个或多个语句的文件的连接，或者是包含语句的变量的名称。必须用源数据库管理系统 (DBMS) 的方言编写 SQL 语句。

如果 SQL 语句存储在某个文件中，则该任务使用文件连接管理器来连接到该文件。有关详细信息，请参阅文件连接管理器。

在 SSIS 设计器中，可以使用**“执行 SQL 任务编辑器”对话框来键入 SQL 语句，也可使用“查询生成器”**（用于创建 SQL 查询的图形用户界面）键入。

注意：
执行 SQL 任务可能无法成功分析在执行 SQL 任务外编写的有效 SQL 语句。

按批发送多个语句

如果在执行 SQL 任务中包含了多个语句，则可以将这些语句进行分组，并将它们作为一批来运行。若要标明批的结束，请使用 GO 命令。在两个 GO 命令间的所有 SQL 语句都作为一批发送到 OLE DB 访问接口来运行。 SQL 命令可以包含多个由 GO 命令分隔的批。

对可以分组到批的 SQL 语句类型有一些限制。有关详细信息，请参阅 Batches of Statements。

如果执行 SQL 任务运行一个 SQL 语句批，则下列规则适用于批：

只有一个语句可以返回结果集，且该语句必须是批中的第一个语句。
如果结果集使用结果绑定，则查询必须返回相同数量的列。如果查询返回不同数量的列，则任务失败。但是，即使任务失败，其运行的查询（如 DELETE 或 INSERT 查询）也可能成功。
如果结果绑定使用列名，则查询必须返回与任务中使用的结果集具有相同名称的列。如果缺少列，则任务失败。
如果任务使用参数绑定，则批中的所有查询都必须具有相同数量和类型的参数。

运行参数化 SQL 命令

SQL 语句和存储过程常常使用输入参数、输出参数和返回代码。执行 SQL 任务支持 Input、Output 和 ReturnValue 参数类型。应当将 Input 类型用于输入参数，将 Output 用于输出参数并将 ReturnValue 用于返回代码。

注意：
只有数据访问接口支持这些参数时，才可在执行 SQL 任务中使用它们。

SQL 命令（包括查询和存储过程）中的参数被映射到在执行 SQL 任务作用域、父容器或包的作用域内创建的用户定义变量。变量的值可在设计时设置，也可在运行时动态填充。还可以将参数映射到系统变量。有关详细信息，请参阅 Integration Services 变量和系统变量。

取决于执行 SQL 任务所使用的连接类型，SQL 命令的语法使用不同的参数标记。例如，ADO.NET 连接管理器类型要求 SQL 命令使用格式为 @varParameter 的参数标记，而 OLE DB 连接类型要求使用问号 (?) 参数标记。

在变量与参数之间的映射中可以用作参数名的名称也根据连接管理器类型而各不相同。例如，ADO.NET 连接管理器类型使用带 @ 前缀的用户定义名称，而 OLE DB 连接管理器类型要求使用基于 0 的序数数值作为参数名称。

下表总结了执行 SQL 任务可以使用的连接管理器类型的 SQL 命令要求。

连接类型	参数标记	参数名称	示例 SQL 命令
ADO	?	Param1, Param2, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
ADO.NET	@<参数名称>	@<参数名称>	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID
ODBC	?	1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
EXCEL 和 OLE DB	?	0, 1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

ADO.NET 连接管理器的参数

ADO.NET 连接管理器要求 SQL 命令使用参数名称作为参数标记。这意味着变量可以直接映射到参数。例如，变量 @varName 映射到名为 @parName 的参数，并向参数 @parName 提供值。

EXCEL、ODBC 和 OLE DB 连接管理器的参数

EXCEL、ODBC 和 OLE DB 连接管理器要求 SQL 命令使用问号 (?) 作为参数标记，并使用基于 0 或 1 的数值作为参数名称。如果执行 SQL 任务使用 ODBC 连接管理器，则映射到查询中第一个参数的参数名被命名为 1；否则，该参数被命名为 0。对于随后的参数，参数名的数值指示了该参数名所映射到的 SQL 命令中的参数。例如，名为 3 的参数映射到第三个参数，这是由 SQL 命令中的第三个问号 (?) 来表示的。

若要向参数提供值，可以将变量映射到参数名称，然后执行 SQL 任务使用参数名称的序数值将值从变量加载到参数。

如果执行任务使用 OLE DB 连接类型，则该任务的 BypassPrepare 属性可用。如果执行 SQL 任务使用带有参数的 SQL 语句，则应将此属性设置为 true。

使用 OLE DB 连接管理器时，不能使用参数化的子查询，因为执行 SQL 任务无法通过 OLE DB 访问接口派生参数信息。但是，可以使用表达式连接参数值与查询字符串并设置任务的 SqlStatementSource 属性。

取决于连接管理器所使用的访问接口，某些 OLE DB 数据类型可能不支持。例如，Excel 驱动程序只识别有限的一组数据类型。有关带有 Excel 驱动程序的 Jet 访问接口的行为的详细信息，请参阅 Excel 源。

ADO 连接管理器的参数

ADO 连接管理器要求 SQL 命令使用问号 (?) 作为参数标记，但除了整数值以外，也可以使用任何用户定义的名称作为参数名称。

若要向参数提供值，需要将变量映射到参数名称，然后执行 SQL 任务使用参数列表中的参数名的序数值将值从变量加载到参数。

与 WHERE 子句一起使用参数

SELECT、INSERT、UPDATE 和 DELETE 命令经常包括 WHERE 子句以指定筛选器，这些筛选器定义源表中的每行必须满足后才能用于 SQL 命令的条件。参数在 WHERE 子句中提供筛选值。

可以使用参数标记来动态提供参数值。可以在 SQL 语句中使用哪些参数标记和参数名称的规则取决于执行 SQL 所使用的连接管理器的类型。

下表按不同连接管理器类型列出了 SELECT 命令的示例。 INSERT、UPDATE 和 DELETE 语句相似。示例使用 SELECT 从 AdventureWorks 中的 Product 表返回其 ProductID 大于和小于由两个参数指定的值的产品。

连接类型	SELECT 语法
EXCEL、ODBC 和 OLEDB	`SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?`
ADO	`SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?`
ADO.NET	`SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID`

这些示例要求在参数中使用以下名称：

EXCEL 和 OLED DB 连接管理器使用参数名称 0 和 1。ODBC 连接类型使用 1 和 2。
ADO 连接类型可以使用任何两个参数名称，例如 Param1 和 Param2，但是这两个参数必须按其在参数列表中的序数位置进行映射。
ADO.NET 连接类型使用参数名称 @parmMinProductID 和 @parmMaxProductID。

在存储过程中使用参数

运行存储过程的 SQL 命令也可以使用参数映射。与参数化查询的规则一样，参数标记和参数名称的使用规则取决于执行 SQL 所使用的连接管理器的类型。

下表按不同的连接管理器类型列出了 EXEC 命令的示例。示例运行 AdventureWorks 中的 uspGetBillOfMaterials 存储过程。存储过程使用 @StartProductID 和 @CheckDate 输入参数。

连接类型	EXEC 语法
EXCEL 和 OLEDB	`EXEC uspGetBillOfMaterials ?, ?`
ODBC	`{call uspGetBillOfMaterials(?, ?)}` 有关 ODBC 调用语法的详细信息，请参阅 MSDN Library 中 ODBC Programmer's Reference（《ODBC 程序员参考》）中的主题过程参数。
ADO	If IsQueryStoredProcedure is set to False, `EXEC uspGetBillOfMaterials ?, ?` If IsQueryStoredProcedure is set to True, `uspGetBillOfMaterials`
ADO.NET	If IsQueryStoredProcedure is set to False, `EXEC uspGetBillOfMaterials @StartProductID, @CheckDate` If IsQueryStoredProcedure is set to True, `uspGetBillOfMaterials`

若要使用输出参数，则语法要求在每个参数标记后跟 OUTPUT 关键字。例如，EXEC myStoredProcedure ? OUTPUT。

有关在 Transact-SQL 存储过程中使用输入和输出参数的详细信息，请参阅参数（数据库引擎）、使用 OUTPUT 参数返回数据和 EXECUTE (Transact-SQL)。

获得返回代码的值

存储过程可以返回整数值（称为“返回代码”），指示过程的执行状态。若要在执行 SQL 任务中实现返回代码，需要使用 ReturnValue 类型的参数。

下表按连接类型列出了实现返回代码的某些 EXEC 命令示例。所有示例均使用输入参数。 ReturnValue 参数的参数标记和参数名称的使用规则与应用于 Input 和 Output 参数类型的规则相同。

某些语法不支持参数文字。在此情况下，必须通过使用变量来提供参数值。

连接类型	EXEC 语法
EXCEL 和 OLEDB	`EXEC ? = myStoredProcedure 1`
ODBC	`{? = call myStoredProcedure(1)}` 有关 ODBC 调用语法的详细信息，请参阅 MSDN Library 中 ODBC Programmer's Reference（《ODBC 程序员参考》）中的主题过程参数。
ADO	If IsQueryStoreProcedure is set to False, `EXEC ? = myStoredProcedure 1` If IsQueryStoreProcedure is set to True, `myStoredProcedure`
ADO.NET	将 IsQueryStoreProcedure 设置为 True。 `myStoredProcedure`

有关在 Transact-SQL 存储过程中使用返回代码的详细信息，请参阅使用返回代码返回数据和 RETURN (Transact-SQL)。

指定结果集类型

执行 SQL 任务可能有结果集返回也可能没有结果集返回，这取决于 SQL 命令的类型。例如，SELECT 语句通常返回结果集，而 INSERT 语句通常不返回结果集。 SELECT 语句所返回的结果集可包含零行、单行或多行。存储过程还可返回指示过程的执行状态的整数值（称为返回代码）。这种情况下，结果集由单行组成。

执行 SQL 任务支持下列结果集类型：

查询不返回结果时使用的**“无”**结果集。例如，该结果集用于在表中添加、更改和删除记录的查询。
查询仅返回一行时使用的**“单行”**结果集。例如，该结果集用于存储过程（返回一个返回代码）或者 SELECT 语句（返回计数或总和）。
查询返回多行时使用的**“完整结果集”**结果集。例如，该结果集用于可检索表中所有行的 SELECT 语句。
查询返回 XML 格式的结果集时使用的**“XML”**结果集。例如，该结果集用于包含 FOR XML 子句的 SELECT 语句。

如果“执行 SQL 任务”使用了**“完整结果集”**结果集并且查询返回多个行集，则该任务仅返回第一个行集。如果该行集生成一个错误，则该任务将报告这个错误。如果其他行集生成错误，则该任务不会报告这些错误。

“执行 SQL 任务”可将 SQL 语句返回的任何非字符串值转换为字符串。例如，将具有 SQL Server uniqueidentifier、bigint、decimal 或 numeric 数据类型的值转换为字符串。

使用结果集填充变量

如果结果集类型为单行、行集或 XML，则可以将查询返回的结果集绑定到用户定义的变量。

如果结果集类型为“单行”，则可以使用列名作为结果集名称，将返回结果中的列绑定到一个变量，也可以使用列列表中列的序号位置作为结果集名称。例如，查询 SELECT Color FROM Production.Product WHERE ProductID = ? 的结果集名称可以是 Color 或 0。如果查询返回多个列，而您要访问所有列中的值，则必须将每列绑定到一个不同的变量。如果使用数字作为结果集名称，将列映射到变量，则数字将反映列在查询的列列表中显示的顺序。例如，在查询 SELECT Color, ListPrice, FROM Production.Product WHERE ProductID = ? 中，对 Color 列使用 0，对 ListPrice 列使用 1。使用列名作为结果集名称的功能将依赖于所配置任务要使用的访问接口。并非所有访问接口都使列名可用。

某些返回单个值的查询可能不包括列名称。例如，语句 SELECT COUNT (*) FROM Production.Product 不返回列名称。可以使用序数位置 0 作为结果名称来访问返回结果。若要按列名称访问返回结果，则查询必须包括 AS <别名> 子句来提供列名称。语句 SELECT COUNT (*)AS CountOfProduct FROM Production.Product 提供 CountOfProduct 列。然后可以使用 CountOfProduct 列名称或序数位置 0 来访问返回结果列。

如果结果集类型为**“完整结果集”或“XML”**，则必须使用 0 作为结果集名称。

当您将一个变量映射到结果集类型为“单行”的结果集时，该变量的数据类型必须与该结果集包含的列的数据类型兼容。例如，如果结果集包含 String 数据类型的列，则它不能映射到 numeric 数据类型的变量。 XML 结果集只能映射到数据类型为 String 或 Object 的变量。如果该变量的数据类型为 String，则“执行 SQL 任务”返回一个字符串，并且 XML 源可以使用 XML 数据。如果该变量的数据类型为 Object，则“执行 SQL 任务”返回一个文档对象模型 (DOM) 对象。 完整结果集必须映射到数据类型为 Object 的变量。返回结果是一个行集对象。您可以编写自定义任务来定位行集对象，并访问有关行集中的列和数据的信息。

下表总结了可以映射到结果集的变量数据类型。

结果集类型	变量的数据类型	对象的类型
单行	与结果集中的类型列兼容的任意类型。	不适用
完整结果集	Object	如果任务使用本机连接管理器（包括 ADO、OLE DB、Excel 和 ODBC 连接管理器），则返回的对象为 ADO Recordset。如果任务使用了托管连接管理器（如 ADO.NET 连接管理器），则返回对象为 System.Data.DataSet。
XML	String	String
XML	Object	如果任务使用本机连接管理器（包括 ADO、OLE DB、Excel 和 ODBC 连接管理器），则返回的对象为 MSXML6.IXMLDOMDocument。如果任务使用了托管连接管理器（如 ADO.NET 连接管理器），则返回对象为 System.Xml.XmlDocument。

您可在执行 SQL 任务作用域或包作用域内定义变量。如果变量的作用域为包，则结果集可用于包中的其他任务和容器，并可用于执行包或执行 DTS 2000 包任务所运行的所有包。

将变量映射到“单行”结果集时，可能会将由 SQL 语句返回的非字符串值转换为字符串。是否发生此转换以及此转换是隐式的还是显式的取决于使用的连接管理器的类型：

使用 ADO.NET 连接管理器时，不进行转换。
使用 ADO 或 ODBC 连接管理器时，将进行隐式转换。
使用 OLE DB 或 Excel 连接管理器时，连接管理器会将以下类型的值显式转换为字符串：DBTYPE_I8、DBTYPE_UI8、DBTYPE_NUMERIC、DBTYPE_GUID 和 DBTYPE_BYTES。

有关如何将结果集加载到变量中的信息，请参阅如何在执行 SQL 任务中将结果集映射到变量。

对执行 SQL 任务可用的自定义日志项

下表介绍了执行 SQL 任务的自定义日志项。有关详细信息，请参阅在包中实现日志记录和日志记录的自定义消息。

日志项	说明
ExecuteSQLExecutingQuery	提供有关 SQL 语句的执行阶段的信息。在任务获得与数据库的连接时、任务开始准备 SQL 语句时以及执行完 SQL 语句之后写入日志项。准备阶段的日志项包括任务所使用的 SQL 语句。