stored procedure taking long time when it runs through SSIS

Question

I have a below procedure which uses 3 temp tables and loaded 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_reponse

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