I have a below procedure which uses 3 temp tables and finally insert into a table. I am using below sp as source (execute sql task) and loading into csv files. When i run this sp from SSMS it runs within 10 seconds, but when I Put into SSIS package it runs for 30 mins and keep on running. Could any of you optimize this SP for SSIS package?
create proc [etl].[payment_report]
@start_date datetime,
@end_date datetime
as
begin
truncate table extract.payment
select
a.bb_acct_id, a.customer_id,
pay.order_id Payment_Order_Id,
aoi.ord_prod_id Ord_Prod_Id,
pd.name, pd.quick_cd
into
#IntialOrders
from
(select *
from stg.payment (NOLOCK)
where order_id is not null) pay
inner join
stg.account a (NOLOCK) on pay.acct_id = a.acct_id
inner join
stg.acct_order ao (NOLOCK) on ao.order_id = pay.order_id
inner join
stg.acct_order_item aoi (NOLOCK) on aoi.order_id = ao.order_id
inner join
stg.ordered_product op (NOLOCK) on op.ord_prod_id = aoi.ord_prod_id
left join
stg.product p (NOLOCK) on p.prod_id = op.prod_id
left join
stg.product_def pd (NOLOCK) on pd.prod_def_id = p.prod_def_id
where
pay.received_date >= @start_date
and pay.received_date < @end_date
and pay.payment_id not in (select payment_id
from stg.op_renewal_history)
create clustered index Idx_Payment_Order_Id on #IntialOrders(Payment_Order_Id)
select
pay.payment_id, gwr.response
into
#gateway_response
from
stg.payment pay (NOLOCK)
inner join
stg.cc_transactions cct (NOLOCK) on cct.payment_id = pay.payment_id
inner join
stg.gateway_response gwr (NOLOCK) on cct.c_c_trans_id = gwr.cc_trans_id
where
pay.posting_status_id = 3
and pay.received_date >= @start_date
and pay.received_date < @end_date
and pay.received_date > '2015-04-15 00:00:00'
select
distinct a.bb_acct_id SP_ACCOUNT_ID
,a.customer_id CP_CUSTOMER_ID
,bu.description BUSINESS_UNIT
,pay.payment_id PAYMENT_ID
,pay.payment_amt AMOUNT
,'SEK' CURRENCY
,Substring(convert(char(19),pay.received_date,126), 1, (len(convert(char(19),pay.received_date,126))-0))+'Z' PAYMENT_DATE
,pt.display_name PAYMENT_METHOD
,post.description PAYMENT_STATUS
,pay.retries PAYMENT_RETRIES
,case when (pay.for_bill_id is not null or orh.op_id is not null) then 'Renewal' else 'OneTime' end as PAYMENT_TYPE
,case when pay.posting_status_id =3 then gwr.response else '' end as FAILURE_REASON
,case when op.ord_prod_id is null then ino.name else pd.name end SUBSCRIPTION_NAME
,case when op.ord_prod_id is null then ino.quick_cd else pd.quick_cd end SKU
,case when op.ord_prod_id is null then ino.Ord_Prod_Id else op.ord_prod_id end ORD_PROD_ID
,convert(char(19),GETDATE(),126)+'Z' EXPORT_TIME
,case when (pay.posting_status_id =3 and pay.retries = 3 ) then 'T' else 'F' end FAILED_EXTENDED_RETRY
into #payment
from stg.payment pay (NOLOCK)
left join #IntialOrders ino (NOLOCK) on
ino.Payment_Order_Id=pay.order_id
left join stg.account a (NOLOCK) on
a.acct_id = pay.acct_id
left join stg.business_unit bu (NOLOCK) on
a.bu_id=bu.bu_id
left join stg.payment_method pm (NOLOCK) on
pm.pay_method_id=pay.payment_method_id
left join stg.payment_type pt (NOLOCK) on
pt.id=pm.type_id
left join stg.posting_status post (NOLOCK) on
post.id=pay.posting_status_id
left join stg.op_renewal_history orh (NOLOCK) on
orh.payment_id = pay.payment_id
left join stg.ordered_product op (NOLOCK) on
op.ord_prod_id = orh.op_id
left join stg.product p (NOLOCK) on
p.prod_id = op.prod_id
left join stg.product_def pd (NOLOCK) on
pd.prod_def_id = p.prod_def_id
left join #gateway_reponse gwr (NOLOCK) on
gwr.payment_id = pay.payment_id
where
a.bu_id=1
and (a.acct_role_id is null or a.acct_role_id !=4)
and (pay.posting_status_id=2 or (pay.posting_status_id =3
and pay.retries = 3
and op.acct_status_id=3))
and pay.received_date >=@start_date
and pay.received_date < @end_date
and pay.received_date > '2015-04-15 00:00:00'
insert into extract.payment
(
SP_ACCOUNT_ID
,CP_CUSTOMER_ID
,BUSINESS_UNIT
,PAYMENT_ID
,AMOUNT
,CURRENCY
,PAYMENT_DATE
,PAYMENT_METHOD
,PAYMENT_STATUS
,PAYMENT_RETRIES
,PAYMENT_TYPE
,FAILURE_REASON
,SUBSCRIPTION_NAME
,SKU,ORD_PROD_ID
,EXPORT_TIME
,FAILED_EXTENDED_RETRY
)
select
cast(p.SP_ACCOUNT_ID as varchar(50)) SP_ACCOUNT_ID
,cast(p.CP_CUSTOMER_ID as varchar(50)) CP_CUSTOMER_ID
,cast(p.BUSINESS_UNIT as varchar(500)) BUSINESS_UNIT
,cast( p.PAYMENT_ID as varchar(50)) PAYMENT_ID, p.AMOUNT
,cast( p.CURRENCY as varchar(50)) CURRENCY
,cast( p.PAYMENT_DATE as varchar(50)) PAYMENT_DATE
,cast( p.PAYMENT_METHOD as varchar(50)) PAYMENT_METHOD
,cast(p.PAYMENT_STATUS as varchar(50)) PAYMENT_STATUS
,cast( p.PAYMENT_RETRIES as varchar(50)) PAYMENT_RETRIES
,cast(p.PAYMENT_TYPE as varchar(50)) PAYMENT_TYPE
,cast( p.FAILURE_REASON as varchar(4000)) FAILURE_REASON
,cast(p.SUBSCRIPTION_NAME as varchar(500)) SUBSCRIPTION_NAME
,cast(p.SKU as varchar(50)) SKU
,cast(p.ORD_PROD_ID as varchar(50)) ORD_PROD_ID
,cast( p.EXPORT_TIME as varchar(50)) EXPORT_TIME
,p.FAILED_EXTENDED_RETRY
from #payment p
order by p.PAYMENT_DATE
Drop table #IntialOrders
Drop table #gateway_reponse
Drop table #payment
END
