I have a execute sql task which insert data into a table, but before that I need to make sure no data is available for particular date. we have a table where we are defining date for which date we want to data, and we are fetching that particular date using ssis variable 'Date'. today I was trying to load data dor '2015-05-10'. But it gave me error like '[Execute SQL Task] Error: "Value does not fall within the expected range.'. Below are the my execute sql task query:
delete from STG_Shipped_Invoiced
where Transaction_Date=?
INSERT INTO STG_Shipped_Invoiced (div_Code, inv_inv_id, tot_Net_Amt,
trans_date, trans_type, Created_date, Transaction_Date)
select inv.DIV_CODE as Div_Code, inv.INV_ID as inv_inv_id, inv.TOT_NET_AMT as Tot_Net_Amt,
inv.TRANS_DATE as trans_date, inv.TRANS_TYPE as trans_type, Getdate() as Created_date,
CONVERT(DATE,inv.TRANS_DATE) as Transaction_Date
from inv inv
where CONVERT(DATE,inv.TRANS_DATE)=? and inv.TRANS_TYPE<>'CM'
And inv.INV_ID in (select distinct INV_ID from inv_shp inv_ship where WHSE_CODE='WPP')
order by 1 asc, 2 asc
SELECT @@ROWCOUNT as NoOfRecords
Here I have defined a variable 'Date', where we are passing the date. Am i doing anything wrong with the delete query?