李勝蘭　上海鐵路局金華車務(wù)段

1　背景

隨著鐵路信息化的迅速發(fā)展，各業(yè)務(wù)部門都設(shè)有專屬的信息系統(tǒng)，業(yè)務(wù)內(nèi)部逐漸開始信息共享，如清算運統(tǒng)一可以從十八點系統(tǒng)和TMIS系統(tǒng)獲取數(shù)據(jù)；但不同業(yè)務(wù)之間的信息系統(tǒng)是異構(gòu)的，各系統(tǒng)的數(shù)據(jù)存儲分散獨立：客票用的是Sybase數(shù)據(jù)庫且與我們辦公網(wǎng)網(wǎng)絡(luò)物理隔離，管內(nèi)的運營數(shù)據(jù)分布在車務(wù)和貨運中心的兩臺Oracle服務(wù)器上，運輸中轉(zhuǎn)數(shù)據(jù)分布在金東站和中小站兩臺Oracle服務(wù)器上，形成一個個信息孤島。對于車務(wù)管理崗位來說，最好能把管內(nèi)各業(yè)務(wù)情況同時一覽無遺，即一張報表總覽客運的、貨運的、中轉(zhuǎn)停留情況等數(shù)據(jù)，從全局分析、判斷、做決策。

那么如何集成這些信息孤島中所需的數(shù)據(jù)？如何在多個異類數(shù)據(jù)存儲區(qū)中提取數(shù)據(jù)并合并到單個一致的數(shù)據(jù)集中？這是開發(fā)我們的車務(wù)數(shù)據(jù)分析系統(tǒng)的關(guān)鍵所在。

之前，本單位曾經(jīng)做個一個信息共享系統(tǒng)，它定時用第三方軟件PowerBuilder的數(shù)據(jù)管道工具來收集各個異構(gòu)數(shù)據(jù)存儲區(qū)的數(shù)據(jù)，并全部復(fù)制存儲在本地數(shù)據(jù)庫。這樣大規(guī)模地多次重復(fù)復(fù)制，既占用網(wǎng)絡(luò)帶寬又浪費本地存儲資源；加上PB近年來已經(jīng)基本淘汰；且也成為新的64位服務(wù)器高性能的瓶頸。所以，找出一種新的解決方案勢在必行。

2　ETL技術(shù)

充分整合各種需要的數(shù)據(jù)資源進(jìn)行再構(gòu)建并呈現(xiàn)，是信息系統(tǒng)建設(shè)一個重要領(lǐng)域。技術(shù)實現(xiàn)上，ETL（Extract,Transform,Load）抽取、轉(zhuǎn)換及裝載技術(shù)是一種方法。

不同業(yè)務(wù)系統(tǒng)不同時期的系統(tǒng)之間數(shù)據(jù)模型不一致，各個異構(gòu)存儲中抽取出來的數(shù)據(jù)在集成前必須進(jìn)行轉(zhuǎn)換后才能裝載，這就需要應(yīng)用ETL轉(zhuǎn)換。常用的轉(zhuǎn)換有以下種：

（1）空值處理：捕獲字段空值，進(jìn)行加載或替換為其他含義數(shù)據(jù)，并可根據(jù)字段空值實現(xiàn)分流加載到不同目標(biāo)庫。

（2）規(guī)范化數(shù)據(jù)格式：實現(xiàn)字段格式約束定義，對于數(shù)據(jù)源中時間、數(shù)值、字符等數(shù)據(jù)，可重新自定義加載格式。

（3）拆分?jǐn)?shù)據(jù)：依據(jù)業(yè)務(wù)需求對字段可進(jìn)行分解，提取字段某部分信息。

（4）驗證數(shù)據(jù)正確性：利用Lookup及拆分功能進(jìn)行數(shù)據(jù)驗證。

（5）數(shù)據(jù)替換：對于因業(yè)務(wù)因素，可實現(xiàn)無效數(shù)據(jù)、缺失數(shù)據(jù)的替換。

市場上，ETL產(chǎn)品有微軟的SSIS（SQLServerIntegration Services）、Informatica 的 PowerCenter，SAPBusinessObjects DataIntegrator等等。其中SSIS是一種高性能的數(shù)據(jù)集成解決方案，它可以連接各種各樣的數(shù)據(jù)源，包括各種關(guān)系型數(shù)據(jù)庫及文檔數(shù)據(jù)庫。我們的車務(wù)數(shù)據(jù)分析系統(tǒng)是基于.NET框架，數(shù)據(jù)庫管理工具采用的是SQLServer，所以SSIS的確是個不錯的選擇。下面將詳細(xì)介紹SSIS。

3　SSIS介紹

SSIS（SQLServerIntegrationServices）就是 SQLServer集成服務(wù)的簡稱。安裝SQLServer時勾選上SSIS，就有SSIS開發(fā)工具。

