ETL Process
Start Input cl_no, pr_no, ld_txt Variables
if cl_no || pr_no is null
Print <client ID>, <period>
cl_no = Client number pr_no = Period number ld_txt = Load text txt_ld = Text load tran_mod = Transformation module str = String msg = Log messages
if cl_no != 1
cl_no = sel cl_id from client table
if cl_no is null
Print error
Declare vars txt_ld,tran_mod,str,msg
If ld_txt = 1
txt_ld = Call proc sp_load txt(cl_no)
P2
If txt_ld != 0
call proc sp_logit(Error loading txt file)
tran_mod = sel trn_mod from tab_version
If tran_mod is null
call proc sp_logit(No trans mod found)
msg = trans_mod : + tran_mod call proc sp_logit(msg)
str = tran_mod + (pr_no) call proc str
P3
If error != 0
call proc sp_logit(Err transforming data)
call proc sp_update_processed_date(cl_no,pr_no) E Stop
P2 Variables c_id = client id row_cnt = row count msg = message str = string sql_tab = Sql table fmt_fil = Format file tem_fil = Template file txt_fil = text file other_txt_fil = other text file txt_loc = Text location txt_fullpth = Text full path fmt_loc = Format file location
if cl_no || pr_no is null
Print <client ID>, <period>
if cl_no != 1
cl_no = sel cl_id from client table
Declare vars c_id, row_cnt, error, sql_cmd msg, str,sql_tab, fmt_fil, tem_fil, txt_fil, other_txt_fil, txt_loc, txt_fil_fullpth, fmt_loc
error = 0
If pdt = Null || pdt not in(lps,heloc Heload) || cl_no = null || p_no = null
Print Incorrect proc Call
pdt = lower(pdt)
If !(sel * from tab_version)
call proc sp_logit(txt file not loaded)
c_id = sel lower(c_id) from client tab
sql_tab = case when lower(pdt) = lps then raw_c_id_pno_lps when lower(pdt) = heloc then raw_cid_pno_heloc when lower(pdt) = heloan then raw_cid_pno_heloan
txt_fil = case when lower(pdt) = lps then raw_c_id_pno_lps.txt when lower(pdt) = heloc then raw_cid_pno_heloc.txt when lower(pdt) = heloan then raw_cid_pno_heloan.txt
other_txt_fil = left(txt_fil,10) + ? + substr(txt_fil,11,100)
fmt_fil = sel fmt_fil from tab_version
tem_fil = sel tem_fil from tab_version
txt_loc = sel txt_fil from tab_fil_loc fmt_loc = sel fmt_fil from tab_fil_loc
If txt_fil = Null || txt_loc = Null || fmt_fil = Null || fmt_loc = Null
call proc sp_logit(txt file not loaded)
call proc sp_logit(Begin: text import) call proc sp_logit(Text file location) call proc sp_logit(Format file) call proc sp_logit(Template file)
txt_fil_full_path = txt_loc + replace(txt_file,.txt,.zip)
call proc sp_wzunzip(txt_file_full_path)
If exists (sel * from rawdata)
Drop table rawdata + sql_tab
Sel top 0* into rawdata + sql_tab from tem_file
Create table #input_texts(filename)
str = cmdshell + dir + txt_loc + txt_fil
Insert into #input_texts exec(str)
str = cmdshell + dir + txt_loc + other_txt_fil
Insert into #input_texts exec(str)
Declare vars filename, input_txt cursor for (select filename from input_txt)
While fetch_status = 0
If cl_no in (60,183)
sql_cmd = Bulk insert rawdata + sql_tab from txt_loc + filename..
If cl_no = 48
sql_cmd = Bulk insert rawdata + sql_tab from txt_loc + filename with format file..
sql_cmd = Bulk insert rawdata + sql_tab from txt_loc + filename with format file..
msg = Loading from + txt_loc + filename
call proc sp_logit(msg)
Print(sql_cmd)
exec (sql_cmd)
row_cnt = @@Rowcount error = @@error
If error = 0
call proc sp_logit(rows affected + row_cnt)
call proc sp_logit(error loading from + txt_loc + filename) call proc sp_logit(cast error + error)
Drop table sql_tab
fetch next from input_txt into filename
call proc sp_logit(sel no of rows from sysindexes) call proc sp_logit(End: text import + c_id + pdt)
if cl_no not in (138)
Call proc sp_check_for_fixes
return
P3
If cl_id not in (nwb,pru,37 ,62)
Invalid function call
Print <client ID>, <period>
If cl_id <> 1
cl_id = sel cl_id from tbl_clients
Declare rawdata,pru_rawdata, wfh_rawdata,nwb_rawdata, Transform_table,company_id, msg,parentid.err,nwb_rows, dupcount,insert_err,rows,output
Parent_id =37 C_id=select c_id from clients Rawdata =rawdata.dbo+c_id+p_no+_lps Trans_table =trans_+c_id+p_no+_lps
Exe proc sp_pre_trnsformation_check @c_id,per_no,output
Msg =BEGIN:LPS transformation(cmp_id)
If c_id =NWB
PRU_rawdata =raw_PRU+p_no+_lps WFH_rawdata =raw_PRU+p_no+_lps
If not exists select * from sysobjects
Msg=PRU& WFH raw data must exists When processing this client
Exec sp_logit proc NWB_rawdata=raw_NWB+p_no+_lps
If not exists select *from sysobjects Name=NWB_rawdata
Select * into+rawdata+from PRU_rawdata
NWB_rows=select top 1 i.rows from sysobjects
If NWB_rows=0
Drop table NWB_rawdata Msg=rawdata+NWB_rawdata+not created
Msg= rawdata+NWB_rawdata+:+NWB_rows+Rows
Exe proc sp_logit Exc proc sp_logit for branch_cd
Exe proc sp_logit
Msg=selection criteria:ltrim(rtrim(branch_cd))
Exe proc sp_logit
If select * from sysobj
Sqlstr=trans_sp_rename+trans_tab
Exe sqlstr
Create table #dupes Inserting values into #dupes Dupcount=select count(*) dupes Msg=duplicate rows found in rawdata+dupcount
Exec proc sp_logit
Msg=dupcount+rows excluded in the trans
Exec proc sp_logit
Sql_cmd=c_id=cast(c_id as varchar)+ p_id=cast(p_id as varchar)+ p_no=cast (p_no as varchar)+ Select add_per=cast (add_per as varchar)+, C_id=cast (c_id as varchar)+, . . . . . Into Trans.dbo+@Trans_tab from rawdata + R
Exec sql_cmd
err=Error Insert_err =error Rows= rowcount
If err!=0
Msg= Error+err+occurred Exec proc sp_logit
Msg=rows+LPS rows transfd
Exec proc sp_logit
Msg=trans_tab+trans_tab
Exec proc sp_logit
Msg=rows+LPS rows transfd
Exec proc sp_logit
return
