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

【 tulaoshi.com - 编程语言 】

　　问题

　　我们很多年来都日常加载数据仓库，每次加载的都是真实记录。我们想批处理地执行这个过程并能够在发生错误的情况下重启失败时间点。你能够为我们提供一个例子介绍下我们该怎样在一个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 2005自带的AdventureWorks示例数据库获得我们的源数据。使用下面的脚本将AdventureWorks的SalesOrderHeader和SalesOrderDetail表复制到一个叫做mssqltips的数据库(如果它不存在，那么创建这个数据库)中：

　　USEmssqltips
　　GO
　　SELECT*
　　INTOdbo.imp_SalesOrderHeader
　　FROMAdventureWorks.Sales.SalesOrderHeader
　　SELECT*
　　INTOdbo.imp_SalesOrderDetail
　　FROMAdventureWorks.Sales.SalesOrderDetail
　　ALTERTABLEdbo.imp_SalesOrderHeader
　　ADDProcessedbitnotnulldefault0
　　GO

　　当记录被处理后，Processed字段将更新为1。

　　在SSIS包中，下面的变量将被使用：

　　我们将在下面的章节中描述变量的用法。

　　Get Batch List(获得批处理列表)

　　Get Batch List执行一个存储过程，它将源数据分组进批处理中。有很多方法来完成这个任务，在这个例子中，我们简单地在存储过程stp_CreateOrderBatchList中按照年和月份来分组：

　　SELECT
　　DATEPART(YYYY,OrderDate)OrderYear
　　,DATEPART(MONTH,OrderDate)OrderMonth
　　FROMdbo.imp_SalesOrderHeader
　　WHEREProcessed=0
　　GROUPBY
　　DATEPART(YYYY,OrderDate)
　　,DATEPART(MONTH,OrderDate)
　　ORDERBY
　　DATEPART(YYYY,OrderDate)
　　,DATEPART(MONTH,OrderDate)

　　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任务，它调用一个存储过程从源表提取一批数据并将它们附加到销售历史表中。如果需要转换，那么我们应该使用一个数据流任务。销售历史表和存储过程如下所示：

　　CREATETABLEdbo.SalesHistory(
　　OrderYearintnotnull,
　　OrderMonthintnotnull,
　　ProductIDintnotnull,
　　OrderQtysmallintnotnull,
　　LineTotalmoneynotnull
　　)
　　CREATEPROCEDUREdbo.stp_AppendSalesHistory
　　@OrderYearint
　　,@OrderMonthint
　　AS
　　BEGIN
　　SETNOCOUNTON;
　　INSERTINTOdbo.SalesHistory(
　　OrderYear
　　,OrderMonth
　　,ProductID
　　,OrderQty
　　,LineTotal
　　)
　　SELECT
　　DATEPART(YYYY,m.OrderDate)
　　,DATEPART(MONTH,m.OrderDate)
　　,d.ProductID
　　,d.OrderQty
　　,d.LineTotal
　　FROMdbo.imp_SalesOrderHeaderm
　　JOINdbo.imp_SalesOrderDetaildONd.SalesOrderID=m.SalesOrderID
　　WHEREProcessed=0
　　ANDDATEPART(YYYY,m.OrderDate)=@OrderYear
　　ANDDATEPART(MONTH,m.OrderDate)=@OrderMonth
　　END
　　GO

　　注意，这个存储过程只获取Processed字段等于0的记录。

　　Execute SQL任务的一般属性设置如下所示：

　　Execute SQL任务的参数匹配属性设置如下所示：

　　在上面的设置中，设置了SQLStatement来执行这个存储过程，对所要求的参数使用占位符。SSIS包变量按照这个存储过程中参数的序列号匹配到这些参数。

　　Compute Aggregation(计算聚合)

　　Compute Aggregation是一个Execute SQL任务，它重新计算销售历史概要表中的概要数据用于要进行的订单年和订单月份批处理。销售历史概要表和存储过程如下所示：

　　CREATETABLEdbo.SalesHistorySummary(
　　OrderYearintnotnull,
　　OrderMonthintnotnull,
　　ProductIDintnotnull,
　　OrderQtysmallintnotnull,
　　LineTotalmoneynotnull
　　)
　　CREATEPROCEDUREdbo.stp_CalcSalesHistorySummary
　　@OrderYearint
　　,@OrderMonthint
　　AS
　　BEGIN
　　SETNOCOUNTON;
　　DELETEFROMdbo.SalesHistorySummary
　　WHEREOrderYear=@OrderYear
　　ANDOrderMonth=@OrderMonth;
　　INSERTINTOdbo.SalesHistorySummary(
　　OrderYear
　　,OrderMonth
　　,ProductID
　　,OrderQty
　　,LineTotal
　　)
　　SELECT
　　OrderYear
　　,OrderMonth
　　,ProductID
　　,SUM(OrderQty)
　　,SUM(LineTotal)
　　FROMdbo.SalesHistory
　　WHEREOrderYear=@OrderYear
　　ANDOrderMonth=@OrderMonth
　　GROUPBY
　　OrderYear
　　,OrderMonth
　　,ProductID
　　END
　　GO