有一个存储过程,想请大侠帮指点下用SSIS实现的思路?

0

•-- ALTER the store procedure

•ALTER PROCEDURE .

•AS

• --定义变量

• DECLARE



•--表t_trans_lj字段

•@Serial_no Varchar(50)

•,@Cpmc Varchar(50) --需要处理

•,@Dept Varchar(50)

•,@Applicant Varchar(50)

•,@Close_date Datetime

•,@Reason Varchar(1000)

•,@Reason_detail Varchar(5000)

•,@Part_no Varchar(8000) --需要处理

•,@Status Varchar(8000) --需要处理

•,@Inventory_flag0 Varchar(50)

•,@Inventory_flag1 Varchar(50)

•,@Inventory_flag2 Varchar(50)

•,@Inventory_flag3 Varchar(50)

•,@Inventory_flag4 Varchar(50)

•,@Inventory_flag5 Varchar(50)

•,@Inventory_flag6 Varchar(50)

•,@Inventory_flag7 Varchar(50)

•,@Inventory_flag8 Varchar(50)

•,@Inventory_flag9 Varchar(50)--20121203新增

•,@Inventory_flag10 Varchar(50)--20121203新增

•,@Attribute1 Varchar(1000)

•,@Attribute2 Varchar(1000)

•,@Attribute3 Varchar(5000)

•,@Attribute4 Varchar(1000)

•,@Attribute5 Varchar(1000)

•,@Attribute7 Varchar(1000)



•--需要补充的列

•,@Flow_type Varchar(50)

•,@Ec_month Varchar(50)

•,@IPMT Varchar(50)

•,@PDT Varchar(50)

•,@Description Varchar(1000)

•,@Item_type Varchar(50)

•,@Inventory_flag Int

•,@Attribute6 Varchar(1000)

•,@my_Part_no Varchar(1000)



•--自定义过程变量

•,@i int

•,@n int



• --定义游标

• DECLARE cur_lj CURSOR FOR

• select '临技' as Flow_type

• ,replace(left(Close_date,7),'/','-') as Ec_month

• ,Serial_no

• ,Dept

• ,Applicant

• ,left(Close_date,charindex(' ',Close_date)-1) as Close_date

• ,Reason

• ,Reason_detail

• ,Part_no

• ,Inventory_flag0 --处理|1 不处理|2 报废|3

• ,Inventory_flag1

• ,Inventory_flag2

• ,Inventory_flag3

• ,Inventory_flag4

• ,Inventory_flag5

• ,Inventory_flag6

• ,Inventory_flag7

• ,Inventory_flag8

• ,Inventory_flag9--新增

• ,Inventory_flag10--新增

• ,Attribute1

• ,Attribute2

• ,Attribute3



• ,case Attribute4

• when '1' then '改制类临技'

• when '2' then '厂验类临技'

• when '3' then '其他类别'

• else Attribute4

• end as Attribute4



• ,case Attribute5

• when '1' then '用于试制'

• when '2' then '用于发货'

• else Attribute5

• end as Attribute5



• ,case Attribute7

• when '1' then '消耗低版本物料'

• when '2' then '针对隔离单有效'

• when '3' then '按任务令一次有效'

• when '4' then '在指定时间内有效'

• when '5' then '其他'

• else Attribute7

• end as Attribute7

• from t_trans_lj_new



• --打开游标

• OPEN cur_lj



• FETCH NEXT FROM cur_lj into @Flow_type

• ,@Ec_month

• ,@Serial_no

•-- ,@Cpmc

•-- ,@IPMT

•-- ,@PDT

• ,@Dept

• ,@Applicant

• ,@Close_date

• ,@Reason

• ,@Reason_detail

• ,@Part_no

•-- ,@Status

• ,@Inventory_flag0

• ,@Inventory_flag1

• ,@Inventory_flag2

• ,@Inventory_flag3

• ,@Inventory_flag4

• ,@Inventory_flag5

• ,@Inventory_flag6

• ,@Inventory_flag7

• ,@Inventory_flag8

• ,@Inventory_flag9--20121203新增

• ,@Inventory_flag10--20121203新增

• ,@Attribute1

• ,@Attribute2

• ,@Attribute3

• ,@Attribute4

• ,@Attribute5

• ,@Attribute7



• WHILE @@FETCH_STATUS = 0

