开篇介绍
在 BI 项目中，有一个非常常用的控制流控件 - Execute SQL Task，在数据从数据源加载到目标表之前它经常会被用来执行一些清空表的操作。除此之外，它的在项目中的常用的情形还包括：
- 执行插入，更新，Merge 等数据操作，包括可能的删除一些 Working table 和创建 Working table 等操作。
- SSIS 日志中调用存储过程，或者直接插入包日志等。
- 保留 SQL 查询的结果或者存储过程的调用结果值到变量中，供包中其它 Task 使用。
- 配合 Foreach Loop 或者 For Loop 在循环中执行增，删，改，查等操作。
通常情况下，Connection Type 我们选择的都是 OLE DB 连接方式，少数情况下使用过 ADO.NET，其它的连接方式很少或者基本上没有使用过。
要执行的 SQL 语句写在 SQL Statement 中，比如执行存储过程，查询，删除，修改等都可以写在里面。并且也支持批处理，只需要在结束的位置添加 GO 命令就可以了。所以在 GO 关键字之间的 SQL 会一次性发出并执行。
简单的 SQL 插入语句操作
在本文的例子中，首先演示一条插入语句的操作，目标表是一个包的Log日志表。
IF OBJECT_ID('T013_PROCESS_LOG') IS NOT NULL
DROP TABLE T013_PROCESS_LOG
GO
CREATE TABLE T013_PROCESS_LOG
(
ID INT PRIMARY KEY IDENTITY(1,1),
EXECUTION_ID NVARCHAR(255),
PACKAGE_NAME NVARCHAR(255),
MACHINE_NAME NVARCHAR(255),
START_TIME DATETIME NULL,
FINISH_TIME DATETIME NULL,
EXECUTE_STATUS_ID INT
)
SELECT * FROM T013_PROCESS_LOG SQL Statement 中的语句：
INSERT INTO T013_PROCESS_LOG VALUES('1001','013_EXECUTE_SQL_TASK','LOCALHOST',GETDATE(),NULL,1)执行包之后查询数据库中的记录，插入操作成功。
带参数的 SQL 插入操作
很显然在这张表中的数据有很多内容是不应该 Hard-code 写死的，因此下面演示参数的传递操作。
INSERT INTO T013_PROCESS_LOG VALUES(?,?,?,GETDATE(),NULL,1)这条语句中我们看到有三个 ？ 分别表示了 EXECUTION_ID, PACKAGE_NAME 和 MACHINE_NAME，这几个值在包执行的时候就已经存在了，我们可以在 SSIS 中把他们引用出来。
保存并切换到 Parameter Mapping 位置，可以选择系统变量。
这三个参数与系统自带的变量进行绑定，注意选择好参数方向，数据类型和参数名称。
- System::ExecutionInstanceGUID - 包每次执行一次就会给这个变量赋值一次，一个独一无二的唯一标识。
- System::PackageName - 包的名称。
- System::MachineName - 机器名称。
参数名称 Parameter Name 中的 0,1,2 表示的是 SQL 语句中 ? 的位置，这个位置不能弄错了。
配置完成后，执行包两次，能看到两个不同的 Execution ID，这种方式在自定义包的Log日志处理中非常有用。
Execute SQL Task 中处理带参数和输出的存储过程
有很多情况下，为了因修改业务逻辑而影响到包，或者当 SQL 内容过多过复杂的时候，我们往往选择将 SQL 语句封装成一个存储过程。这样逻辑的修改不会影响包，也就是说在不需要打开包的情况下就把业务逻辑给修改好了。
以下存储过程插入一条记录，并将自增的 ID标识 返回。
IF OBJECT_ID('USP_T013_INSERT_PROCESS_LOG') IS NOT NULL
DROP PROCEDURE USP_T013_INSERT_PROCESS_LOG
GO
CREATE PROCEDURE USP_T013_INSERT_PROCESS_LOG
@EXECUTION_ID NVARCHAR(255),
@PACKAGE_NAME NVARCHAR(255),
@MACHINE_NAME NVARCHAR(255),
@ID INT OUTPUT
AS
BEGIN
INSERT INTO T013_PROCESS_LOG VALUES(@EXECUTION_ID,@PACKAGE_NAME,@MACHINE_NAME,GETDATE(),NULL,1)
SELECT @ID = @@IDENTITY
END
GOEXECUTE SQL TASK 中 SQL 语句。
EXECUTE USP_T013_INSERT_PROCESS_LOG
@EXECUTION_ID = ?,
@PACKAGE_NAME = ?,
@MACHINE_NAME = ?,
@ID = ? OUTPUT其它位置保持不变，对于存储过程的输出值需要有一个变量来接受保存，点击 New Variable 创建变量，或者应该事先创建好。
创建一个 Package 级别的变量，注意命名规范 - PV 表示 Package Variable。
添加 Mapping ，注意这里选择的 Output 类型是 Long 表示 Int 类型数据，3是？的位置。
添加一个 Script Task 来显示一下新插入产生的 Process Log ID。
选择用户自定义的变量。
Script 中的代码显示一下这个新的自增长的 ID 值。
{
// TODO: Add your code here
MessageBox.Show(Dts.Variables["User::PV_PROCESS_LOG_ID"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}保存并执行，执行的过程中可以看到新的LOG ID。
查看数据库可以看到对应的 ID 值。
带有 ReturnValue 的操作
关于 ReturnValue 和 Output 的概念以及区别大家可以参考 SQL Server 的官方文档，下面修改这个存储过程，添加一个返回值，表示存储过程执行成功。
IF OBJECT_ID('USP_T013_INSERT_PROCESS_LOG') IS NOT NULL
DROP PROCEDURE USP_T013_INSERT_PROCESS_LOG
GO
CREATE PROCEDURE USP_T013_INSERT_PROCESS_LOG
@EXECUTION_ID NVARCHAR(255),
@PACKAGE_NAME NVARCHAR(255),
@MACHINE_NAME NVARCHAR(255),
@ID INT OUTPUT
AS
BEGIN
INSERT INTO T013_PROCESS_LOG VALUES(@EXECUTION_ID,@PACKAGE_NAME,@MACHINE_NAME,GETDATE(),NULL,1)
SELECT @ID = @@IDENTITY
RETURN 1
END
GO修改 Execute SQL Task 中的 SQL 语句并修改 Parameter Mapping ，自行添加一个用户变量 - PV_USP_STATUS。
修改 Script Task 显示这个变量的结果。
public void Main()
{
// TODO: Add your code here
MessageBox.Show(Dts.Variables["User::PV_PROCESS_LOG_ID"].Value.ToString());
MessageBox.Show(Dts.Variables["User::PV_USP_STATUS"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
保存并执行包，第一次弹出的是自增长的 PROCESS LOG ID。
第二次弹出是描述存储过程的执行状态编码 1。