SSIS包括數(shù)據(jù)集成的一條龍服務(wù)：有用于提取和加載數(shù)據(jù)的數(shù)據(jù)源和目標(biāo)；用于清理、聚合、合并和復(fù)制數(shù)據(jù)的轉(zhuǎn)換；用于對 IntegrationServices對象模型編程的應(yīng)用程序編程接口 (API)，以及用于生成和調(diào)試包的圖形工具和向?qū)В挥糜趫?zhí)行工作流函數(shù)（如 FTP操作）、執(zhí)行 SQL語句或發(fā)送電子郵件的任務(wù)等。

SSIS可以連接到各種各樣的數(shù)據(jù)源。可以使用 .NET和OLEDB訪問接口連接到關(guān)系數(shù)據(jù)庫，還可以使用 ODBC驅(qū)動程序連接到多個早期數(shù)據(jù)庫。包還可以連接到平面文件、Excel文件和 AnalysisServices項目。

SSIS包含一些源組件：內(nèi)置任務(wù)、容器、轉(zhuǎn)換和數(shù)據(jù)適配器，您無需編寫一行代碼，就可以創(chuàng)建 SSIS解決方案來使用ETL和商業(yè)智能解決復(fù)雜的業(yè)務(wù)問題。這些組件負(fù)責(zé)從所連接的數(shù)據(jù)源中的平面文件、Excel電子表格、XML文檔和關(guān)系數(shù)據(jù)庫中的表及視圖提取數(shù)據(jù)。再用SSIS包含的轉(zhuǎn)換功能對數(shù)據(jù)進(jìn)行轉(zhuǎn)換。數(shù)據(jù)轉(zhuǎn)換為兼容格式后，就可以將其物理合并到一個數(shù)據(jù)集中。這個數(shù)據(jù)集可以是平面文件、原始文件和關(guān)系數(shù)據(jù)庫時所用的目標(biāo)。數(shù)據(jù)也可以加載到內(nèi)存中的記錄集中，供其他包元素訪問。

4　技術(shù)實踐

我們利用SSIS來實時增量抽取業(yè)務(wù)數(shù)據(jù)，保證業(yè)務(wù)數(shù)據(jù)的快速和時效性。具體過程為建立序列容器，并且添加數(shù)據(jù)流任務(wù)，TMIS數(shù)據(jù)定時為每 5分鐘增量抽取一次，18點統(tǒng)計數(shù)據(jù)每日七點后增量抽取一次。

SSIS的工作區(qū)見圖1。

圖1　SSIS設(shè)計界面圖

4.1　構(gòu)成

SSIS最基本的組成是包，每個包主要由以下幾部分構(gòu)成：

（1）連接管理器，位于圖1的中下方。

（2）控制流任務(wù)（ControlFlowTasks）及容器（Container）等，位于圖1的中間主窗口。多個控制流任務(wù)可以放在一個容器內(nèi)，可以以容器為單位測試執(zhí)行情況。

（3）數(shù)據(jù)流的源、轉(zhuǎn)換、目標(biāo)處理任務(wù)(DataFlowTasks)等，雙擊控制流任務(wù)可以進(jìn)入具體的數(shù)據(jù)流設(shè)計頁面，圖1左邊的工具箱也換成數(shù)據(jù)流相關(guān)的工具（如圖2）。

圖2　數(shù)據(jù)流設(shè)計界面圖

（4）語法：變量、表達(dá)式、單/多分支、循環(huán)、連接線、作用域等，在數(shù)據(jù)流設(shè)計中具體應(yīng)用。

（5）日志提供程序。SSIS包含日志記錄功能，可在包或各個任務(wù)以及容器中添加日志記錄功能來監(jiān)控包執(zhí)行情況以及進(jìn)行故障排除，可選擇我記錄的時間，也可以對單個包創(chuàng)建多個日志。

4.2　具體應(yīng)用步驟

（1）新建項目和包：在VisualStudio中創(chuàng)建一個IntegrationServices項目，也可在SQLServer2008中打開SSIS并新建項目。在該項目中創(chuàng)建包，后綴名為.dtsx。

（2）建立連接管理器：在連接管理器中，添加、配置外部數(shù)據(jù)源新建數(shù)據(jù)源，即建立到各業(yè)務(wù)的多個數(shù)據(jù)庫的連接，TMIS數(shù)據(jù)相關(guān)的有三個：中小站TMIS、金東TMIS、衢化TMIS，18點統(tǒng)計數(shù)據(jù)有兩個：本段18點(客收、客發(fā)、停時、中時、運用車、金東辦理輛數(shù)等指標(biāo))和貨運中心18點（涉及一些裝卸車數(shù)和貨物發(fā)送）。這些都是Oracle數(shù)據(jù)庫，所以需安裝32位的Oracle客戶端軟件，配好本地服務(wù)名后，新建MicrosoftOLEDBProviderforOracle類型連接管理器。

