實(shí)現(xiàn)的環(huán)境：

業(yè)務(wù)數(shù)據(jù)庫(kù)：Oracle數(shù)據(jù)庫(kù)9i

數(shù)據(jù)倉(cāng)庫(kù)數(shù)據(jù)庫(kù)：SQL Server 2000數(shù)據(jù)庫(kù)

1、前提條件：SQL Server服務(wù)器必須已經(jīng)安裝Oracle驅(qū)動(dòng)

2、創(chuàng)建鏈接數(shù)據(jù)庫(kù)

打開(kāi)企業(yè)管理器->安全性-鏈接服務(wù)器-右鍵新建

通常情況當(dāng)鏈接數(shù)據(jù)庫(kù)創(chuàng)建好，進(jìn)行打開(kāi)的時(shí)候都會(huì)彈出一下錯(cuò)誤窗口：

一般情況下運(yùn)行C:PRogram FilesCommon FilesSystemOle DBmtxoci81_win2k.reg該文件后，重啟SQLServer數(shù)據(jù)庫(kù)，再重新連接；

如果仍有問(wèn)題，重啟操作系統(tǒng)，即可OK。

3、創(chuàng)建Oracle環(huán)境腳本

--創(chuàng)建Oracle業(yè)務(wù)系統(tǒng)表結(jié)構(gòu)

CREATE TABLE SourceTable

(

ID1 VARCHAR2(50),

ID2 VARCHAR2(50),

Measure1 INTEGER,

Measure2 INTEGER,

CloseDate DATE

)

--創(chuàng)建測(cè)試數(shù)據(jù)

DECLARE

-- Local variables here

i INTEGER;

BEGIN

-- Test statements here

FOR i IN 1..365 LOOP

INSERT INTO SourceTable

VALUES(i,i,i,i,TO_DATE('2006-01-01','yyyy-mm-dd')+i);

INSERT INTO SourceTable

VALUES(i,i,i,i,TO_DATE('2006-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss')+i);

END LOOP

COMMIT;

END;

4、創(chuàng)建SQLServer數(shù)據(jù)倉(cāng)庫(kù)環(huán)境腳本

--創(chuàng)建系統(tǒng)參數(shù)表內(nèi)

CREATE TABLE ExtractTaskList (

TaskName VARCHAR(32) ,

TargetTable VARCHAR(32) ,

TargetFieldList VARCHAR(500) ,

SourceTable VARCHAR(32) ,

SourceFieldList VARCHAR(500) ,

WhereFieldName VARCHAR(32) ,

IncType INT ,

TransType INT ,

TargetDate DATETIME ,

SourceDate DATETIME ,

Flag INT ,

Note VARCHAR (500)

)

GO

--創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)目標(biāo)表

CREATE TABLE TargetTable (

ID1 VARCHAR(50) ,

ID2 VARCHAR(50) ,

Measure1 DECIMAL(18, 0) ,

Measure2 DECIMAL(18, 0) ,

CloseDate DATETIME

)

GO

5、創(chuàng)建SQLServer數(shù)據(jù)倉(cāng)庫(kù)ETL腳本

腳本考慮到現(xiàn)實(shí)的問(wèn)題，已經(jīng)做了許多取舍，不再追求全部動(dòng)態(tài)實(shí)現(xiàn)，旨在給定一個(gè)模板，在有限的范圍內(nèi)可以更改每次抽取的周期，每次時(shí)間的跨度，抽取的字段，表等等；數(shù)據(jù)字典表僅僅利用了其中的四個(gè)字段：任務(wù)名稱，當(dāng)前抽取時(shí)間、結(jié)束時(shí)間、抽取狀態(tài)。 CREATE PROCEDURE p_org_Extract

AS

DECLARE @sql VARCHAR(3000)

BEGIN

DECLARE @BeginDate DATETIME,

@EndDate DATETIME,

@TaskName VARCHAR(32),

@Flag INTEGER,

@Num INTEGER,

@CurrDate DATETIME

SELECT @Num = COUNT(TaskName) FROM ExtractTaskList

WHERE UPPER(TaskName) = UPPER('test')

IF @Num != 1

