When I run stored proc on ssms, it runs fine. Problem occurs when I try to execute the store proc in SSIS package.

Stored Proc is being used in Data Flow Task > OLE DB Source. I selected SQL query as source.

Here is the store proc code sample:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

alter procedure dbo.usp_GetBill_Info

@StartDate datetime,

@EndDate datetime,

AS

BEGIN

SET NOCOUNT ON;

create table #ID

(

ID nvarchar(100),

TID nvarchar(100)

)

insert into #EID (ID, TEID)

select a1.ID, a2.TID

FROM CustID AS a1

inner JOIN TransactionHeader AS a2

where PV1.DischargeDtTm between @StartDate AND @EndDate2

create NONCLUSTERED INDEX #idx_ID on #TID(ID) include (TID)

-- Start Report

select

#ID.ID

,ID.TID

,Account_Status = V1.AccountStatus

,Quantity=f.[Quantity]

,Amount = cast(fin.[Amount] as decimal(38, 2))

,Service_Date = fin.[ServiceDate]

,fin.BatchDate

from #ID

INNER JOIN Account as v1

on #ID.ID = v1.ID

INNER JOIN financeData AS fin

on #ID.TID = fin.TID

drop table #EID

end

go