SQL+Server+BI+Step+by+Step+SSIS+.doc

SQL Server BI Step by Step SSIS 1 - 准备SQL Server 2005 和2008提供了很多新的和增强的商务智能功能,包括利用集成服务(SSIS)整合多种数据源;利用分析服务(SSAS)使数据内容更丰富并且建立复杂的商业分析; 以及利用报表服务(SSRS)编辑,管理,和提交丰富的报表. 如果你现在还不清楚这些功能,那么接下来一系列的介绍会让你对SQL Server现在的商务智能支持大吃一惊.不过现在关于SQL Server商务智能(SQL Server Business Intelligence - BI)的中文资料相对较少,很多时间对于一些复杂问题的研究,都需要直接搜索英文资料或者是直接去国外的社区求教.从本文开始,我将以现在掌握的相关知识为基础,介绍SQL Server BI,希望和这方面的朋友一过研究和提高.让我们先做一下前期的准备工作,整个案例都会以AdventureWorks数据库为基础,如果你在安装SQL Server时没有选择安装,也可以单独下载,/SqlServerSamples,而且这里包括SQL Server的很多例子,工具和资源,如果你有BI方面的基础,建议直接从上面下载例子进行研究.AdventureWorks数据库及示例的安装可以参照/luman/archive/2008/08/28/1278447.html如果你对AdventureWorks数据库并不熟悉,请先通过以下资源进行了解:/zh-cn/library/ms124438(SQL.90).aspxSQL Server 2005AdventureWorks数据字典/zh-cn/library/ms124438.aspx SQL Server 2008AdventureWorks数据字典/CUI/Register.aspx?culture=zh-CN&EventID=1032321320&CountryCode=CN&IsRedirect=false 介绍AdventureWorks 数据库的webcast在安装SQL Server时,请选择安装Integration Service,Reporting Service,Analysis Service等服务,并且选中开发工具.安装完成后,就可以用vs .net打开BI项目:SSIS项目: SSAS项目: SSRS项目: 可以看到,微软已经给出BI的一整套解决方案,而且他们之间可以互操作,Reporting Service可以根据SSAS生成的多维数据集生成复杂的KPI报表,Integration Service也可以在控制流中调用SSAS进行数据分析,另外Sql Server BI还能够和微软的其它产品整合,比如Reporting Service直接整合到MOSS中,可以安装插件,在Excel中直接操作SSAS分析出来的数据,使客户端更加方便的操作.这些我们在后面都会一一介绍. SQL Server BI Step by Step SSIS 2让我们首先开始学习SSIS吧,利用SSIS把SQL Server中的数据导出.首先,打开V 2005,选择商业智能项目,然后选择模板中的Integration Service项目,输入项目名称:创建好后,我们就在默认的Package包中进行设计(关于一些概念性的就不介绍了,请参照这个文章, 或者是其它的教程或者书籍).首先向控制流中添加一个数据流组件(Data Flow Task),双击进入数据流.从左边的工具箱中选择OLEDB数据源(OLE DB Source),可以看到,添加进去后是红色的.双击设置一下连接,如果没有已经创建的链接,则新建一个连接,选定后设置直接访问Produt表,当然在这里也可以通过sql语句获取数据源,其中可以调用存储过程,另外还可以通过变量设置的方式,可以把表或者视图的名称,或者sql命令直接放在变量中. OK,确认了之后,发现红色的错误提示已经不存在了.接下来,我们直接再添加一个目标数据源,我们将数据导出成Excel数据格式,所以选择Excel Destination,同样,双击对Excel连接管理器进行配置,配置好文件名称和路径以后(此处选择在首行显示列名,这样会从第二行开始才开始显示数据),如果文件不存在,直接选择下面的新建,创建新的Excel工作表.然后从左边切换到映射,对数据流中的元数据的列和Excel表中的列进行一一映射,因为刚才是自动创建的Excel工作表,所以默认是根据名称对应的.这样我们就完成了对Product产品表的导出,在右边的解决方案中,右击执行包,可以看到绿色执行成功. 打开刚才指定的路径中的Excel文件,已经包含了导出的数据.是不是比手工写C#代码实现数据库数据导出到Excel文件方便了很多?呵呵,这还只是最基础的功能.我们通过设置sql语句导出所有颜色为黑色的产品,让我们把OLEDB数据源的访问模式改成sql命令,然后输入查询语句:SELECT*FROM Production.ProductWHERE (Color = Black)再次执行时就会发现生成的Excel表中已经只包含了Color=Black的数据(注意,如果你刚才所有的数据的Excel文件没有删除,你会发现这次导出的数据是添加到了上次的数据的后面).接下来,我们再将刚才导出的产品数据导入Product表中.再添加Excel Source和OLE DB Destination,其实就是做和导出相反的过程.把Excel Source的OLEDB连接指向Excel连接管理器(刚才导出中Excel Destination中设置的,在下面连接管理器中会列出来),OLE DB Destination的连接设置成OLE DB Source中的数据库连接,同样,设置成以表或视图的方式访问Product表,确定后发现有红色错误提示,这是因为数据库中Product产品是以ProductID作为主键标识的,所以不能够插入,我们从映射中设置将ProdutID字段删除,同样的,我们需要将rowguid字段,这两个字段都是数据库中自动生成的.再次确认后会发现已经没有错误,只剩下了黄色的警告,我们现在暂时不理会这个警告.我们把刚才生成的Excel文件删除,重新配置Excel连接管理器生成新的空Excel文件(或者把生成的Excel中的数据删除),然后再次运行包,你会发现,刚才的数据导出仍然正常,但是数据导入却显示的是没有导入任何数据,这是因为在数据中刚才的数据导出和导出并没有先后,所以他们是同步执行的,执行导入时发现里面的数据为空,所以没有导入成功任何数据.不过,尝试着把数据导入的操作直接放在Excel Destination后面是失败的,Excel Destination就是数据流目标,意味着整个流程的结束.(此时Excel Destination中只可定义一个错误输出).我们切换到控制流,再添加一个数据流任务,将第一个数据流任何指向这个(鼠标拖拉绿色箭头):双击刚添加的数据导入(已经编辑的数据流组件名称),把刚才的数据流中的组件剪切过来.清空Excel数据再次运行包,怎么还是有错误,”这是为什么呢?” ,呵呵,看下面的错误信息:An OLE DB record is available.Source: Microsoft SQL Native ClientHresult: 0x80040E2FDescription: 语句已终止。.An OLE DB record is available.Source: Microsoft SQL Native ClientHresult: 0x80040E2FDescription: 不能在具有唯一索引 AK_Product_ProductNumber 的对象 Production.Product 中插入重复键的行。.其实产品编号ProductNumber也是主键,呵呵,这个问题是在运行前SSIS没有提示的.怎么办呢?为了达到目的,我们暂时通过添加一次转换,在刚才的数据流源和数据流目标中间再添加一个派生列组件(Derived Column ,Updates column values using expressions).添加一个新列NewProductNumber,在Excel中的产品编号后面加1,组成新的产品编号,同样我们派生出一个新的产品名称Name,因为在数据库中同样也有唯一性约束. 同时,我们还要修改OLE DB目标中的映射,将目标列的ProductNumber对应的输入列ProductNumber改成刚才派生的NewProductNumber.将目标列的Name对应的输入列Name改成刚才派生的NewName.清空Excel数据,再次运行包,都变成了绿色,执行全部成功通过对比数据库,确实已经成功的添加进了93行新的数据.细心的可能会发现,控制流中的数据导出和导入两个组件其实是前后约束条件的,也就是必须数据导出必须成功了才会执行导入(后面会介绍).另外,所谓的数据导出并不会局限于数据库的导入和导出,数据流源和数据流目标都可以是Excel,Flat File(txt,csv),XML,DataReader等连接.也就是说同样可以实现txt导入Excel,或者是XML导入数据库等操作.好了,今天是SSIS的一个入门,我们利用SSIS实现了数据的导入和导出,把Product表中的数据导出成Excel,然后对产品编号和名称两个字段经过派生的功能进行转换再导入到数据库中,这其中我们认识了控制流和数据流,数据流源和数据流目标,并且还引入了派生列组件来实现我们的导入(这里主要是为了实现导入,有可能是正好产品名称相对1这个字符导致错误).SQL Server BI Step by Step SSIS 3 - 批量导入Excel表上一次我们已经介绍了简单的数据导出和导入,但是只是对单一文件进行操作,如果我们想同时对一个目录下面的所有的文件执行数据导入怎么实现呢?相当简单,SSIS在控制流中提供了Foreach循环容器,很容易理解,它和序列容器相比就是能够循环的遍历执行,能够对指定枚举器的每个成员重复执行控制流:可以看到,Foreach循环窗器遍历文件夹可以通过一些设置或者是正则来匹配,使用 Foreach 循环容器，可以枚举：ADO 记录集行和架构信息文件和目录结构系统、包和用户变量SQL 管理对象 (SMO)首先我们准备一下数据,把上次生成的Excel文件重命名为Product1.xls,新建一个Product2.xls,复制Product1.xls中的ProductID800的行到新建的Product2.xls中(注意两个Excel的Sheet名字一致),然后在我们的SSIS项目中新建一个ForeachInput包,Foreach 循环容器容器进来,双击进行集合设置,在文件夹处指定我们Excel文件所在的文件路径,比如F: ,文件名处写*.xls,这样就能够匹配出所有的后缀名为xls的文件,然后单独执行容器里面的流程,不过在我们的容器中需要去访问每一个Excel文件,我们必须获得每一个枚举变量也就是文件的名称,可以通过设置变量映射实现.如上图,新建变量后,每次找到一个与条件相匹配的文件时，Foreach 循环容器就会将用该文件名填充用户定义的变量.同理,当我们遍历其它对象时,也可以采用变量的方式进行映射,获得遍历到的单个匹配对象.然后我们直接把上一次建的包(OutputAndInput.dtsx)中的数据导入这个数据流组件复制到Foreach 循环容器中(就象复制普通文件一样,呵呵),同样也把连接管理器中的Excel文件连接和数据库连接复制过来.这样我们基本上已经完成了,只不过现在的Excel文件连接还是指定文件,我们只需要和我们的变量currentFilePath绑定上就行了.点击我们的Excel文件连接,在右边的属性中点击Expressions.在属性表达式编辑器里,我们可以对Excel文件连接的所有的属性的值直接用表达式绑定,不仅是Excel文件连接,象普通文件连接,数据库连接,FTP连接同样可以.这无疑提高了灵活性.在这里,我们仅需要绑定其文件路径,在属性里选择ExcelFilePath,然后点击表达式后面的省略号按钮.这里不仅可以直接绑定系统变量和用户变量,而且可以使用脚本组合成表达式.我们只需要选定用户变量currentFilePath.点击计算表达式,只是一个空的字符串,因为此时并没有进行遍历.所以点击后发现Excel Source数据流源组件显示红色的,提示文件并不存在,我们不需要此时对文件进行检测,所以将Excel文件连接的延迟验证DelayValidation属性值设置成True.我们为了防止和数据库里面已经有的数据发生冲突,修改派生列组件,因为上一次我们是ProductNumber和Name加了1,这次我们加2.然后右击包执行,你会发现执行成功,而且是执行了两次数据流(有可能很快看不出来),可以通过对比数据库看出来,已经添加进了两个Excel表里的数据.当然,你可以分成更多个Excel,多少个都没有关系.此处,你也可以通过添加数据查看器进行查看执行过程中的数据,这样可以很方便的进行SSIS包的简单的调试.右击派生列组件和Product目标表中间的绿色,点击数据查看器,然后点击添加,再选择网络,其它全部默认,确定.我们把派生列中改成+3”时再次运行包,你会发现所有的组件都是黄包,这代表正在执行,在弹出来的数据查看器中可以看到所有的数据流的55行数据,点击上面的绿色按钮可以继续运行.通过数据查看器我们可以查看正在数据流中传输的数据.我们在上面的数据查看器中就可以看到新增加的NewProductNumber和NewName两个列是否正确.到现在我们就已经完成了批量导入Excel,本次我们主要学习了Foreach循环容器,并且配合用户变量的使用,以及在使用连接器中的连接的一些简单设置,比如属性动态绑定表达式等,另外还介绍了数据查看器的用法.SQL Server BI Step by Step SSIS 4 -合并数据1我们已经实现了把Excel中的数据导入到数据流目标,也就是数据库中,但是我们只是模拟了不重复的数据,也就是都是数据库中此时不存在的数据.那么如果数据流源Excel中的数据和数据库中的数据的主键相同的情况下,再进行插入操作就会出现异常,怎么才能实现自动的更新操作呢?也就是自动的判断数据源的数据,如果数据库中存在就执行更新,不存在就执行插入呢? 可能叫做合并数据会有些误解,这里的合并数据与数据流中的Merge组件和Merge Join组件直接实现的效果是有差别的,首先我们看看这两个组件. 我们新建一个包,重合名为MergeData,然后在控制流中将上次的ForeachInput包中的组件全部复制过来(包括连接管理器),删除Foreach循环容器,然后修改数据流,添加两个Excel数据流源,分别对其通过Sort进行排序(按照ProductNumber字段),再添加Merge进行数据合并,分别添加三个数据查看器,修改后的数据流图如下: 我们执行一下,三个数据查看器的部分载图如下: 可以看到,Merge数据合并了两个数据源的数据,但是并不直接实现我们想达到的目标,不过注意到Merge有以下两个特点: 1.使用Merge转换之前必须对数据进行排序,可能通过象上面那样使用Sort转换,或者直接在源连接中指定ORDER BY直接完成. 2.合并数据的两个路径之间的元数据必须相同. 3.只能应用于两个数据的合并,如要两个以上,可考虑选择Union All转换 Merge Join组件和Merge组件有什么样的区别呢?我们再新建一个包MergeData2来看下Merge Join组件,两个数据流源分别连接到Product表和ProductInventory表,修改数据流如下: 两个Sort分别按照ProductID字段进行升序排序,Product路径输入流作为左侧输出,Merge Jogin组件设置成左外部连接(左连接,外部,内部连接和SQL一样),并选择需要输出传递到路径的列: 执行过程的三个数据查看器显示的数据如下: 可以看到和SQL的连接一下,将产品的基本信息和产品的库存信息连接在一起组合一起,它的作用就是可以合并两个输入的输出并对数据执行INNER或者OUTER连接,当然,如果两路输入位于同一个数据库中,你直接使用OLE DB数据级别的join操作会更快,但是当你希望合并两个不同的数据源时,Merge Join就可以派上用场了. OK,今天就到这里,我们主要今天主要是熟悉了Merge和Merge Join,但是并没有实现我们上面提出来的直接同步合并数据的需求(不过其实仔细考虑一下,其实再结合其它的组件可以实现).下几次我们将采用几种方式实现.SQL Server BI Step by Step SSIS 4 -合并数据2上次我们并没有实现Excel中的数据与数据库中的数据进行整合,存在即更新,不存在即插入.这次主要介绍几种方法来实现: 1.使用Lookup 2.使用execute SQL task调用存储过程 3.使用script component脚本实现 4.使用MERGE 语句(SQL SERVER 2008) 5.使用上次我们用到的Merge来实现 6.使用第三方组件SCD Component 看起来能够实现的方法确实不少,我们来一一介绍,介绍的同时也会介绍一些组件的应用,同理在我们实现其它功能时,也可以同样使用.Lookup 新建一个包MergeDataLookUp,我们把ForeachInput中的复制过来,我们实现遍历Excel的同时,实现Excel中的数据与数据库中的数据合并,在数据流中,在Excel数据源的下面,删除原来的组件,拖入LookUp组件,选择OLE DB连接后,进行查找的设置: 可以看到,我们是根据Excel数据中的ProductNumber字段去数据库中查找对应的数据(列ProductID),即找到对应的数据时,ProductID会作为新列添加到我们的数据流中,找不到时则会出现错误,点击上面的标出的配置错误输出, 这样对于Lookup的两个输出,正常的输出也就是找到了ProductNumber对应的数据,此时做更新操作.上图中我们已经进行了配置,当某一行没有找到时,我们将这行数据重新定行到错误输出,此时再进行插入操作.我们在正常的输出上添加OLE DB Command,来执行我们的Update语句. 在错误输出上添加我们上次添加过的OLE DB Destination,其中忽略ProductID和rowguid两个字段.整个数据流如下: 到现在我们已经实现了利用Lookup合并数据.2.使用存储过程 使用存储过程实现这个就相当容易的多,不过并不建议这样做,我们把所有的流程都放在了存储过程里面,而不是SSIS包里面,有一个应该考虑的是,一般我们可以在SSIS包里设置成支持事务(设置包或者组件的TransactionOption属性).在存储过程里,我们可以直接采用存储过程里面的事务机制. 我们新建一个包MergeDataProcedure,完成如上个包的设置, 只需要执行一个OLE DB Command,在这里我们调用存储过程,存储过程里我们只是完成根据ProductNumber判断数据存在不存在,存在即执行更新,不存在插入.在这里就不再对这个方法进行详细介绍了.3.使用Script Component 新建一个包MergeDataScript,复制MergeDataLookUp中的控制流和变量,连接器,将Loopup组件换成Script Component组件(添加时使用方法为转换).首先选择输入列,并设置其使用类型: 然后设置输入输出,在这里,有一个输入,然后设置三个输出,输出的列不需要手动配置的,是自动创建的,注意到输入RecordsInput的ID为2778.三个输出的名称分别为UpdateRecordsOutput,InsertRecordsOutput,IgnoreRecordsOutput,将输出的属性ExclusionGroup设置成1,将属性SyncronousInputID值设置成RecordsInput(ID为2778),每个输入控件的SyncronousInputID都是不一样的.设置这两个属性是我们下面脚本运行的关键,具体将查询官方文档. 最后我们设置一下连接管理器,因为我们在脚本中要获取数据库连接,所以在这里我们添加一个连接的名称,注意这里我并没有连接到原来的OLE DB连接,我在脚本中使用了SqlDataReader,此处需要新建一个ADO.NET连接. 设置完以后,我们再切换到脚本,直接设置脚本,打开脚本编辑器,输入如下脚本,然后关闭.确定.ImportsSystemImportsSystem.DataImportsSystem.MathImportsMicrosoft.SqlServer.Dts.Pipeline.WrapperImportsMicrosoft.SqlServer.Dts.Runtime.WrapperImportsMicrosoft.SqlServer.Dts.RuntimeImportsSystem.Data.SqlClientPublicClassScriptMainInheritsUserComponentDimconnMgrAsIDTSConnectionManager90DimsqlConnAsSqlConnectionDimsqlCmdAsSqlCommandDimsqlParamAsSqlParameterPublicOverridesSubAcquireConnections(ByValTransactionAsObject)connMgr=Me.Connections.DBConnectionsqlConn=CType(connMgr.AcquireConnection(Nothing),SqlConnection)EndSubPublicOverridesSubPreExecute()sqlCmd=NewSqlCommand(SELECTNameFROMProductWHEREProductNumber=ProductNumber,sqlConn)sqlParam=NewSqlParameter(ProductNumber,SqlDbType.NVarChar,25)sqlCmd.Parameters.Add(sqlParam)EndSubPublicOverridesSubRecordsInput_ProcessInputRow(ByValRowAsRecordsInputBuffer)DimreaderAsSqlDataReadersqlCmd.Parameters(ProductNumber).Value=Row.ProductNumberreader=sqlCmd.ExecuteReader()Ifreader.Read()Then此处可以根据需要进行字段的比较If(reader(Name).ToString()Row.Name)ThenRow.DirectRowToUpdateRecordsOutput()ElseRow.DirectRowToIgnoreRecordsOutput()EndIfElseRow.DirectRowToInsertRecordsOutput()EndIfreader.Close()EndSubPublicOverridesSubReleaseConnections()connMgr.ReleaseConnection(sqlConn)EndSubEndClass 上面的脚本的具体含义也就不再详细介绍,比较容易理解,其实与我们使用LookUp实现的功能相同,使用ProductNumber进行查找Name字段,如果找到Name则跳转到更新输出,否则跳转到忽略输出,如果没有找到,则跳转到添加输出.我们也可以直接把添加,更新这些操作放在脚本里面.不过为了使整个流程更加清晰,我们只是使用脚本进行了一个转换.不过其实脚本实现的会更加灵活,这里其实还可以实现双向查找或者是更加复杂的功能. 我们在Script Component后面添加对应的输出,其中UpdateRecordsOutput,InsertRecordsOutput和使用LookUp一样.不过我们对于IgnoreRecordsOutput输出我们添加一个RowCount进行统计.执行包,完成了数据的更新和添加.好吧,有些困了,今天就只介绍这三种方法的实现,下次再简单的介绍另外三种方法.SQL Server BI Step by Step SSIS 4 -合并数据3我们已经介绍了三种方法来实现数据流源和数据库数据的合并,也就是Excel中的数据和数据库里面的数据进行同步,存在的数据进行更新操作,不存在的添加数据,另外还可以扩展为,如果数据库为的数据在Excel数据源中不存在,则执行删除操作.接下来我们来介绍其它的方法来实现这样的目标. 我们在合并数据1中曾经介绍过,直接通过Merge和Merge Jone是无法直接实现的,不过我们可以通过变通的方法实现.首先我们新建一个包MergeDataMerge,像之前一样,象上次一样,准备控制流,变量和连接管理器.其实前面和合并数据1中的MergeData2一样,将Excel数据源和数据库表数据根据ProductNumber字段进行合并. 这里我们选的是完全外部连接,这样才能完全合并两边的数据.当一方数据没有匹配的数据时,有NULL代替.所以我们直接根据合并后的ProductNumber和NewProductNumber 就可以直接这行数据应该对应的是添加,更新,还是删除.在Merge Join组件下面,我们添加Conditional Split组件,这个组件来判断对于不同的情况的分支: 很简单,当行中对应的ProductNumber为NULL时,说明没有从数据库时面查到匹配的数据,所以这条数据应该进行新增操作,当NewProductNumber为NULL时,说明Excel数据源中不存在数据库中ProductNumber对应的数据,所以此条数据应该进行删除,最后,如果两个ProductNumber相同,则应该进行更新操作.这里需要注意的是,一定要把两个判断ISNULL的条件放在前面,因为我们在第三个里面用到了TRIM,如果为NULL值时,会有错误发生. 接下来我们介绍一下使用第三方组件SCD Component怎么来实现这个,SCD Component是一个开源的SSIS数据流组件,它的主页是/ 首先从上面下载对应的版本,并且按照安装提示进行安装.安装完以后,就可以象其它组件一样简单的使用了.我们先建一个包MergeDataSCDComponent,同样,准备好其它的资源,然后拖动Slowly Changing Dimension组件进来,双击这个控件进行设置,我们会发现,这类似于一个安装程序,可以一步一步的设置,点击下一步,首先我们需要选择数据连接管理器,然后再设置业务键,在这里我们设置ProductNumber列作为业务键: 再点击下一步,在这里我们可以选择数据源中的列,然后设置此列的更改类型,分为固定,变化还是历史,在左面已经列中不同的类型的详细说明: 可以看到这个组件很强大,可以针对不同的更改类型做出这么多的区分,我们再点击下一步,看到在这里可以设置,如果固定类型的属性更改时是否转换失败,变化的属性更改时,是否更改所有的匹配记录. 我们直接下一步,然后完成整个配置,可以看到,Slowly Changing Dimension下面多出来了三个分支,就象我们前几次实现的一样,只不过这次是全部配置完成的.虽然下面的其中的两个组件名显示的是英文(估计是没有对应的中文翻译),不过还是相应强大的. 我们来看一下插入目标这个组件,自动生成的只是一个OLE DB目标组件,然后已经把字段映射好了,而且对于一些特殊的列已经自动进行了忽略(考虑的还真周到).其它的两个更新的操作,都是生成的OLE DB Command组件,而且自动生成了SQL语句. 我们运行一个这个包,发现能够自动的判断数据是否更新,还是插入: 好了,到现在为止,合并数据就介绍完了. SQL Server BI Step by Step SSIS 5 - 通过Email发送忙了一段，终于有时间来完成这一系列了。sql server 2008正式版已经发布了，接下来的系列都将基于sql server 2008+ 2008开发。引言在一个B2B项目中遇到这样的一个情况,每天老板都想看到所有的新的订单信息，而这个老板很懒，不想登录系统后台，而是想通过查看邮件的方式。当然实现方式很多，这里我们介绍一个怎么使用SSIS包来实现这样的一个功能。使用SQL XML Query查询出XML结果数据，然后使用Xsl模板将xml数据转换成html，再将html内容发送邮件。创建SSIS包1.和 20005中一样的方式，创建一个Integration Service项目，并且包的名称修改为SendMailPackage2.在你的本地硬盘上新建一个目录,比如F:SSIS_ExampleSendMail 用来存放查询语句和Xsl文件.3.在刚才的目录下新建一个QueryOrder.sql文件，填充如下内容，在连接管理器中新建一个文件连接，选择新建的sql文件:隐藏行号 复制代码 ？ QueryOrder.sql/*实际使用时将v_CurrentDate换成当时日期*/declarev_CurrentDatedatetimesetv_CurrentDate=2003-07-17/*setv_CurrentDate=GETDATE()*/ifexists(select1fromSales.SalesOrderHeaderwhereOrderDate=v_CurrentDate)beginselecttop10oh.OrderDate,(selectround(sum(TotalDue),2)fromSales.SalesOrderHeaderwhereOrderDate=v_CurrentDate)asDayTotal,p.ProductID,p.Name,round(sum(oh.TotalDue),2)asProductSubtotalfromAdventureWorks.Sales.SalesOrderHeaderohjoinAdventureWorks.Sales.SalesOrderDetailodonod.SalesOrderID=oh.SalesOrderIDjoinAdventureWorks.Production.Productponp.ProductID=od.ProductIDwhereoh.OrderDate=v_CurrentDategroupbyoh.OrderDate,p.ProductID,p.Nameorderby5desc,p.ProductIDascforxmlauto,elements,type,root(Order)endelseselectcast(Nosalesrecordsavailableforthisdate.asxml)注: 在这里我们使用一个固定的日期，实际中你可以使用系统方法GETDATE()或者DATEDIFF()来查询你需要的日期。4.同样在目录下面新建一个Orders.xsl文件，由于内容较多，请直接下载附件中文件，这里也不在详细介绍xsl的具体内容。 同样也新建一个文件连接。5.在目录下新建一个Orders.htm文件，用来存放生成的html内容，同时也新建一个文件连接。6.新建一个A 连接你的AdventureWorks数据库。现在你的连接管理器有如下连接:7.创建如下几个变量，作用域为当前包SendMailPackage.名称 数据类型 值 varSalesSummaryXML String varSalesSummaryHTML String varMailTo String 你的接收Email地址 8.在你的控制流中添加一个执行SQL任务,并设置sql类型为文件的方式，并选择我们的文件连接:9.切换到结果集，点击添加，结果果名称为0,变量选择varSalesSummaryXML10.在控制流中添加一个XML任务，将刚才的SQL任务的绿箭头指向XML任务，设置属性如下:为了测试结果，上面我们将输入结果设置为保存文件的方式，先执行一下包，测试一下采用xml+xslt的方式生成的html的内容是否正确:注意，现在我们再把输出结果改为变量的方式，设置DestinationType属性为变量的方式，并选择varSalesSummaryHTML11. 在XML任务下面添加一个脚本任务来执行发送邮件，之所以不直接采用SSIS的发送邮件任务，是因为发送邮件任务目前不支持html内容.进行脚本任务的属性设置:可以看到，脚本任务的设置相对于2005的版本发生比较大的变化，脚本语言终于开始支持了C#，原来的PreCompileScriptIntoBinaryCode属性不存在了，所有的脚本都要进行预编译，另外ReadOnlyVariables和ReadWriteVariables变量的设置都可以通过后面的选择对话框直接选择。这些改进方便了许多，设置完成后，我们点击编译脚本就可以起用用我们熟悉的C#来完成发送邮件的操作了.注意我们需要引入System.Net.Mail，然后通过SmtpClient来通过指定的邮件服务器发送邮件，可以指定邮件帐号和密码，而SSIS的发送邮件任务是不支持非windows论证的。隐藏行号 复制代码 ？ QueryOrder.sqlpublicvoidMain()SmtpClientsmtpClient=newSmtpClient(邮件服务器地址,25);stringbody=Dts.VariablesvarSalesSummaryHTML.Value.ToString();stringaddress=Dts.VariablesvarMailTo.Value.ToString();MailMessagemm=newMailMessage(发送地址,address,OrderDetail,body);mm.IsBodyHtml=true;smtpClient.Credentials=newSystem.Net.NetworkCredential(帐号,密码);smtpClient.Send(mm);Dts.TaskResult=(int)ScriptResults.Success;12. 到现在为止已经完成了功能，直接执行包，就可以直接收到订单列表了，但是要想让这个包定期的执行，需要部署这个SSIS包，然后在数据库job中定义执行此包.右击当前的项目，设置CreateDeploymentUtility属性为True，这样能够自动生成部署文件.重新编译此项目，在bin目录下面Deployment文件夹，双击下面的后缀名为SSISDeploymentManifest文件，启动包安装向导，在这里我们不详细介绍，直接下一步，直到完成.13. 打开你的SSMS，确认已经启动Sql Server代理，新建作业，步骤里面设置执行我们刚才的SSIS包，再把计划设置为你想要的时间即可，这里就不再详细介绍。参考/articles/Integration+Services+(SSIS)/62678/SQL Server BI Step by Step SSIS 6 - 获取远程主机的SSIS不仅是一个ETL工具，其实它的功能非常强大。就拿它的WMI Data Reader任务和Event Watcher任务来说,甚至可以获取很多操作系统的信息。Windows管理规范(Windows Management Instrumentation)是Windows中的“最高机密”之一. WMI通过WQL查询，可以来完成以下应用: 1.读取系统事件日志来查找某个特定的错误2.查询运行中的应用程序列表3.查询在程序包执行期间有多少内存可用调试4.确定磁盘上的空余磁盘空间当然，这些应用你也可以通过C#调用WMI来实现。不过使用SSIS，你可以结合这些应用，与数据库的操作，数据转换和集成联系起来，能够发挥更强大的作用。需求：需要获取远程服务器的磁盘可用空间大小.实现:1.首先需要确定的是，查询远程电脑的磁盘大小，必须具有这台主机的管理员权限，或者能够以管理员的身份来运行包.2.新建一个SSIS包，名称为WMI_Query,在控制流中添加WMI数据读取器任务,双击对期属性进行设置:WMIConnection直接新建的链接，输入主机名称，用户名和密码即可，当然，在这里也可以采用动态连接的方式，那样需要直接改变WMIConnection的连接字符串.WQL的输入方式为直接输入，这里也可以使用变量名教文件输入。查询磁盘空间的语句为:SELECTFreeSpace,DeviceId,Size,SystemName,DescriptionFROMWin32_LogicalDiskwhereDriveType=3WMI中DriveType是指驱动器类型, 0代表未知，1代表可移动，2代表固定，3代表网络，4代表CD-ROM，5代表RAM磁盘，我们设置目标的方式为变量，并且直接新建一个字符串的变量DiskUsageResults.3. 为了方便查看，我们可以添加一个脚本任务来输出这个变量，直接弹出窗口来显示磁盘信息:publicvoidMain()/TODO:AddyourcodehereMessageBox.Show(Dts.VariablesDiskUsageResults.Value.ToString();Dts.TaskResult=(int)ScriptResults.Success;运行包，我们可以直接看到远程机器上面的所有的磁盘使用情况结论: 可能这种功能能够派上用场的时候比较少，但是SSIS提供的功能已经涵盖非常广，在很多问题的解决方案中能够成为一种选择。SQL Server BI Step by St