从SQL Server中的存储过程执行SSIS包

问题

我们的组织一直在使用 SSIS包来执行与后端数据库直接相关的各种操作。我们希望能够直接从我们的应用程序中利用这些功能。通常，我们构建SSIS程序包，将它们部署到 SSIS目录中，然后根据计划从SQL Server代理作业中执行程序包。尽管这很好用，但我们希望能够从应用程序执行SSIS包并在运行时指定包参数的值。

例如，我们提供了一个基于云的CRM应用程序。我们希望推出一个完全自助服务的新客户入职流程。当客户注册我们的服务时，他们需要从现有系统中获取数据并加载到我们的应用程序中。我们将提供一个Web界面，客户可以在其中通过简单的CSV文件加载其数据。Web应用程序将允许客户导航到CSV文件并上传文件。上载会将文件放在我们服务器上的文件夹中，并且Web应用程序将执行SSIS包以执行加载。

我们怎样才能做到这一点？

解

SQL Server集成服务（SSIS）提供了SSISDB数据库，该数据库包含存储过程和视图，使您可以使用SSIS项目和部署到Integration Services Catalog的程序包。我将采用的实现目标的方法是创建SQL对象，这些对象可以使用存储过程从目录中执行SSIS包。存储过程将利用SSISDB数据库中的对象以及一些支持对象。

这种方法有很大的好处。您已经具有可以执行的现有SSIS程序包。通过在应用程序的各个位置执行这些SSIS包，您可以重用现有过程。

作为参考，请看一下前面的技巧“ 如何从SQL Server存储过程执行Integration Services（SSIS）程序包”。该技巧显示了一个通过存储过程执行SSIS包的简单示例。它没有涉及处理项目和包参数的细节。技巧介绍了执行存储过程所需的ADO.NET代码。在这种情况下，我将从SQL Server开发人员的角度提供演示，重点介绍执行存储过程所需的T-SQL。

以下是实现此解决方案的概述：

查看已部署到目录的SSIS包的关键点
将SSIS项目和程序包部署到目录后，请查看SSIS目录
详细介绍如何创建可以执行部署到SSIS目录的SSIS包的存储过程
演示使用简单的T-SQL执行存储过程，并逐步执行SSIS目录报告以查看其工作方式的详细信息

SSIS程序包审查

我们的出发点是查看将在演示中使用的SSIS软件包。重点将放在与SSIS目录中的部署以及从目录中执行程序包相关的程序包部分。包装非常简单。它将CSV文件从文件夹加载到SQL Server表中。

SSIS具有项目参数和程序包参数，以使SSIS程序包可重复使用。项目参数是适用于项目中多个SSIS包的参数。程序包参数是特定于单个SSIS程序包的值。SSIS目录提供了存储和更新参数值以及在执行程序包时指定参数值的功能。

您可以在SSIS项目下方的Visual Studio（或SQL Server数据工具）的解决方案资源管理器中看到项目参数（Project.params），如下所示：

双击Project.params节点以显示项目参数：

要点如下：

CRM_Flat_File_Folder是SSIS程序包将在其中检查CSV文件以加载到SQL Server表中的文件夹。通常，这将是UNC路径（例如\\ SERVERNAME \ FOLDERNAME），但是出于演示目的，我仅使用本地计算机上的文件夹。请注意，在运行SQL Server的服务器（本例中为本地计算机）上访问该文件夹。
CRM_Staging_ServerName是运行SQL Server实例的服务器名称。通常这不是本地计算机，但是出于演示目的，我正在使用本地计算机。
两个项目参数都将Required属性设置为True。这表明在部署到SSIS目录时，我们应该提供不同的值。开发和测试软件包时，将使用SSIS软件包中的项目参数值。

您可以通过在SSIS包设计器中单击“参数”选项卡来查看包参数。示例程序包具有以下程序包参数：

package参数用于指定要加载的实际文件名。每次执行包时，这通常会有所不同。在创建和测试程序包时使用上面显示的值。Required属性为True，表示在将程序包部署到SSIS目录时，通常是从目录执行程序包时，需要指定一个值。例如，当您在SQL代理作业步骤中从目录执行SSIS包时，将被迫输入所需包参数的值。

SSIS项目目录部署

现在，我们已经查看了样本SSIS包，在将SSIS项目部署到Integration Service Catalog之后，我将对其进行回顾。创建Integration Services目录，将SSIS项目部署到目录以及创建环境涉及两个步骤。

打开SQL Server Management Studio（SSMS），然后导航到“对象资源管理器”中的“集成服务目录”，如下所示：

以下是要点：

CRM是部署项目的文件夹名称
CRM_OnBoarding是SSIS项目的名称
LoadCustomers.dtsx是SSIS项目中的SSIS程序包；我将在演示中执行它
DEFAULT是包含项目参数值的环境的名称

该环境具有变量及其值的列表。双击DEFAULT，变量和值将显示如下：

项目参数在SSIS包中定义。通常，您使用与包中指定的值不同的值。由于这只是一个演示，所以我没有。但是，使用环境的好处是，如果需要更改任何值，则可以在环境中更新它们，而不必更改SSIS包。SSIS项目中的任何程序包都可以在运行时使用环境变量值。

要查看环境如何与项目连接，请右键单击CRM_OnBoarding项目，然后从上下文菜单中选择“配置”。单击“引用”后，下面将显示“配置”窗口（您必须添加对要在SSIS项目中使用的环境的引用）：

将对环境的引用添加到项目后，您可以将环境变量映射到项目参数。单击参数，您将看到映射：

当“值”列显示带有下划线的变量名称时，该值来自具有该名称的环境变量。您可以引用多个环境。无论您在运行时使用什么环境，都将执行映射。