INSERT INTO ExtractTaskList(TaskName,IncType,TransType) VALUES('test',2,2)

--獲取列表中的當(dāng)前任務(wù)的時(shí)間戳和狀態(tài)

SELECT @BeginDate = SourceDate,@Flag = Flag FROM ExtractTaskList WHERE TaskName='TEST'

--如果上次執(zhí)行未成功，這樣取值效率會(huì)高一些，則從數(shù)據(jù)倉(cāng)庫(kù)表中直接讀取

--TargetDate和SourceDate可能會(huì)不一致

IF @Flag = 2 OR @Flag IS NULL

SELECT @BeginDate = DATEADD(ss,1,MAX(closedate)) FROM TargetTable

--如果數(shù)據(jù)倉(cāng)庫(kù)無(wú)數(shù)據(jù)，則從業(yè)務(wù)系統(tǒng)中直接讀取，也可以設(shè)置一個(gè)默認(rèn)的初始化時(shí)間

IF @BeginDate IS NULL

SELECT @BeginDate = MinLogDate FROM OPENQUERY(SOURCE,'SELECT MIN(CloseDate) AS MinLogDate FROM SourceTable')

--如果仍無(wú)數(shù)據(jù)，則表示無(wú)數(shù)據(jù)可抽取，退出執(zhí)行

IF @BeginDate IS NULL

RETURN

--抽取結(jié)束時(shí)間為當(dāng)前時(shí)間前一天,每次循環(huán)抽取1天數(shù)據(jù),可以更改dd為hh，變成按小時(shí)抽取

--通常業(yè)務(wù)系統(tǒng)是連續(xù)的，如果有疑問(wèn)也可以從業(yè)務(wù)系統(tǒng)中獲取最大時(shí)間

SELECT @EndDate = CONVERT(DATETIME,LEFT(CONVERT(VARCHAR,GETDATE(),120),10)+' 00:00:00')

--更新當(dāng)前開(kāi)始時(shí)間和結(jié)束時(shí)間

UPDATE ExtractTaskList

SET TargetDate = @BeginDate,

SourceDate = @EndDate

WHERE UPPER(TaskName) = UPPER('test')

WHILE @BeginDate < @EndDate

BEGIN

SELECT @sql = ' INSERT INTO TargetTable

(

ID1,

ID2,

Measure1,

Measure2,

CloseDate

)SELECT * FROM OPENQUERY(SOURCE,''select

ID1,

ID2,

Measure1,

Measure2,

CloseDate

FROM SourceTable

WHERE CloseDate >= TO_DATE(''''' + CONVERT(varchar,@BeginDate,120) + ''''', ''''YYYY-MM-DD HH24:MI:SS'

+ ''''') AND CloseDate < TO_DATE(''''' + CONVERT(varchar,DATEADD(day,1,@BeginDate),120) + ''''', ''''yyyy-mm-dd HH24:MI:SS'

+ ''''') AND CloseDate < TO_DATE(''''' + CONVERT(varchar,@EndDate,120) + ''''', ''''YYYY-MM-DD HH24:MI:SS'

+ ''''')'')'

--PRINT @sql

EXEC (@sql)

--獲取本次任務(wù)運(yùn)行抽取的最大時(shí)間

IF DATEADD(day,1,@BeginDate)>@EndDate

SELECT @CurrDate = @EndDate

ELSE

SELECT @CurrDate = DATEADD(day,1,@BeginDate)

--如果@sql執(zhí)行失敗，同樣記錄狀態(tài)和時(shí)間

IF @@ERROR <> 0

GOTO FAIL

--記錄每次運(yùn)行的時(shí)間運(yùn)行情況，可提供相應(yīng)參考

UPDATE ExtractTaskList

SET TargetDate = @CurrDate,

Flag = 1

WHERE UPPER(TaskName) = UPPER('test')

SELECT @BeginDate = DATEADD(DD,1,@BeginDate)

END

RETURN

FAIL:

--記錄錯(cuò)誤

UPDATE ExtractTaskList

SET TargetDate = @CurrDate,

Flag = 2

WHERE UPPER(TaskName) = UPPER('test')

RETURN 0

END