How to create temp tables in SSIS and use them in data flow task
hi ,
my requirement data server , put serverb. have somany joins in source query , temp tables if run query in ssms runs fine, put same query in data flow ole db source task, receive error.
below code, lareg dont think need worry code @ working fine
select dbo.hs_claim.hsclaimnumber claim_number, min(hs_payment_vw.hscheckdate) min_check_date,dbo.hs_claim.hsdatecreated into ##1 from dbo.hs_claim left outer join dbo.hs_transaction trans on hs_claim.hsclaimid = trans.hsclaimid left outer join dbo.hs_transaction_code_vw tcode_vw on trans.hstransactioncodeid = tcode_vw.hstransactioncodeid left outer join hs_payment_vw on trans.hspaymentid = hs_payment_vw.hspaymentid where hs_payment_vw.hsstatus = 'payment complete'and tcode_vw.hstransaction = 'payment'and tcode_vw.hstransactiontype ='indemnity' group dbo.hs_claim.hsclaimnumber,dbo.hs_claim.hsdatecreated select distinct dbo.hs_claim.hsclaimnumber claim_number,t.min_check_date,qx_users.fullname paid_by,s.value specialty,o.value office, integrator.qw_fullname integrator,hs_payment_vw.hsreason, payment_method = case when hs_payment_vw.hs_eft_payment = 1 'eft' else 'check' end, hs_payee.hstype, contact_date =dateadd(day, case datename(weekday, getdate()) when 'friday' 3 else 1 end, getdate()),upper(rtrim(supervisor.qw_lastname)) team, hs_payment_vw.hscheckdate check_date,hs_payment_vw.hsreason payment_description,dbo.hs_claim.hsdatecreated,qw_employee.hsspecialty into ##2 from dbo.hs_claim left outer join dbo.hs_transaction trans on hs_claim.hsclaimid = trans.hsclaimid left outer join dbo.hs_transaction_code_vw tcode_vw on trans.hstransactioncodeid = tcode_vw.hstransactioncodeid left outer join dbo.hs_dd_claimstatus_vw on hs_claim.hsclaimstatus = hs_dd_claimstatus_vw.pkey left outer join hs_payment_vw on trans.hspaymentid = hs_payment_vw.hspaymentid left outer join dbo.hs_transaction_code on trans.hstransactioncodeid = hs_transaction_code.hstransactioncodeid left outer join qx_users on hs_payment_vw.hscreatedby = qx_users.loginname left outer join qw_employee on qx_users.pkey = qw_employee.qw_userid left outer join hs_dd_office_vw o on qw_employee.hsoffice=o.pkey left outer join hs_dd_specialty_vw s on qw_employee.hsspecialty = s.pkey left outer join qw_employee supervisor on qw_employee.qw_managerid = supervisor.qw_userid left outer join hs_payee on hs_payment_vw.hspaymentid = hs_payee.hspaymentid left outer join qw_employee integrator on integrator.qw_userid=dbo.hs_claim.hsintegratorid inner join ##1 t on t.claim_number=dbo.hs_claim.hsclaimnumber , t.min_check_date=hs_payment_vw.hscheckdate where hs_payment_vw.hsstatus = 'payment complete'and tcode_vw.hstransaction = 'payment' and tcode_vw.hstransactiontype ='indemnity' , hstype =1 , integrator.hsspecialty<>68 group dbo.hs_claim.hsclaimnumber ,qx_users.fullname ,integrator.qw_fullname ,hs_payment_vw.hsreason,t.min_check_date,supervisor.qw_lastname, hs_payment_vw.hs_eft_payment , hs_payee.hstype, s.value,o.value, hs_payment_vw.hscheckdate ,hs_payment_vw.hsreason ,dbo.hs_claim.hsdatecreated,qw_employee.hsspecialty having convert(date,t.min_check_date,101) = case when hs_payment_vw.hs_eft_payment=1 , datepart (weekday, dateadd( dd, -6, getdate())) in (1,7) convert(date,dateadd(dd,-4,getdate()),101) when hs_payment_vw.hs_eft_payment=1 , datepart (weekday, dateadd( dd, -6, getdate())) not in (1,7) convert(date,dateadd(dd,-6,getdate()),101) when hs_payment_vw.hs_eft_payment=0 , datepart (weekday, dateadd( dd, -14, getdate())) not in (1,7) convert(date,dateadd(dd,-14,getdate()),101)else null end select hs_comments.hsdatecreated date_created,dbo.hs_claim.hsclaimnumber claim_number , dbo.qw_employee.qw_fullname full_name,hsnote ##3 from dbo.hs_claim inner join dbo.hs_comments on dbo.hs_claim.hsclaimid = dbo.hs_comments.hsobjectid left outer join qx_users on (case when hs_comments.hscreatedby= 'kpullo' 'kdavidson' when hs_comments.hscreatedby= 'lross' 'linette.august' when hs_comments.hscreatedby= 'ntarry' 'nnaylor' when hs_comments.hscreatedby= 'katee.flesher' 'katee.parton' else hs_comments.hscreatedby end ) = qx_users.loginname left outer join qw_employee on qx_users.pkey = qw_employee.qw_userid where dbo.hs_comments.hsaction in (1) , year(hs_comments.hsdatecreated) >= year(getdate()) , hs_comments.hsnote '%ppc%' select t2.claim_number,t2.check_date first_acv_payment,t2.contact_date,t2.specialty,t2.office,t2.integrator,t2.paid_by,t2.payment_method,t2.payment_description,t2.team,datediff(day,t2.hsdatecreated,t2.check_date) cycle_time,t2.hsdatecreated,'qx' loadsource from ##2 t2 left outer join ##3 t3 on t3.claim_number =t2.claim_number , t2.min_check_date < t3.date_created where t3.claim_number null , year(t2.hsdatecreated)=year(getdate()) order t3.claim_number asc drop table ##1 drop table ##2 drop table ##3
the error receive in data flow task "invalid object name ##1". may need change way dropping tables?
can please me suggestions on this?
thanks
hi sqldev12,
as per understanding, think issue should create global temporary tables beforehand, directly execute select query return desired columns.
another method using common table expressions, can create 3 cte instead of 3 global temporary tables achieve requirement. following query commands reference:
with table1 ( select dbo.hs_claim.hsclaimnumber claim_number, min(hs_payment_vw.hscheckdate) min_check_date,dbo.hs_claim.hsdatecreated dbo.hs_claim left outer join dbo.hs_transaction trans on hs_claim.hsclaimid = trans.hsclaimid left outer join dbo.hs_transaction_code_vw tcode_vw on trans.hstransactioncodeid = tcode_vw.hstransactioncodeid left outer join hs_payment_vw on trans.hspaymentid = hs_payment_vw.hspaymentid hs_payment_vw.hsstatus = 'payment complete'and tcode_vw.hstransaction = 'payment'and tcode_vw.hstransactiontype ='indemnity' group dbo.hs_claim.hsclaimnumber,dbo.hs_claim.hsdatecreated ), table2 ( select distinct dbo.hs_claim.hsclaimnumber claim_number,t.min_check_date,qx_users.fullname paid_by,s.value specialty,o.value office, integrator.qw_fullname integrator,hs_payment_vw.hsreason, payment_method = case when hs_payment_vw.hs_eft_payment = 1 'eft' else 'check' end, hs_payee.hstype, contact_date =dateadd(day, case datename(weekday, getdate()) when 'friday' 3 else 1 end, getdate()),upper(rtrim(supervisor.qw_lastname)) team, hs_payment_vw.hscheckdate check_date,hs_payment_vw.hsreason payment_description,dbo.hs_claim.hsdatecreated,qw_employee.hsspecialty dbo.hs_claim left outer join dbo.hs_transaction trans on hs_claim.hsclaimid = trans.hsclaimid left outer join dbo.hs_transaction_code_vw tcode_vw on trans.hstransactioncodeid = tcode_vw.hstransactioncodeid left outer join dbo.hs_dd_claimstatus_vw on hs_claim.hsclaimstatus = hs_dd_claimstatus_vw.pkey left outer join hs_payment_vw on trans.hspaymentid = hs_payment_vw.hspaymentid left outer join dbo.hs_transaction_code on trans.hstransactioncodeid = hs_transaction_code.hstransactioncodeid left outer join qx_users on hs_payment_vw.hscreatedby = qx_users.loginname left outer join qw_employee on qx_users.pkey = qw_employee.qw_userid left outer join hs_dd_office_vw o on qw_employee.hsoffice=o.pkey left outer join hs_dd_specialty_vw s on qw_employee.hsspecialty = s.pkey left outer join qw_employee supervisor on qw_employee.qw_managerid = supervisor.qw_userid left outer join hs_payee on hs_payment_vw.hspaymentid = hs_payee.hspaymentid left outer join qw_employee integrator on integrator.qw_userid=dbo.hs_claim.hsintegratorid inner join table1 t on t.claim_number=dbo.hs_claim.hsclaimnumber , t.min_check_date=hs_payment_vw.hscheckdate hs_payment_vw.hsstatus = 'payment complete'and tcode_vw.hstransaction = 'payment' , tcode_vw.hstransactiontype ='indemnity' , hstype =1 , integrator.hsspecialty<>68 group dbo.hs_claim.hsclaimnumber ,qx_users.fullname ,integrator.qw_fullname ,hs_payment_vw.hsreason,t.min_check_date,supervisor.qw_lastname, hs_payment_vw.hs_eft_payment , hs_payee.hstype, s.value,o.value, hs_payment_vw.hscheckdate ,hs_payment_vw.hsreason ,dbo.hs_claim.hsdatecreated,qw_employee.hsspecialty having convert(date,t.min_check_date,101) = case when hs_payment_vw.hs_eft_payment=1 , datepart (weekday, dateadd( dd, -6, getdate())) in (1,7) convert(date,dateadd(dd,-4,getdate()),101) when hs_payment_vw.hs_eft_payment=1 , datepart (weekday, dateadd( dd, -6, getdate())) not in (1,7) convert(date,dateadd(dd,-6,getdate()),101) when hs_payment_vw.hs_eft_payment=0 , datepart (weekday, dateadd( dd, -14, getdate())) not in (1,7) convert(date,dateadd(dd,-14,getdate()),101)else null end ), table3 as( select hs_comments.hsdatecreated date_created,dbo.hs_claim.hsclaimnumber claim_number , dbo.qw_employee.qw_fullname full_name,hsnote dbo.hs_claim inner join dbo.hs_comments on dbo.hs_claim.hsclaimid = dbo.hs_comments.hsobjectid left outer join qx_users on (case when hs_comments.hscreatedby= 'kpullo' 'kdavidson' when hs_comments.hscreatedby= 'lross' 'linette.august' when hs_comments.hscreatedby= 'ntarry' 'nnaylor' when hs_comments.hscreatedby= 'katee.flesher' 'katee.parton' else hs_comments.hscreatedby end ) = qx_users.loginname left outer join qw_employee on qx_users.pkey = qw_employee.qw_userid dbo.hs_comments.hsaction in (1) , year(hs_comments.hsdatecreated) >= year(getdate()) , hs_comments.hsnote '%ppc%' ) select t2.claim_number,t2.check_date first_acv_payment,t2.contact_date,t2.specialty,t2.office,t2.integrator,t2.paid_by,t2.payment_method,t2.payment_description,t2.team,datediff(day,t2.hsdatecreated,t2.check_date) cycle_time,t2.hsdatecreated,'qx' loadsource table2 t2 left outer join table3 t3 on t3.claim_number =t2.claim_number , t2.min_check_date < t3.date_created t3.claim_number null , year(t2.hsdatecreated)=year(getdate()) order t3.claim_number asc
if there other questions, please feel free ask.
thanks,
katherine xiong
katherine xiong
technet community support
SQL Server > SQL Server Integration Services
Comments
Post a Comment