怎样执行SQL Server集成服务(SSIS)中的批处理 电脑资料

怎样执行SQL Server集成服务(SSIS)中的批处理 电脑资料 问题 我们很多年来都日常加载数据仓库，每次加载的都是真实记录， 专家解答 SSIS使用Toolbox中的现有组件能够很好地支持批处理。在SSIS中执行批处理的一个很简便的方法是将记录分组放到批处理中，处理每个批处理，然后将每个组更新为处理过的。首先让我们描绘一个场景然后执行一个SSIS包来完成这个工作。 在开发报表应用程序中，一个很常见的需求是聚合数据使得报表查询可以快速地执行。让我们假设我们想要按月份聚合数据。我们还想具有能够调整聚合数据并只重新计算调整过的按月聚合的能力。 我们可以假设一个SSIS包具有以下步骤： Get Batch List(获得批处理列表)是一个Execute SQL任务，它将源数据分组在批处理中执行，创建一个每个批处理中包含一个单独记录的结果集。 Process Batch Loop(执行批处理循环)是一个Foreach Loop容器，它迭代这个结果集记录;例如，这个结果集中的每条记录执行一次。 Transaction Container(事务容器)是一个序列容器，它包含这个循环的每个迭代要执行的任务;它控制事务，如果成功就提交，如果失败就回滚。 Append Batch to Sales History(附加批处理到销售历史)是一个Execute SQL任务，它提取一批记录并将它们插入到一个历史表中。 Compute Aggregation(计算聚合)是一个Execute SQL任务，它对这个批处理执行聚合并更新聚合表。 Mark Batch as Processed(将批处理标记为处理过的)是一个Execute SQL任务，它更新源表中的记录以显示它们已经处理过了。 在下面的章节中，我们将详细讨论SSIS包中的每个步骤。从建立开始，然后逐步介绍。 建立 为了简便，我们将从SQL Server xx自带的AdventureWorks示例数据库获得我们的源数据。使用下面的脚本将AdventureWorks的SalesOrderHeader和SalesOrderDetail表复制到一个叫做mssqltips的数据库(如果它不存在，那么创建这个数据库)中： USE mssqltips GO SELECT * INTO dbo.imp_SalesOrderHeader FROM AdventureWorks.Sales.SalesOrderHeader SELECT * INTO dbo.imp_SalesOrderDetail FROM AdventureWorks.Sales.SalesOrderDetail ALTER TABLE dbo.imp_SalesOrderHeader ADD Processed bit not null default 0 GO 当记录被处理后，Processed字段将更新为1。 在SSIS包中，下面的变量将被使用： 我们将在下面的章节中描述变量的用法。 Get Batch List(获得批处理列表) Get Batch List执行一个存储过程，它将源数据分组进批处理中。有很多方法来完成这个任务，在这个例子中，我们简单地在存储过程stp_CreateOrderBatchList中按照年和月份来分组： SELECT DATEPART(YYYY,OrderDate) OrderYear ,DATEPART(MONTH,OrderDate) OrderMonth FROM dbo.imp_SalesOrderHeader WHERE Processed = 0 GROUP BY DATEPART(YYYY,OrderDate) ,DATEPART(MONTH,OrderDate) ORDER BY DATEPART(YYYY,OrderDate) ,DATEPART(MONTH,OrderDate) 注意，这个存储过程只获得Processed字段等于0的记录。 在我们的SSIS包中Execute SQL任务执行上面的存储过程。一般属性页显示如下： 注意，ResultSet属性设置为Full result set(完全结果集)。这个存储过程只进行一个SELECT，而Execute SQL任务将结果集存储在一个包变量中。结果集属性页将结果集匹配到包变量User:v_BatchList。变量类型必须是System.Object。结果集名称要求是0。 Process Batch Loop(处理批处理循环) Process Batch Loop是一个Foreach Loop容器，它迭代在Get Batch List中创建的结果集，一次进行结果集中的一条记录。有两个属性页要进行配置集合和变量匹配。集合属性页具有下面的设置： 为了迭代Get Batch List创建的结果集，Enumerator被设置为Foreach ADO Enumerator，而ADO object source variable(ADO对象源变量)被设置为User:v_BatchList。Get Batch List将User:v_BatchList变量匹配到这个结果集。Enumeration mode(Enumeration模式)被设置为Rows in the first table(第一个表中的记录，在这个结果集中只有一个表)。 变量匹配属性页具有以下设置： 在Get Batch List中执行的这个存储过程返回一个具有两个字段的结果集OrderYear和OrderMonth。变量匹配属性页将结果集中每条记录的字段匹配到基于字段顺序位置的包变量(第一个字段为0)。 Transaction Container(事务容器) Transaction Container是一个序列容器。这个容器中的任务都在一个事务中执行。它们或者都执行成功并被提交，或者出错后回滚。将序列容器的TransactionOption属性设置为Required(必需的);这个设置在一个事务环境中执行这个容器中所有的任务。在这个循环中每次都新创建一个事务。 Append Batch to Sales History(将批处理附加到销售历史) Append Batch to Sales History是一个Execute SQL任务，它调用一个存储过程从源表提取一批数据并将它们附加到销售历史表中， CREATE TABLE dbo.SalesHistory ( OrderYear int not null, OrderMonth int not null, ProductID int not null, OrderQty smallint not null, LineTotal money not null ) CREATE PROCEDURE dbo.stp_AppendSalesHistory OrderYear int ,OrderMonth int AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.SalesHistory ( OrderYear ,OrderMonth ,ProductID ,OrderQty ,LineTotal ) SELECT DATEPART(YYYY,m.OrderDate) ,DATEPART(MONTH,m.OrderDate) ,d.ProductID ,d.OrderQty ,d.LineTotal FROM dbo.imp_SalesOrderHeader m JOIN dbo.imp_SalesOrderDetail d ON d.SalesOrderID = m.SalesOrderID WHERE Processed = 0 AND DATEPART(YYYY,m.OrderDate) = OrderYear AND DATEPART(MONTH,m.OrderDate) = OrderMonth END GO 注意，这个存储过程只获取Processed字段等于0的记录。 Execute SQL任务的一般属性设置如下所示： Execute SQL任务的参数匹配属性设置如下所示： 在上面的设置中，设置了SQLStatement来执行这个存储过程，对所要求的参数使用占位符。SSIS包变量按照这个存储过程中参数的序列号匹配到这些参数。 Compute Aggregation(计算聚合) Compute Aggregation是一个Execute SQL任务，它重新计算销售历史概要表中的概要数据用于要进行的订单年和订单月份批处理。销售历史概要表和存储过程如下所示： CREATE TABLE dbo.SalesHistorySummary ( OrderYear int not null, OrderMonth int not null, ProductID int not null, OrderQty smallint not null, LineTotal money not null ) CREATE PROCEDURE dbo.stp_CalcSalesHistorySummary OrderYear int ,OrderMonth int AS BEGIN SET NOCOUNT ON; DELETE FROM dbo.SalesHistorySummary WHERE rderYear = OrderYear AND rderMonth = OrderMonth; INSERT INTO dbo.SalesHistorySummary ( OrderYear ,OrderMonth ,ProductID ,OrderQty ,LineTotal ) SELECT OrderYear ,OrderMonth ,ProductID ,SUM(OrderQty) ,SUM(LineTotal) FROM dbo.SalesHistory WHERE rderYear = OrderYear AND rderMonth = OrderMonth GROUP BY OrderYear ,OrderMonth ,ProductID END GO 上面的存储过程首先删除概要表中要被处理的订单年和月份的所有记录，然后执行聚合和插入。Execute SQL任务属性设置和Append Batch to Sales History中的一样，除了要执行的存储过程的名称;我们跳过显示这个截屏。 Mark Batch as Processed(将批处理标记为处理过的) Mark Batch as Processed是一个Execute SQL任务，它更新源表中在当前批处理中处理过的记录的Processed字段。它调用下面的存储过程： CREATE PROCEDURE dbo.stp_MarkOrdersProcessed OrderYear int ,OrderMonth int AS BEGIN SET NOCOUNT ON; UPDATE dbo.imp_SalesOrderHeader SET Processed = 1 WHERE DATEPART(YYYY,OrderDate) = OrderYear AND DATEPART(MONTH,OrderDate) = OrderMonth; END GO Execute SQL任务属性设置也和之前的一样，除了存储过程