• BEGIN --1.0



• --清空初值

• set @Inventory_flag = 0

• set @Attribute6 = ''



• --处理|1 不处理|2 报废|3

• if @Inventory_flag0 in ('1','3')

• begin

• set @Attribute6 = @Attribute6 + '在途PO' + ';'

• set @Inventory_flag = 1

• end

• if @Inventory_flag1 in ('1','3')

• begin

• set @Attribute6 = @Attribute6 + '原材料' + ';'

• set @Inventory_flag = 1

• end

• if @Inventory_flag2 in ('1','3')

• begin

• set @Attribute6 = @Attribute6 + '半成品' + ';'

• set @Inventory_flag = 1

• end

• if @Inventory_flag3 in ('1','3')

• begin

• set @Attribute6 = @Attribute6 + '成品' + ';'

• set @Inventory_flag = 1

• end

• if @Inventory_flag4 in ('1','3')

• begin

• set @Attribute6 = @Attribute6 + '研发库' + ';'

• set @Inventory_flag = 1

• end

• if @Inventory_flag5 in ('1','3')

• begin

• set @Attribute6 = @Attribute6 + '逆向返回品' + ';'

• set @Inventory_flag = 1

• end

• if @Inventory_flag6 in ('1','3')

• begin

• set @Attribute6 = @Attribute6 + '海外备件库' + ';'

• set @Inventory_flag = 1

• end

• if @Inventory_flag7 in ('1','3')

• begin

• set @Attribute6 = @Attribute6 + 'HUB成品库' + ';'

• set @Inventory_flag = 1

• end

• if @Inventory_flag8 in ('1','3')

• begin

• set @Attribute6 = @Attribute6 + '其他环节' + ';'

• set @Inventory_flag = 1

• end

• if @Inventory_flag9 in ('1','3')

• begin

• set @Attribute6 = @Attribute6 + '逆向备件维修' + ';' --20121203新增

• set @Inventory_flag = 1

• end

• if @Inventory_flag10 in ('1','3')

• begin

• set @Attribute6 = @Attribute6 + '逆向原件维修' + ';' --20121203新增

• set @Inventory_flag = 1

• end





• --拆分Partno

•-- set @i = 1

•-- set @n = dbo.Get_StrArrayLength(@Part_no,char(10)) --此函数默认返回值为



•-- while @i <= @n

•-- begin --1.1

•-- set @my_Part_no = ltrim(rtrim(dbo.Get_StrArrayStrOfIndex(@Part_no,char(10),@i)))



• set @my_Part_no = @Part_no



• --补充项目状态

• select @Status = max(status)

• from t_pending_status

• where part_no = @my_Part_no

• and implemented_date = (

• select max(implemented_date)

• from t_pending_status

• where part_no = @my_Part_no

• and implemented_date <= @Close_date

• )



• --补充描述、模板、cpmc pdt ipmt

• select @Description = Description

• ,@Item_type = Item_type

• ,@ipmt = ipmt

• ,@pdt = pdt

• ,@cpmc = cpmc

• from t_part

• where part_no = @my_Part_no



•-- set @i = @i + 1



• if len(@my_Part_no) > 0

• --写入t_ec基表,按Part的数量为n值来做写入循环

• insert into t_ec (Flow_type

• ,Ec_month

• ,Serial_no

• ,Cpmc

• ,IPMT

• ,PDT

• ,Dept

• ,applicant

• ,Close_date

• ,Reason

• ,Reason_detail

• ,Part_no

• ,Description

• ,Item_type

• ,Status

• ,Inventory_flag

• ,Attribute1

• ,Attribute2

• ,Attribute3

• ,Attribute4

• ,Attribute5

• ,Attribute6

• ,Attribute7

• ,Create_date

• )

• values(@Flow_type

• ,@Ec_month

• ,@Serial_no

• ,@Cpmc --

• ,@IPMT --

• ,@PDT --

• ,@Dept

• ,@applicant

• ,@Close_date

• ,@Reason

• ,@Reason_detail

• ,@my_Part_no --

• ,@Description --

• ,@Item_type --

• ,@Status --

• ,@Inventory_flag

• ,@Attribute1

• ,@Attribute2

• ,@Attribute3

• ,@Attribute4

• ,@Attribute5

• ,@Attribute6

• ,@Attribute7

• ,getdate()

• )

