有一个存储过程,想请大侠帮指点下用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
没有找到相关结果
重要提示:提问者不能发表回复,可以通过评论与回答者沟通,沟通后可以通过编辑功能完善问题描述,以便后续其他人能够更容易理解问题.
6 个回复
choc - 终于找到组织了,学习BI中 2013-12-29 回答
赞同来自:
1)如果不用游标,可以怎么实现?上述存储过程,要跑90s,3000多条数据,我知道游标比较慢,但是不用游标,是否可以实现?
2)我觉得可以用SSIS实现,求指点思路。
牟瑞 - 大数据 Hadoop 讲师 Hadoop入门课程地址:http://www.hellobi.com/course/39 有架构师,技术总监,CTO的职位请联系我! 2013-12-29 回答
赞同来自:
choc - 终于找到组织了,学习BI中 2013-12-30 回答
赞同来自:
(1)如下存储过程,不用游标,如何实现?目前如下存储过程,3000多条数据,要跑90s,我知道游标比较慢,可是不知道如何优化?
(2)存储过程改用SSIS实现,求指点具体思路,用到哪些组件?
BIWORK - 热衷于微软BI技术,技术架构和解决方案! 2013-12-30 回答
赞同来自:
还有,如果是这样使用到游标的,整体逻辑过程较长不易分解的我不建议放到 SSIS 中实现:
第一:逻辑比较复杂的放到 SSIS 实现反而会更加复杂,特别是参数众多的情况下, 在 SSIS 中不易控制。
第二:如果出现任何的逻辑变动,不得不重新打开 SSIS 包并进行逻辑处理,逻辑复杂的情况下更加容易出现错误,不利于维护。如果是将逻辑放在存储过程中,那么不需要编辑 SSIS 包就可以直接修改存储过程,整个修改行为不对 SSIS 包有任何影响,你也不需要重新部署 SSIS 包。
第三: SSIS 包没有很好的办法对复杂的逻辑进行调试,但是放到存储过程中就可以很好的进行调试和优化。
所以 SSIS 包适合做流程,重要的非常复杂的逻辑应该使用存储过程。
我曾经做过的 ETL 项目有超过100多个 SSIS 包,所有的包与数据库的交互仅限于存储过程和视图。即使是一句 Truncate Table 也是一条存储过程,因为你无法预计到以后的需求变更会不会Drop 多个表数据。
当然正常的 Lookup , Data Conversion 该走 SSIS 的还是走 SSIS,仅限于复杂逻辑处理,需要不断优化和调试的SQL代码就应该放在存储过程中。
choc - 终于找到组织了,学习BI中 2013-12-30 回答
赞同来自:
梁勇 - 天道酬勤、上善若水。爱好商业智能 2013-12-30 回答
赞同来自: