sql2012 ssrs_如何使用SQL Server Reporting Services（SSRS）执行SQL代理作业-CSDN博客

sql2012 ssrs

Every production ETL (Extract, Transform, Load) solution is often intrinsically linked to a scheduling mechanism that is used to execute that ETL solution. In a SQL Server-based environment, SQL Server Agent is one of the scheduling mechanism that can be utilized to schedule an execution of ETL solutions such as SQL Server Integration Service (SSIS) packages. In the organization that I currently work for, we’ve had several instances (for various reasons) whereby as the data team we’ve been required to provide a platform for business users to execute an ETL at their own convenience (i.e. on-demand). In this article, we will demonstrate on how we went about delivering self-service ETL execution requirement.

每个生产ETL（提取，转换，加载）解决方案通常都固有地链接到用于执行该ETL解决方案的调度机制。在基于SQL Server的环境中，SQL Server代理是一种调度机制，可用于调度ETL解决方案（如SQL Server集成服务（SSIS）程序包）的执行。在我目前工作的组织中，由于各种原因，我们有多个实例，作为数据团队，我们被要求为业务用户提供一个平台，以便他们在自己方便的时候（即按需）执行ETL。）。在本文中，我们将演示如何实现自助ETL执行要求。

自助ETL执行的概念 (The Notion of Self-Service ETL Execution)

At the outset, leaving it to business users to run ETL solutions should not be the preferred way of doings. Instead, business users should only be given control of manipulating and reporting on the data. This is where the notion of self-service reporting becomes useful as power users from the business can connect to a data model and slice and dice according to their requirement. Nevertheless, there could be viable circumstances that force data teams to extend business users control of reporting to executing ETL solutions.

从一开始，将其留给业务用户来运行ETL解决方案应该不是首选的做法。取而代之的是，应该只给业务用户控制操作和报告数据的权限。在这里，自助服务报告的概念变得非常有用，因为企业中的高级用户可以连接到数据模型，并根据其需求进行切片和切块。但是，可能存在可行的情况，迫使数据团队将业务用户对报告的控制扩展到执行ETL解决方案。

Whatever your reasons for allowing business to execute ETL solutions, you should try to keep it simple for business users to do so. In our case, we devised a way for self-service ETL execution using SQL Server Reporting Services (SSRS) as most business users are already familiar with the tool.

无论出于什么原因允许企业执行ETL解决方案，您都应尽量简化企业用户执行ETL解决方案的过程。在我们的案例中，由于大多数企业用户已经熟悉该工具，因此我们设计了一种使用SQL Server Reporting Services（SSRS）执行自助ETL的方法。

In the following sections, we will take a look at three steps required to setup self-service ETL execution using SSRS as a business interface.

在以下各节中，我们将介绍使用SSRS作为业务接口来设置自助ETL执行所需的三个步骤。

步骤1：设置SQL代理作业 (Step 1: Setup a SQL Agent Job)

The first step involves setting up a SQL Server agent job that will execute an ETL solution. You can skip this step if you already have a SQL Server Agent job that executes some form of an ETL solution. For the purposes of this discussion, I set up a sample SQL Server agent job, referred to as My Dummy Job. The properties of this sample job are shown in Figure 1.

第一步涉及设置SQL Server代理作业，该作业将执行ETL解决方案。如果您已经具有执行某种形式的ETL解决方案SQL Server代理作业，则可以跳过此步骤。为了便于讨论，我设置了一个示例SQL Server代理作业，称为“ 我的虚拟作业” 。此示例作业的属性如图1所示。

The complete T-SQL script for My Dummy Job can be found in the Downloads section at the bottom of this article.

可以在本文底部的“ 下载”部分中找到“ 我的虚拟作业 ”的完整T-SQL脚本。

步骤2：配置SSRS数据集 (Step 2: Configuring SSRS Dataset)

Now that we have configured out SQL Server agent job we switch our focus to SSRS development and deployment. In this step, we set up a sample stored procedure – RunMyJob – that will be embedded in SSRS datasets. The definition of this sample stored procedure is shown in Script 1.

现在我们已经配置了SQL Server代理作业，我们将重点转移到了SSRS的开发和部署上。在此步骤中，我们设置了一个示例存储过程RunMyJob ，它将嵌入到SSRS数据集中。 脚本1中显示了此示例存储过程的定义。

 
  CREATE PROCEDURE [dbo].[RunMyDummyJob] (@RunOption VARCHAR(100))
  WITH EXECUTE AS CALLER
AS
  BEGIN
      SET NOCOUNT ON;
      --Part A
      DECLARE @ConditionCheck INT = (
	SELECT TOP 1 
	  CASE 
	    WHEN stop_execution_date IS NULL AND start_execution_date IS NOT NULL THEN 1 
	    ELSE 0 
	  END 
	FROM msdb.dbo.sysjobactivity 
	WHERE job_id in (
	  SELECT job_id 
	  FROM msdb.dbo.sysjobs 
	  WHERE name like 'My Dummy Job'
	)
      )
      --Part B
      IF @ConditionCheck = 0
      BEGIN
	  IF (@RunOption='Load')
	  BEGIN
	      EXEC msdb.dbo.sp_start_job 'My Dummy Job'
	  END
      END
      --Part C
      EXEC msdb.dbo.sp_help_jobactivity @job_name = 'My Dummy Job'
  END
GO

Script 1: Definition of RunMyDummyJob Stored Procedure

脚本1： RunMyDummyJob存储过程的定义

The stored procedure accepts a string parameter that has two possible values:

该存储过程接受具有两个可能值的字符串参数：

My Dummy Job latest execution history我的虚拟作业”最新执行历史
My Dummy Job我的虚拟作业

As it can be seen, the stored procedure is divided into three parts. Part A declares and uses local variable @ConditionCheck variable to determine whether or not My Dummy Job is currently being executed. This is done to prevent executing a job that is already executing. Part B is used to execute My Dummy Job provided the @ConditionCheck variable value is 0 and the @RunOption variable value is Load. The last part of the stored procedure is executed regardless of the value of the @ConditionCheck variable. This last part retrieves latest activities relating to My Dummy Job.

可以看出，存储过程分为三个部分。 A部分声明并使用局部变量@ConditionCheck变量来确定当前是否正在执行“ 我的虚拟作业” 。这样做是为了防止执行已经执行的作业。如果@ConditionCheck变量值为0且@RunOption变量值为Load ，则B部分用于执行My Dummy Job 。无论@ConditionCheck变量的值如何，都将执行存储过程的最后一部分。最后一部分检索与“ 我的虚拟作业”有关的最新活动。

步骤3：报表开发和部署 (Step 3: Report Development and Deployment)

Now that we have set up the Job and the stored procedure that will execute that job, we move on to developing the actual SSRS report that will be used as the interface for business users to execute an ETL on their own. As I go through some of the items in the design view of the report, you can always access the complete report .rdl file in the Downloads section, located at the bottom of this article.

现在，我们已经设置了Job和将执行该作业的存储过程，我们继续开发实际的SSRS报告，该报告将用作业务用户自己执行ETL的接口。在查看报表设计视图中的某些项目时，您始终可以在本文底部的“ 下载”部分中访问完整的报表.rdl文件。

Visual Studio was used an IDE to develop this report and Figure 2 shows an SSDT 2013 design view of the report which consists of a single tablix control with 7 columns.

Visual Studio被一个IDE用于开发此报告，图2显示了该报告的SSDT 2013设计视图，该视图由一个带有7列的tablix控件组成。

The tablix is populated by dataset – RunMyDummyJob. Figure 3 shows the properties of RunMyDummyJob dataset and you will notice that the dataset is in turn based off the RunMyDummyJob stored procedure which was created in the previous step (Step 2).

Tablix由数据集– RunMyDummyJob填充。图3显示了RunMyDummyJob数据集的属性，您将注意到该数据集又基于上一步（步骤2）中创建的RunMyDummyJob存储过程。

The dataset has a single parameter which is mapped to a report parameter – @Operation, as shown in Figure 4.

数据集具有单个参数，该参数映射到报告参数– @Operation， 如图4所示。

As shown in Figure 5, the @Operation parameter has two hard-coded available values, namely, Refresh as well as Load. Refresh is set as the default value as a means of precaution so as not to execute the report every time it is opened/previewed.

如图5所示，@Operation参数具有两个硬编码的可用值，即Refresh和Load。为了预防起见，将“刷新”设置为默认值，以便在每次打开/预览报告时都不执行报告。

By default, a preview of the report displays job activity information as shown in Figure 6.

默认情况下，报告的预览显示作业活动信息， 如图6所示。

For a business users to execute the ETL (MyDummyJob), they will have to change the Operation parameter value from Refresh to Load and then click View Report button. Once the View Report button has been clicked, the report will display latest job activity details with the run status column indicating that the job is running as shown in Figure 7.

对于要执行ETL（ MyDummyJob ）的业务用户，他们将必须将Operation参数值从Refresh更改为Load，然后单击View Report按钮。单击“查看报告”按钮后，报告将显示最新的作业活动详细信息，其中“运行状态”列指示作业正在运行， 如图7所示。

In order to get the progress of the job execution, you will have to change the Operation parameter value from Load back to Refresh and click View Report button again. When the job has completed running, the report will display information similar to what is shown in Figure 8.

为了获得作业执行的进度，您必须将“操作”参数值从“加载回”更改为“刷新”，然后再次单击“查看报告”按钮。作业完成运行后，报告将显示类似于图8所示的信息。

Another way of confirming that the job history displayed in the SSRS report is accurate is by viewing SQL Server Management Studio’s Job Activity Monitor and the Last run column should match to job start date field in the SSRS report.

确认SSRS报告中显示的作业历史记录正确的另一种方法是查看SQL Server Management Studio的“作业活动监视器”，并且“ 上次运行”列应与SSRS报告中的作业开始日期字段匹配。

结论 (Conclusion)

Unlike self-service reporting, self-service ETL execution should not be a preferred way of running your ETL solutions as there are several steps and checks involved in getting it set up. However, in circumstances where a requirement for self-service ETL execution is justified, you can use the steps described in this article as a guide to assist you in setting up such a process.

与自助式报告不同，自助式ETL执行不应该是运行ETL解决方案的首选方式，因为设置它涉及多个步骤和检查。但是，在有必要执行自助ETL的情况下，您可以使用本文中描述的步骤作为指导，以帮助您建立这样的过程。