「数据ETL」从数据民工到数据白领蜕变之旅（四）-有了PowerQuery还需要SSIS吗？

在真正的企业级BI项目中，使用PowerBIDeskTop那一套数据ETL是不可行的，需要使用专业的ETL工具完成数据仓库的搭建，再进行数据建模的工作。 鉴于笔者所能触达的读者群体，多数为业务背景的数据分析工作者，本篇给大家带来简单的入门实操演示，让大家减少对专业IT型工具的恐惧心理。 所有工具都是为人所用，都是想着尽可能简单易上手的，学习过PowerQuery的群体，笔者很有信心只需跨出信心的一步，SSIS掌握到够用的级别还是很容易实现的。

PowerQuery的局限性

作为一款自助式BI的轻量ETL工具，PowerQuery的确可以让我们享受许多数据处理的便利，无需专业的能力，大部分仅需通过界面的操作即可完成，无可否认PowerQuery的使用体验是非常棒的。

甚至很多在SSIS这样专业级ETL工具上实现起来繁琐的任务，在PowerQuery上可以非常流畅地完成如逆透视，简单网页抓取，空值填充，行列转置等。

但PowerQuery的局限性也是非常明显的。

首先，它的性能是非常容易出现瓶颈的，虽然数据处理逻辑很清晰，但就是要忍受漫长的等待时间，很多时候，数据量级别稍大一些，单单这点就要否决使用PowerQuery方案。

其实，对某些数据ETL它是有缺陷的，例如不能扩展性地使用正则表达式处理字符串数据；

最后，它很大的弊端是目标数据只能进入到PowerPivot层面，不能回到关系型数据库这样更友好的数据存储区，数据处理好，只能在数据模型上消费，不能有其他用途产生。

同样地这样的结果，将失去了一大片江山，不能使用SQL语句来对数据进行更进一步的清洗、整合。SQL语句是基于行集处理方式，并且有窗口分析函数的性能保障，在数据ETL过程中发挥着非常大的作用，一般能够在SQL上处理的优先在SQL上来满足，保证性能的优势。

当然还有许多领域PowerQuery是缺陷的，例如增量更新机制，更为复杂的缓慢变化维的处理，重新生成数据仓库的代理键替代原有的业务主键等功能。

SSDT安装简介

在前面的Tabular Editor系列中，笔者提到是丢弃SSDT转向Tabular Editor，此处又回来说重新使用SSDT？

是的，SSDT是整个企业BI的开发工具，包含了对SSIS、SSAS、SSRS三大模块的开发，Tabular Editor仅能替代SSAS部分。

SSIS学习资源

SSIS的学习，类似于Excel、PowerQuery的学习一样，因其操作类的步骤较多，更为直观的是视频化的讲解，可以到YouTube上可以找到有老外的免费的系列教程（看了人家老外的课程再对比国内的，就知道什么叫知识付费式收割了）。 https://www.youtube.com/watch?v=Td97JdNUujg&list=PLWf6TEjiiuIDUhRIhBSuJgHOggAR_SZsQ

同时微软官方的文档也提供了非常详尽的资料，不过初学看起来比较吃力，当作文档查考一下还可以。

https://docs.microsoft.com/zh-cn/sql/integration-services/sql-server-integration-services?view=sql-server-2017

其入门教程，笔者看了下，还是偏IT化语言，不是太容易看下来，截图少，更难理解。

https://docs.microsoft.com/zh-cn/sql/integration-services/integration-services-tutorials?view=sql-server-2017

同样地，真正要系统学习SSIS，建议还是需要备一本红皮书，通读一遍，在实战中有问题时再回头翻阅，比在网络上找答案要系统完整（本书中文版网络上已无法找到纸质书，笔者有电子版收藏，可后台回复【SSIS】获取）。

开始第一个SSIS项目

安装好之后的程序入口，可能要选择安装SSDT2015比较合适，SSDT2017笔者安装过好多轮都是出错。

进来后，生成一个叫包的东西Package.dtsx，后续所有的操作，都围绕着往这个包里填充控件逻辑。

在左上方可看到，当前是控制流的位置，而SSIS工具箱里的控件都是在控制流里使用的，因其是近乎万能级别的ETL工具，所以非常多的任务可用，我们一般只用到上方的【执行SQL任务】和【数据流任务】两种为主。其他只会在特定的任务场景上才会使用。

控制流和数据流的区别，用笔者语言来说是控制流是类似我们写程序的一个函数、过程任务片段，完成一件数据单元的任务，而数据流，是指控制流中涉及到数据的转换处理的加工过程，就像一个管道一样，从控制流的起点开匝放水，水流经过整个数据流的过程，最终流出回到控制流的流程中。

控制流中的数据流任务，可以再嵌套一个循环结构的容器，就变成批量执行某个数据流任务单元了，例如抽取某个文件夹下的所有Excel文件数据到数据库中，使用循环容器，就可以将任务分解成循环执行【Excel文件抽取数据到数据库】这样一个数据流任务，最终实现文件夹内所有Excel文件都抽取到数据库中。

Sqlserver的导入导出任务在SSIS上复现

前面的Sqlserver系列的文章中，曾经演示过导入导出的任务，其实底层就是用SSIS的数据流任务来完成，以下简单演示下Excel数据到Sqlserver数据库表的过程。全过程都是界面化操作，拖拉组件即可完成，非常易上手。

首先，拖一个数据流任务出来。

双击数据流任务，或直接切换到数据流选项卡中，来到数据流任务的设计界面。

