如何使用 SSIS 包自动执行存储过程? [英] How to automate the execution of a stored procedure with an SSIS package?

问题描述

我有一个使用执行 SQL 任务通过 SQL SSIS 执行的存储过程.

I have a stored procedure that gets executed through SQL SSIS using a Execute SQL Task.

任务有以下内容:

USE [OPPY_DWUSD]
GO
DECLARE @return_value int
EXEC    @return_value = [dbo].[generate_merge_scdbk]
    @Schema = N'dim',
    @Dimension = N'VARIETY',
    @ETLSchema = N'stg',
    @ETLTable = N'vw_VARIETY',
    @Execute = 1

SELECT  'Return Value' = @return_value
GO

现在我有这个设置，我有多个执行 SQL 任务，代码相同但值不同，大约 20 个执行 SQL 任务.

Right now the way I have this setup, I have multiple Execute SQL Tasks with the same code but different values, about 20 Execute SQL Tasks.

有没有更简洁的方法来解决这个问题?

Is there a more cleaner way to pull this off?

推荐答案

这是一种方法.该示例使用 SSIS 2008 R2 和 SQL Server 2012 后端.

Here is one way of doing this. The example uses SSIS 2008 R2 with SQL Server 2012 backend.

创建一个表来存储您的参数值.假设表名是 dbo.SProcValues.根据您的存储过程定义，表架构将如下所示.

Create a table to store your parameter values. Let's say the table name is dbo.SProcValues. Based on your stored procedure definition, the table schema would look like this.

CREATE TABLE dbo.SProcValues(
    Id int IDENTITY(1,1) NOT NULL,
    SProcName nvarchar(40) NOT NULL,
    SchemaName nvarchar(20) NOT NULL,
    Dimension nvarchar(40) NOT NULL,
    ETLSchema nvarchar(20) NOT NULL,
    ETLTable nvarchar(40) NOT NULL,
    IsExecute bit NOT NULL
) 
GO

让我们使用以下脚本插入一些示例数据.

Let's insert some sample data using the following script.

INSERT INTO dbo.SProcValues 
    (SProcName, SchemaName, Dimension, ETLSchema, ETLTable, IsExecute) VALUES
    ('dbo.sp_generate_merge', 'dim1', 'dimension1', 'stg1', 'table1', 1),
    ('dbo.sp_generate_merge_scdbk', 'dim2', 'dimension2', 'stg2', 'table2', 1),
    ('dbo.sp_generate_merge_scdbk', 'dim3', 'dimension3', 'stg3', 'table3', 0),
    ('dbo.sp_generate_merge', 'dim4', 'dimension4', 'stg4', 'table4', 0);
GO

在 SSIS 包上，假设您已经建立了数据源和连接管理器.创建以下变量.变量 SProcValues 将保存我们存储在上述表中的参数集.变量 SQLInnerQuery 将保存稍后将在内部执行 SQL 任务中使用的查询.其他变量与表中可用的每一列相关，因此我们可以遍历每一行并将其保存在一个变量中.

On the SSIS package, assuming that you have the data source and connection manager already established. Create the following variables. Variable SProcValues will hold the parameter set that we stored in the above-mentioned table. Variable SQLInnerQuery will hold the query that will be used later in the inner Execute SQL Task. Other variables relate to each column available in the table so we can loop through each row and hold it in a variable.

将以下查询粘贴到变量 SQLGetParameters

Paste the following query in the value of the variable SQLGetParameters

SELECT SProcName, SchemaName, Dimension, ETLSchema, ETLTable, IsExecute FROM dbo.SProcValues

选择变量 SQLInnerQuery 并按 F4 查看属性.将属性 EvaluateAsExpression 设置为 True，然后针对 Expression 属性单击省略号按钮.

Select the variable SQLInnerQuery and press F4 to view the properties. Set the property EvaluateAsExpression to True and then click the Ellipsis button against the Expression property.

我们需要设置一个表达式，该表达式将计算为 EXEC 存储过程语句，该语句稍后可以提供给内部执行 SQL 任务.设置以下表达式.

We need to set an expression that will evaluate to the EXEC stored procedure statement that can be later supplied to the inner Execute SQL Task. Set the following expression.

