dts使用开发培训1

开发使用培训

《ETL设计报告》

编号：XAB-QJ-???-R-TES-??? 文件状态： 当前版本： 1.0 [ ] 草稿 完成日期： [√] 正式发布 编 制： [ ] 正在修改 审 核： 批 准：

北京鑫奥博科技发展有限公司软件开发部

北京鑫奥博科技发展有限公司

Page 2 of 13

《ETL设计报告》

版 本 历 史

版本/状态 作者 参与者 起止日期 1.0 孙旭东

北京鑫奥博科技发展有限公司

备注 Page 3 of 13

《ETL设计报告》

目 录

1 1.1 1.2 1.3 1.4 1.5 2 3 3.1

文档介绍 ...................................................................................................................... 5 文档目的 ................................................................................................................... 5 文档范围 ................................................................................................................... 5 读者对象 ................................................................................................................... 5 参考文献 ................................................................................................................... 5 术语与缩写解释 ....................................................................................................... 5 开发环境 ...................................................................................................................... 6 开发 .............................................................................................................................. 7 数据连接 ................................................................................................................... 7

3.1.1 3.1.2 3.1.3 3.1.4 3.1.5 3.2 3.2.1 3.2.2 3.2.3 3.2.4 3.2.5 3.2.6 3.3 3.4 3.5 3.6 4 4.1 4.2 4.3 4.4 4.5

文件源 ............................................................................................................... 7 文件目的 ............................................................................................................ 7

Microsoft ODBC Driver for Oracle .................................................................... 7 Microsoft OLE DB Provider for Oracle ............................................................. 7 Microsoft OLE DB Provider for SQL Server ...................................................... 7 ActiveX 脚本任务 ............................................................................................. 7

数据处理任务 ........................................................................................................... 7

数据转换任务 .................................................................................................... 7 执行进程任务 .................................................................................................... 7 执行 SQL 任务 ................................................................................................. 7

Dynamic Properties Task（动态属性任务）..................................................... 7

执行包任务 ........................................................................................................ 7

工作流 ....................................................................................................................... 7 包属性 ....................................................................................................................... 7 参数使用 ................................................................................................................... 7 脱接编辑 ................................................................................................................... 7 规范 .............................................................................................................................. 8 程序存放位置 ........................................................................................................... 8 不同项目之间的程序交互处理规范 ........................................................................ 8 数据源处理 ............................................................................................................... 8 SQL语句编写模式 .................................................................................................... 8 SQL语句的调优 ...................................................................................................... 10

北京鑫奥博科技发展有限公司

Page 4 of 13

《ETL设计报告》

1 文档介绍

1.1 文档目的

为dts开发提供使用技术参考 为dts开发定义规范。

1.2 文档范围

1.3 读者对象

Etl处理开发人员

1.4 参考文献

VBSTOC.HTM、sql server帮助系统

1.5 术语与缩写解释

缩写、术语

解 释 北京鑫奥博科技发展有限公司

Page 5 of 13

《ETL设计报告》

2 开发环境

源数据：oracle 9i,txt,csv(excel文件保存为csv文件)等 目标数据库：oracle9i

程序编写和执行环境：sql server数据库（可以不安装服务，而使用其他机器的sql server服务）

北京鑫奥博科技发展有限公司

Page 6 of 13

《ETL设计报告》

3 开发

3.1 数据连接

3.1.1 文本文件源 3.1.2 文本文件目的

3.1.3 Microsoft ODBC Driver for Oracle 3.1.4 Microsoft OLE DB Provider for Oracle 3.1.5 Microsoft OLE DB Provider for SQL Server

3.2 数据处理任务

3.2.1 ActiveX 脚本任务 3.2.2 数据转换任务 3.2.3 执行进程任务 3.2.4 执行 SQL 任务

3.2.5 Dynamic Properties Task（动态属性任务） 3.2.6 执行包任务

3.3 工作流 3.4 包属性 3.5 参数使用

3.5.1 Vbscript传参 3.5.2 Sql查询传参

3.6 脱接编辑

北京鑫奥博科技发展有限公司

Page 7 of 13

《ETL设计报告》

4 规范

4.1 程序存放位置

4.2 不同项目之间的程序交互处理规范 4.3 数据源处理

Excel文件必须存为csv文件，去除“?”（乱码）、空格

4.4 Sql语句编写模式

/*初步汇总层*/ /*问题:*/

/* 1、全局应税总额统计的是税收还是全部？如果只是税收，而要和重点功能区作比较，重点功能区统计的是全部，没有意义*/ /* 2、数据中有局代码为空的纪录，如何解释*/ /**/ /**/ /**/