一个数据流任务中，一般有有种类型的组件，分别完成E（Extract抽取 源组件），T(Transform 转换组件)，L（Load 目标组件）。

首先拉一个源组件，连接Excel文件。

同样使用双击的方式，打开Excel源的详细设置，如Excel源的连接信息，抽取哪个表数据等，同样可以使用此界面的【新建】按钮，直接创建一个数据源连接信息。

选择好Excel文件的路径信息即可完成连接信息的创建。

有了连接信息后，就可以读取到此Excel文件的架构，然后可以直接选取需要读取哪个Excel工作表即可（当然此步一样可以写SQL查询，查询此Excel文件的内容，用Excel直接的SQL语法进行操作，通常必要性不大，在Excel里存放的数据全量抽取到数据库中，再作处理更为轻松）。

若需调整表内的字段信息如增减字段和字段的重命名等操作，可以跳到列选项卡中进行操作，反正所有一切，都可界面完成，无需写SQL语句。

image.png

企业级的产品，最强大之处在于其稳定性的保障，处理错误的能力也是非常必要，我们永远需要假设我们上游给到的数据是不干净大概率有异常情形的如数据类型不对。

所以【错误输出】这里可以更进一步去处理发生错误时应该怎么做，是直接报错中止，还是忽略错误，而错误的产生甚至可以颗粒度到哪个字段产生而使用不同的错误处理对策。在练习阶段，这些都可以先默认设置，日后回到头来再细细地对照着文档研究其中的细节。

数据源的加载环节已经做完，我们简单做一个转换操作的演示，增加一列数据的加载时间，方便日后数据审核复查时，知道数据是什么时候抽取的。

点击上面的源任务，出现两条箭头，蓝色的代表此组件执行成功后的下一步操作指向，红色指向执行失败的导向。将蓝色箭头拖到下方的【派生列】组件即可。

连接好的效果。

因数据流任务里的数据管道的概念，现阶段管道里的内容是Excel表的数据，列字段是源里抽取后得到的结果，所以在派生列里，其实可以对上游的列字段进行识别，进行简单的计算转换如单位转换，计算转换如生成金额列=单价*数量等。

本次只生成一个时间戳的字段，无需依赖于上游的字段，直接用SSIS里的内置函数得到，同样地拖拉一下函数即可。生成的新列，甚至可以替换原来列的内容，或作为新列添加。

在SSIS里，支持OLEDB的数据源与目标，Sqlserver使用OLEDB的数据驱动去连接，兼容性会更好，一般推荐使用它而不是Sqlserver的原生驱动Native Client。

此处若不太知道目标源怎么选，甚至还可以用目标源助手，再来一次向导式的引导，当然源也一样可以，熟练后一般都不会再用向导操作。

同样地我们利用【新建】按钮，直接跳转到创建目标的数据连接。

同样地，SSIS已经自动帮我们按源的数据类型和字段名称，生成了SQL语句用来创建目标表（若是已经有现成表，直接选择即可，会将源数据直接插入到目标表中存放，怎样避免重复插入及插入数据去重等，就需要一些进阶的用法，后续再分享）。

同样地转到【映射】选项卡中，可以看到SSIS自动帮我们创建好对应的列匹配关系，若源和目标的字段名称不同，需要手动去在输入列与目标列中做匹配映射调整。

重新回顾我们所做的数据流，没有错误提示，即代表成功了。

同样地我们模拟了一下【控制流】的任务清单，给大家再次感受下两者的差异（实际情况更好的处理方式是每个数据流的任务，单独建一个包，而不是一个包执行多个数据流任务，后续再分享细节）。

最后一步大功告成，我们要享受我们的开发成果，可以执行此包或此数据流任务（数据流任务可以单独执行，方便调度，包的执行就是包有控制流任务都一起生效，单个任务流组件执行，仅对此组件的任务生效）。

执行完好，我们可以切换不同的选项卡看一下不同的结果，因此次只执行了一个数据流，比较简单，复杂的【任务流】可以在进度选项卡中看到更丰富的执行过程日志。

来到数据库中查看，可看到我们目标表中，多出一列加载时间。源数据按预期加载完成。

因现在是测试模型，执行完，需要中止回到设计模型才可以进行修改。

再执行一遍，可发现数据已经重复生成了多一份副本，所以我们刚刚的控制流，需要再做其他的任务控制，先删除清空目标表的数据，再进行源数据的抽取加载到目标表，这些后续再给大家做完整的演示。

结语

本篇简略分析了PowerQuery的一些局限性，建议有PowerQuery的使用经验的群体，可以再往前一步，加入到SSIS的阵营中来。

结合之前的Sqlserver和Azure的系列推文，将这些能力整合起来，就可以由业务分析者去主导真正的企业级BI，从部门级别的应用慢慢地反推整个企业级的应用，由甲方人员推动的BI项目，才能够走得更远，做得更合乎实际使用并且可扩展性更强，持续产出更大。

笔者未来聚焦在数据领域的分享，不限于Excel，会分享更多Sqlserver、dotNET、Azure、PowerBI等话题，升级数据分析的能力，欢迎继续关注。*

系列文章

从数据民工到数据白领蜕变之旅（一）-工具总览 https://www.jianshu.com/p/2bd3f90206ec 从数据民工到数据白领蜕变之旅（二）-重温Excel催化剂经典 https://www.jianshu.com/p/cb89929bb8ae 从数据民工到数据白领蜕变之旅（三）-除了Excel催化剂之外PowerQuery新物种同样值得期待 https://www.jianshu.com/p/d154b09c881d