Customer_Flat_File_Name是一个包参数，因此该值显示要使用的文件名，而不是到环境变量的映射。我将展示从存储过程执行SSIS包时如何指定值。

创建一个存储过程以执行SSIS包

至此，我们将逐步完成一个存储过程，该过程将执行Integration Services Catalog中部署的SSIS包。存储过程如下所示：

CREATE PROCEDURE [dbo].[ExecuteLoadCustomersSSISPackage]
    @CUSTOMER_FLAT_FILE_NAME   NVARCHAR(50)
,   @EXECUTION_ID              BIGINT OUTPUT
AS
BEGIN
   -- 1. declare variables
   DECLARE
       @PROCEDURE_NAME  SYSNAME
   ,   @FOLDER_NAME     NVARCHAR(128)
   ,   @PROJECT_NAME    NVARCHAR(128)
   ,   @PACKAGE_NAME    NVARCHAR(128)
   ,   @REFERENCE_ID    BIGINT;
 
   -- 2. get this stored procedure's name
   SET @PROCEDURE_NAME = OBJECT_NAME(@@PROCID);
 
   -- 3. get the parameters for executing the SSIS package
   SELECT 
       @FOLDER_NAME  = [FOLDER_NAME]   
   ,   @PROJECT_NAME = [PROJECT_NAME]   
   ,   @PACKAGE_NAME = [PACKAGE_NAME]  
   ,   @REFERENCE_ID = [REFERENCE_ID]
   FROM [dbo].[ProcedureToCreateExecutionMapping]
   WHERE [ProcedureName] = @PROCEDURE_NAME;
 
   -- 4. check if 1 row was returned
   IF @@ROWCOUNT <> 1
   BEGIN
      -- throw error
      RETURN;
   END
 
   -- 5. create the package execution
   EXEC [SSISDB].[catalog].[create_execution]
       @folder_name = @FOLDER_NAME
   ,   @project_name = @PROJECT_NAME
   ,   @package_name = @PACKAGE_NAME
   ,   @reference_id = @REFERENCE_ID
   ,   @execution_id = @EXECUTION_ID OUTPUT;
 
   -- 6. set value for Customer_Flat_File_Name package parameter
   EXEC [SSISDB].[catalog].[set_execution_parameter_value]
        @execution_id = @EXECUTION_ID  
    ,   @object_type = 30 -- package parameter 
    ,   @parameter_name = N'Customer_Flat_File_Name'  
    ,   @parameter_value = @CUSTOMER_FLAT_FILE_NAME;
   
   -- 7. start the execution
   EXEC [SSISDB].[catalog].[start_execution]
      @execution_id = @EXECUTION_ID;
 
END

为了从目录中执行SSIS包，我们需要许多值。我创建了ProcedureToCreateExecuteMapping表来存储这些值。ProcedureName列使我可以根据正在运行的存储过程来检索参数值。

为了从目录中执行SSIS包，我们必须从SSISDB数据库中至少执行两个存储过程：create_execution和start_execution。在create_execution中，我们指定标识要执行的软件包所需的详细信息（文件夹名称，项目名称和软件包名称）。我们还指定环境的参考ID。成功执行create_execution时，它将返回EXECUTION_ID。SSIS包尚未运行。

在create_execution之后，我们可以执行其他存储过程。在这种情况下，我们执行set_execution_parameter_value，这使我们可以为Customer_Flat_File_Name包参数指定值。我们传入要加载的文件名，并从create_execution返回EXECUTION_ID值。

最后，我们执行start_execution存储过程，并传入从create_execution返回的EXECUTION_ID值。此时，SSIS包正在运行。

在继续执行存储过程之前，我想澄清一个细节。ProcedureToCreateExecutionMapping表返回了[REFERENCE_ID]列（这是环境参考），将项目连接到环境。为了获得此值，您必须执行以下查询：

-- get the folder_id
SELECT *
FROM [SSISDB].[catalog].[folders]
WHERE [name] = 'CRM';
-- [folder_id] = 20004
 
-- get the project_id
SELECT *
FROM [SSISDB].[catalog].[projects]
WHERE [folder_id] = 20004;
-- [project_id] = 40007
 
-- get the environment reference id
SELECT *
FROM [SSISDB].[catalog].[environment_references]
WHERE [project_id] = 40007;
-- [reference_id] = 30005

为了获取环境参考，您必须使用SSISDB数据库中的视图获取folder_id和project_id，如上所示。

执行存储过程

现在，我们已经创建了存储过程以从目录中执行SSIS包，是时候运行它了。使用以下T-SQL运行存储过程：

DECLARE @P_EXECUTION_IDBIGINT;
 
EXEC [dbo].[ExecuteLoadCustomersSSISPackage]
    @CUSTOMER_FLAT_FILE_NAME=N'CustomerSample.csv'
,   @EXECUTION_ID= @P_EXECUTION_IDOUTPUT;
 
SELECT @P_EXECUTION_ID;
 
SELECT
    [start_time]
,   [end_time]
,   [status]
FROM [SSISDB].[catalog].[executions]
WHERE [execution_id]= @P_EXECUTION_ID;

SSISDB.catalog.create_execution存储过程返回了@execution_id OUTPUT参数。上面的存储过程也将它作为OUTPUT参数返回。要检查SSIS包的状态，请检查[SSISDB]。[catalog]。[executions]视图。[status]列具有成功= 7的多个可能值。检查 catalog.executions以获取详细信息。在SSIS包失败或成功之前，您可能必须多次查询catalog.executions。

SSIS目录提供有关SSIS包执行的详细信息。返回到SSMS Object Explorer中的Integration Services目录，右键单击SSIS包，选择“报告”，“标准报告”和“所有执行”，如下所示：