/*注意：1、一个隐含条件是纳税人状态为10的正常户*/ /**/ /**/ /**/ /*insert

into

F_ZDGNQSYJK

(D_NY,D_ZDGNQDM,D_ZDGNQMC,F_XQZHS,F_XQZDHS,F_XQYSZE,F_XQZDHYSZE,F_XQYNSZE,F_XQZDHYNSZE,F_QJZHS,F_QJZDHS,F_QJYSZE,F_QJZDHYSZE,F_QJYNSZE,F_QJZDHYNSZE,F_QJYZ,F_QJRK)*/ select

t1.D_NY,t1.D_ZDGNQDM,t1.D_ZDGNQMC,t1.F_XQYSZE,t1.F_XQYNSZE,t1.F_QJYZ,t1.F_QJRK,t2.F_XQZHS,t3.F_XQZDHS,t4.F_XQZDHYSZE,t4.F_XQZDHYNSZE,t5.F_QJZHS,t6.F_QJZDHS,t7.F_QJYSZE,t7.F_QJYNSZE,t8.F_QJZDHYSZE,t8.F_QJZDHYNSZE from

(

select

Page 8 of 13

/**辖区应税总额、辖区已(纳)税总额/区级应征、区级应征**/

北京鑫奥博科技发展有限公司

《ETL设计报告》

F_SYHSYJKSZ.D_NY,D_ZDGNQ.D_ZDGNQDM,D_ZDGNQ.D_ZDGNQMC,sum(F_SYHSYJKSZ.F_YSJE_QB)

as

F_XQYSZE,sum(F_SYHSYJKSZ.F_YNSJE_QB)

as as

F_XQYNSZE,sum(F_SYHSYJKSZ.F_YSJE_SR) F_QJYZ,sum(F_SYHSYJKSZ.F_YNSJE_SR) as F_QJRK

from D_ZDGNQ,F_SYHSYJKSZ,T_SYHJBXX where

T_SYHJBXX.D_JSJDM=F_SYHSYJKSZ.D_JSJDM

and and by

D_ZDGNQ.D_ZDGNQDM=T_SYHJBXX.D_ZDGNQDM T_SYHJBXX.D_NSRZTDM='10'

group

F_SYHSYJKSZ.D_NY,D_ZDGNQ.D_ZDGNQDM,D_ZDGNQ.D_ZDGNQMC

) t1,

/**辖区总户数**/ (

select D_ZDGNQDM,count(T_SYHJBXX.D_JSJDM) as F_XQZHS from T_SYHJBXX where D_NSRZTDM='10' group by D_ZDGNQDM

) t2,

/**辖区重点户数**/ (

select D_ZDGNQDM,count(D_JSJDM) as F_XQZDHS from T_SYHJBXX

where D_ZDHLXDM!='-1' and D_NSRZTDM='10' group by D_ZDGNQDM

) t3,

/**辖区重点户应税总额、辖区重点户已(纳)税总额**/ (

select

F_SYHSYJKSZ.D_NY,T_SYHJBXX.D_ZDGNQDM,sum(F_SYHSYJKSZ.F_YSJE_QB) as F_XQZDHYSZE,sum(F_SYHSYJKSZ.F_YNSJE_QB) as F_XQZDHYNSZE

from F_SYHSYJKSZ,T_SYHJBXX where

T_SYHJBXX.D_JSJDM=F_SYHSYJKSZ.D_JSJDM

and

T_SYHJBXX.D_ZDHLXDM!='-1' and T_SYHJBXX.D_NSRZTDM='10'

group by F_SYHSYJKSZ.D_NY,T_SYHJBXX.D_ZDGNQDM

) t4,

/**全局总户数**/ (

select count(D_JSJDM) as F_QJZHS from T_SYHJBXX where D_NSRZTDM='10'

Page 9 of 13

北京鑫奥博科技发展有限公司

《ETL设计报告》

) t5,

/*全局重点户数*/ (

select count(D_JSJDM) as F_QJZDHS from T_SYHJBXX

where D_NSRZTDM='10' and D_ZDHLXDM!='-1'

) t6,

/**全局应税总额、全局已(纳)税总额**/ (

select

F_SYHSYJKSZ.D_NY,sum(F_SYHSYJKSZ.F_YSJE_QB)

as

F_QJYSZE,sum(F_SYHSYJKSZ.F_YNSJE_QB) as F_QJYNSZE

from F_SYHSYJKSZ,T_SYHJBXX where

T_SYHJBXX.D_JSJDM=F_SYHSYJKSZ.D_JSJDM

and

T_SYHJBXX.D_NSRZTDM='10'

group by F_SYHSYJKSZ.D_NY

) t7,