"EXEC"+ @[用户::SProcName] + "@Schema = ?, @Dimension = ?, @ETLSchema = ?, @ETLTable = ?, @IsExecute = ?"

如果您单击编辑器上的 Evaluate Expression 按钮，您可以看到表达式的计算结果.您还会注意到以下屏幕截图中没有存储过程名称，这是因为包变量 SProcName 当前没有任何值.在运行时，SProcName 将被分配表中的值，该表达式将自动解析自身.

If you click Evaluate Expression button on the editor, you can see what the expression will evaluate to. You will also notice that there is no stored procedure name in the below screenshot that is because the package variable SProcName currently does not have any value. During runtime, the SProcName will be assigned with the value from the table and this expression will automatically resolve itself.

在 SSIS 包上，拖放一个执行 SQL 任务.此任务将运行以下查询以获取存储在表 dbo.SProcValues 中的参数值列表.在执行 SQL 任务上配置常规页面，如下所示.示例使用 OLEDB 连接，连接管理器/数据源命名为 Practice.

On the SSIS package, drag and drop an Execute SQL Task. This task will run the following query to fetch the list of parameter values that are stored in the table dbo.SProcValues. Configure the General page on the Execute SQL Task as shown below. The example uses OLEDB connection and the connection manager/data source is named as Practice.

配置执行 SQL 任务的结果集页面，将查询的结果集存储到对象变量中.

Configure the Result Set page of Execute SQL Task to store the result set from the query to an object variable.

既然第一个执行 SQL 任务已配置为获取应传递给存储过程的参数值列表，您需要遍历记录.

Now that the first Execute SQL Task is configured to get the list of parameter values that should be passed to the stored procedure, you need to loop through the records.

拖放 Foreach 循环容器.将执行 SQL 任务的优先级容器连接到 Foreach 循环容器.配置 Foreach Loop 容器的 Collection 页面，如下所示.我们正在使用 ADO 枚举器遍历结果集.

Drag and drop a Foreach Loop container. Connect the Execute SQL Task's precedence container to the Foreach Loop container. Configure the Collection page of the Foreach Loop container as shown below. We are looping through the result set using the ADO enumerator.

在 Foreach 循环容器上配置变量映射页面，如下所示.当我们遍历每一行时，我们将列值存储在相应的变量中，以便我们可以将其传递给下一个执行 SQL 任务以运行存储过程.

Configure the Variable Mappings page on Foreach Loop container as shown below. As we loop through each row, we are storing the column values in respective variables so we can pass it to the next Execute SQL Task to run the stored procedure.

在 Foreach Loop 容器中拖放一个 Execute SQL Task，这样每次我们循环遍历结果集中的一行时都会执行这个任务.配置执行 SQL 任务，如下所示.

Drag and drop an Execute SQL Task inside the Foreach Loop container so that this task is executed each time we loop through a row in the result set. Configure the Execute SQL Task as shown below.

您可能希望根据您的要求在第二个执行 SQL 任务上配置 ResultSet 属性.如果选择 ResultSet，则需要配置适当的对象变量来接受结果集.对于这个示例，我将其保留为 None.

You might want to configure the ResultSet property on this second Execute SQL Task according to your requirements. If you choose ResultSet, then you need to configure an appropriate object variable to accept the result set. I left it as None for this example.

配置要作为参数传递给存储过程的值.

Configure the values to be passed as parameters to the stored procedure.

最后，控制流看起来像这样.

Finally, the control flow would look something like this.

当程序包运行时，循环将针对上述 SELECT 查询返回的记录执行存储过程，前提是您在表行中定义的所有存储过程都在数据库中可用.我使用相同的参数定义创建了存储过程 dbo.sp_generate_merge_scdbk 和 dbo.sp_generate_merge.这就是包成功执行的原因.

When the package runs, the loop will execute the stored procedure for as many records are returned by the SELECT query mentioned above, provided that you have all the stored procedures defined in the table rows are available in the database. I had created the stored procedures dbo.sp_generate_merge_scdbk and dbo.sp_generate_merge with the same parameters definition. That's the reason the package executed successfully.

这篇关于如何使用 SSIS 包自动执行存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！