微软BI 之SSIS 系列

zhouying23

开篇介绍
　　由于项目需要我们会从SharePoint 上读取一些配置数据，同时也有可能执行一些回写操作去更新SharePoint 的数据。之前没有做过这样的操作，有的也应该是通过 C# 编程去获取或者写入一些数据。查阅了一些相关的文章，自己也动手测试了一下如何在 SSIS Package 中访问SharePoint List 并将数据写入 SharePoint List。
　　两个操作

• 读取SharePoint List 的数据到数据库
  
• 从数据库中筛选一些数据然后插入到一个新的 SharePoint List 中
  

　　这些操作不涉及到任何SharePoint 能够实现的功能，仅仅只是演示如何通过 SSIS 来完成这些操作， 也无关这个读写逻辑是否合理了。

实现过程
　　步骤一 – 安装 SharePoint List Source and Destination
　　由于SSIS 是没有SharePoint 操作的控件的，但是目前已经有比较成型的第三方工具可以使用。
　　到 CodePlex 中去下载这个控件 http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652
                    
　　我目前的测试环境是 VS 2008，我下载的是第一个，应该也能兼容到 VS 2010。如果是 2012 版本，应该可以使用第二个 2012 的 Beta 版本，其安装和操作的过程大同小异。
　　下载之后安装，然后打开 VS 2008，在项目中 Data Flow Source 工具栏或者其他工具拦点击右键选择 Choose Items 后就会出现这个界面。

　　如果是VS 2010 或者 VS 2012 有可能不需要我们手动的添加这两个控件，也有可能连 SSIS Data Flow Items 可能都找不到。那么很有可能就是在安装的同时，这两个控件就已经自动的添加到 Data Flow 的工具箱了。
　　VS 2008 还是这样来选择一下，然后就能在工具箱看到这两个控件了，一个是Source，一个是Target。
　　默认情况下它们会出现在 General 下面。

　　你可以按照分类把 SharePoint List Source 拖放到 Data Flow Sources 和 Data Flow Destinations 中。

　　这样第一步配置和安装的过程就算是完成了。
　　步骤二 –  读取 SharePoint List 的数据到数据库表

　　这里有一个List – Area Code，现在我们要把这个List 中的 Area Code，Region，Timezone Offset 还有 Description的内容读取到数据库中。
　　在设计 SSIS 的阶段也可以根据数据源的数据来创建表，但是最好根据实际需求先创建好表，结构如下-



USE BIWork
GO
IF OBJECT_ID('dbo.Area_Code','U') IS NOT NULL
DROP TABLE dbo.Area_Code
GO
CREATE TABLE dbo.Area_Code
(
ACID INT PRIMARY KEY IDENTITY(1,1),
AreaCode NVARCHAR(10) NOT NULL,
Region NVARCHAR(10),
TimeOffset NVARCHAR(10),
Descript NTEXT
)
　　创建 SSIS 项目和Package 包，并在 Control Flow 中添加一个Data Flow，命名为 Extract SharePoint List。

　　在这个Data Flow 中，添加 SharePoint List Source。

　　在编辑SharePoint List Source 之前先要创建一个 SharePoint Connection Manager。注意 – 不同的版本可能有一些区别，有的版本不需要 Connection Manager 没有这一项。
　　在 Connection Manager 中 New Connection 选择 SPCRED就可以了。

　　顺便把数据库的 Connection Manager 都创建好，创建完的结果应该是。

　　一个是 BIWORK 数据库的连接，另外两个都是SharePoint 的连接，后面都会用到。
　　下面就要对 SharePoint List Source 进行编辑。
　　在 SharePoint List Source 的Connection Manager 中选择好刚创建的连接。

　　Component Properties 中 有几个地方要注意。

　　由于在 SharePoint 中访问List 的URL 是http://biwork/Lists/Area%20Code/AllItems.aspx
　　因此在填写 SiteURL 的时候就只应该填写 Lists 之前的部分http://biwork
　　在 SiteListName 中填写上需要访问的List 名称，在这里我们访问的是 Area Code。
　　不需要的Column 直接删除掉，只保留需要的Column，这样可以提高效率。

　　就点击保存，那么这样 SharePoint List Source 配置就完成了。
　　添加一个 OLE DB Destination。

　　Mapping 的选择。

　　保存后执行Package并查询数据库结果。


　　Description 中有其它的 HTML 标签，应该是在设置 SharePoint List 的时候 Description Column 的类型是 Multiple line of Text 因此对应的数据库 Descript 类型也是 NTEXT 的，其它单行的数据就没有出现HTML 标签。
　　步骤三 – 将数据库中的数据导入到新的SharePoint List

　　创建一个新的List，并查询数据库中 Time Offset 为-5的数据插入到这个新的List 中 – New Area。
　　创建 Data Flow Task 并且在 OLE DB Source 中使用查询语句筛选出 Time Offset 为 -5 的数据。



SELECT AreaCode,
Region
FROM dbo.Area_Code
WHERE TimeOffset = -5

　　创建 SharePoint List Destination。

　　Component Properties 配置。

　　SiteListName – New Area
　　SiteURL – http://biwork
　　Input Column 中应该要刷新一下才能看到 Mapping 信息。

　　Insert Into SharePoint List Data Flow

　　执行Package 包

　　检查 SharePoint List  - New Area 添加成功

　　这样就完成对 SharePoint List 的读取和写入操作了。
　　除此之外还可以做删除 更新等操作，如有需要请参看其它文章。

• http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652
  
• http://msdn.microsoft.com/en-us/library/hh368261.aspx
  
• http://dataqueen.unlimitedviz.com/2011/06/how-to-use-a-sharepoint-list-as-a-data-source-in-your-ssis-package/
  

　　PS：最后补充一下，关于在 Visual Studio 2010 和 Visual Studio 2012 版本中这个插件都是安装完了重启一下 VS 就可以了。并且 SharePointList 组件是属于数据流的，在控制流中是看不到的。
　　以下我的 Visual Studio 2010 版本中的 SharePoint List Source 和 SharePoint List Destination。

　　以下我的 Visual Studio 2012 版本中的 SharePoint List Source 和 SharePoint List Destination。

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server) 如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。