•-- end --1.1



• --继续处理下一条存在的记录

• FETCH NEXT FROM cur_lj into @Flow_type

• ,@Ec_month

• ,@Serial_no

•-- ,@Cpmc

•-- ,@IPMT

•-- ,@PDT

• ,@Dept

• ,@Applicant

• ,@Close_date

• ,@Reason

• ,@Reason_detail

• ,@Part_no

•-- ,@Status

• ,@Inventory_flag0

• ,@Inventory_flag1

• ,@Inventory_flag2

• ,@Inventory_flag3

• ,@Inventory_flag4

• ,@Inventory_flag5

• ,@Inventory_flag6

• ,@Inventory_flag7

• ,@Inventory_flag8

• ,@Inventory_flag9

• ,@Inventory_flag10

• ,@Attribute1

• ,@Attribute2

• ,@Attribute3

• ,@Attribute4

• ,@Attribute5

• ,@Attribute7



• END --1.0

• --关闭游标.

• CLOSE cur_lj

• DEALLOCATE cur_lj
已邀请:
0

choc - 终于找到组织了,学习BI中 2013-12-29 回答

关于上述存储过程,有2个疑问:
1)如果不用游标,可以怎么实现?上述存储过程,要跑90s,3000多条数据,我知道游标比较慢,但是不用游标,是否可以实现?
2)我觉得可以用SSIS实现,求指点思路。
0

牟瑞 - 大数据 Hadoop 讲师 Hadoop入门课程地址:http://www.hellobi.com/course/39 有架构师,技术总监,CTO的职位请联系我! 2013-12-29 回答

LOOK UP 控件是不是就可以解决这个问题?是一定要用SSIS么?优化下存储过程也是可以的吧?具体存储过程我没看,太长了,用临时表或者CTE应该是可以替换掉游标的。
0

choc - 终于找到组织了,学习BI中 2013-12-30 回答

2个疑问:
(1)如下存储过程,不用游标,如何实现?目前如下存储过程,3000多条数据,要跑90s,我知道游标比较慢,可是不知道如何优化?
(2)存储过程改用SSIS实现,求指点具体思路,用到哪些组件?
0

BIWORK - 热衷于微软BI技术,技术架构和解决方案! 2013-12-30 回答

我有一个小建议,就是这个代码格式如果能整理一下大家看的才方便,我把你的拷贝到 SQL Server 里就是这样的格式, 600 多行还要跨行看逻辑确实很困难
1.PNG


还有,如果是这样使用到游标的,整体逻辑过程较长不易分解的我不建议放到 SSIS 中实现:
第一:逻辑比较复杂的放到 SSIS 实现反而会更加复杂,特别是参数众多的情况下, 在 SSIS 中不易控制。
第二:如果出现任何的逻辑变动,不得不重新打开 SSIS 包并进行逻辑处理,逻辑复杂的情况下更加容易出现错误,不利于维护。如果是将逻辑放在存储过程中,那么不需要编辑 SSIS 包就可以直接修改存储过程,整个修改行为不对 SSIS 包有任何影响,你也不需要重新部署 SSIS 包。
第三: SSIS 包没有很好的办法对复杂的逻辑进行调试,但是放到存储过程中就可以很好的进行调试和优化。

所以 SSIS 包适合做流程,重要的非常复杂的逻辑应该使用存储过程。

我曾经做过的 ETL 项目有超过100多个 SSIS 包,所有的包与数据库的交互仅限于存储过程和视图。即使是一句 Truncate Table 也是一条存储过程,因为你无法预计到以后的需求变更会不会Drop 多个表数据。

当然正常的 Lookup , Data Conversion 该走 SSIS 的还是走 SSIS,仅限于复杂逻辑处理,需要不断优化和调试的SQL代码就应该放在存储过程中。
0

choc - 终于找到组织了,学习BI中 2013-12-30 回答

我晚上回去重新排下版,多谢BIWork的答复
0

梁勇 - 天道酬勤、上善若水。爱好商业智能 2013-12-30 回答

@choc 我帮语句放到代码里面了,以后发帖,可以把SQL语句、存储过程等相关的语句放到代码里面,这样方便大家阅读,谢谢。具体如下图所示
1.png

要回复问题请先登录注册