（3）在連接管理器中，添加、配置目標(biāo)數(shù)據(jù)源，由于車務(wù)數(shù)據(jù)分析系統(tǒng)采用的數(shù)據(jù)庫是SQLServer，所以新建OLEDB類型連接管理器。

（4）在“控制流”ControlFlow面板中，新增、配置“數(shù)據(jù)流任務(wù)”DataFlowTask。

（5）雙擊該數(shù)據(jù)流任務(wù)，進(jìn)入“數(shù)據(jù)流”DataFlow面板，添加數(shù)據(jù)源、數(shù)據(jù)流轉(zhuǎn)換及數(shù)據(jù)流目標(biāo)過程，如圖2。

（6）調(diào)試包，全為綠色即為成功。

（7）部署包。推薦修改配置屬性啟用包配置，然后再生成安裝部署文件。接著執(zhí)行xxx.SSISDeploymentManifest彈出包的安裝向?qū)б徊讲皆O(shè)置。SSIS包一般有兩種部署方式：部署到文件系統(tǒng)和部署到SQLServer。部署到文件系統(tǒng)，操作更簡單，配置修改也比較容易；而部署到SQLServer安全性更高。

（8）包的調(diào)度。通過SQLServeAgentJOB調(diào)用類型“SQLServerIntegrationService包”，并選擇該包的依賴配置文件（非常重要，以后包無論通過什么用戶運行，都會重新加載這個配置文件，使用這個數(shù)據(jù)庫連接密碼，否則包可能執(zhí)行不成功。）執(zhí)行JOB，查詢是否成功，并查看包執(zhí)行后的結(jié)果。

5　實踐總結(jié)

通過SSIS連接Oracle數(shù)據(jù)庫時走了些許彎路，也積累了一些經(jīng)驗，在此與大家分享：

（1）必須注意SSIS是32位開發(fā)工具，即使安裝64位的SQLServer。而 32位的工具只能 load32位的 oracle provider。所以相應(yīng)的Oracle客戶端工具也必須安裝32位，在SSIS中才能連接上數(shù)據(jù)源。

（2）Linkedserver去 連接Oracle的時候，首先會讀取tnsname.ora文件，然后讀取sqlnet.ora文件，然后用SQLServr.exe直接去連oracle,連接成功以后會再次讀取tnsname.ora文件，然后開始載入oracleOLEDB的DLL文件。在64位操作系統(tǒng)上安裝的32位SQLServer中，讀到的路徑是這樣的：(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.137.90.10)(PORT=1521))(CONNECT_DATA=(SID=express)(CID=(PROGRAM=D：ProgramFiles(x86)MicrosoftSQL ServerMSSQL.1MSSQLBinnsqlservr.exe)(HOST=SCT-ITSQLTEST)(USER=SYSTEM))))

（3）在64bit的操作系統(tǒng)上安裝32位的SQLServer服務(wù)器以及32位的oracle客戶端，是可以創(chuàng)建linkedserver和SSIS程序包的。但是，oracle在處理包含括號文件全路徑名稱時有個bug，這個bug在8i,9i和10g的版本上都存在，會報錯ORA-12154或者 ORA-6413，必須通過打oracle的補?。˙ug 3807408）來解決，所以推薦安裝Oracle的11g。

（4）報“無法從OLEDB訪問接口檢索列代碼頁信息”時，將數(shù)據(jù)流任務(wù)屬性中AlwaysUseDefaultCodePage設(shè)為True。

（5）注意 varchar類型（非 unicode）裝載到 nvarchar(unicode)類型字段時，要加個中間轉(zhuǎn)換插件，把字段類型轉(zhuǎn)換下再插入，或是把源表的字段類型先轉(zhuǎn)換成nvarchar。在SSIS中字符串類型的對應(yīng)關(guān)系如表1。

表1　SSIS中字符串類型對應(yīng)表

（6）32位的 SSIS包在64位的SQLServer環(huán)境執(zhí)行時要做修改：可以在SSDT里面修改debug選項(Run64BitRuntime=false)，還可以建立job執(zhí)行的時強制以32位方式運行。

6　展望

當(dāng)然，目前只是針對一、兩年內(nèi)近期的數(shù)據(jù)進(jìn)行分析與呈現(xiàn)。其實我們還可以進(jìn)一步深入挖掘多年的運營數(shù)據(jù)，對于身處大數(shù)據(jù)時代的企業(yè)而言，成功的關(guān)鍵還在于找出大數(shù)據(jù)所隱含的真知灼見?！耙郧?，人們總說信息就是力量，但如今，對數(shù)據(jù)進(jìn)行分析、利用和挖掘才是力量之所在。”