/**全局重点户应税总额、全局重点户已(纳)税总额**/ ( ) t8

/*先连接重点功能区代码*/

t1.D_ZDGNQDM=t2.D_ZDGNQDM and t2.D_ZDGNQDM=t3.D_ZDGNQDM and /*再连接年月*/

t1.D_NY=t4.D_NY and t4.D_NY=t7.D_NY and t7.D_NY=t8.D_NY

select

F_SYHSYJKSZ.D_NY,sum(F_SYHSYJKSZ.F_YSJE_QB)

as

F_QJZDHYSZE,sum(F_SYHSYJKSZ.F_YNSJE_QB) as F_QJZDHYNSZE

from F_SYHSYJKSZ,T_SYHJBXX where

T_SYHJBXX.D_JSJDM=F_SYHSYJKSZ.D_JSJDM

and

T_SYHJBXX.D_NSRZTDM='10' and T_SYHJBXX.D_ZDHLXDM!='-1'

group by F_SYHSYJKSZ.D_NY

where

t3.D_ZDGNQDM=t4.D_ZDGNQDM and

4.5 Sql语句的调优

Not in

临时表（将查询过程拆分）

北京鑫奥博科技发展有限公司

Page 10 of 13

《ETL设计报告》

5 实际应用

5.1 Case 行转列 5.2 两部分数据比较不同

5.3 Dba_tab_columns、Dba_tables

select b.table_name,b.column_name from dba_tab_columns a,dba_tab_columns b where a.owner='a' and b.owner='b'

and a.table_name(+)=b.table_name and a.column_name(+)=b.column_name and a.column_name is null

5.4 Tab 5.5 工作流控制

'********************************************************************** ' Visual Basic ActiveX Script

'************************************************************************

Function Main()

if isFirst=0 then

Main = DTSStepScriptResult_ExecuteTask else

Main =DTSStepScriptResult_DontExecuteTask end if

dim isFirst

isFirst=cint(DTSGlobalVariables(\"isfirst\").Value)

End Function

北京鑫奥博科技发展有限公司

Page 11 of 13

《ETL设计报告》

5.6 截取

该税种的营业税金额75931.05,计算7%的金额是5315.1735 该税种的营业税金额312.5,计算7%的金额是21.875 该税种的营业税金额1050,计算7%的金额是73.5 该税种的营业税金额10000,计算7%的金额是700 --钩稽关系预警历史数据修改

alter table F_GJGXYJ add tmp1 VARCHAR2(255); update f_gjgxyj set tmp1=d_glxx

where length(d_glxx)-instr(d_glxx,'.',1,2)>2 and instr(d_glxx,'.',1,2)>0; commit;

update f_gjgxyj set d_glxx=substr(d_glxx,1,instr(d_glxx,'.',1,2)+2) where tmp1 is not null

and to_number(substr(tmp1,instr(tmp1,'.',1,2)+3,1))<5; commit; update ,1))+1)

where tmp1 is not null

and to_number(substr(tmp1,instr(tmp1,'.',1,2)+3,1))>=5; commit;

select count(*) from f_gjgxyj update

f_gjgxyj

set

tmp1=substr(d_glxx,instr(d_glxx,'

是

',1,1)+1,length(d_glxx)-instr(d_glxx,'是',1,1)) where instr(d_glxx,'是',1,1)>0; commit; update

f_gjgxyj

set

tmp1=substr(d_glxx,instr(d_glxx,'

为

',1,2)+1,length(d_glxx)-instr(d_glxx,'为',1,2)) where instr(d_glxx,'为',1,2)>0; commit;

delete from f_gjgxyj where to_number(tmp1)=f_jsje; commit;

alter table F_GJGXYJ drop column tmp1;

f_gjgxyj

set

d_glxx=substr(d_glxx,1,instr(d_glxx,'.',1,2)+1)||(to_number(substr(tmp1,instr(tmp1,'.',1,2)+2

5.7 Sqlloader

作业：

体育馆、永外分户数据导入219的zsthcwds， 坐标数据导入

北京鑫奥博科技发展有限公司

Page 12 of 13

《ETL设计报告》

原文件： 目的表

2、文本文件取两列，合并放到一个表的一个字段中 表自己创建

3、创建表包含列：jsjdm,szdm,1月金额，2月。。。12月， 数据来源f_aszysjlb的2006年的数据，2006通过配置文件指定。 全局变量传参数。

北京鑫奥博科技发展有限公司

Page 13 of 13