SSIS passing DATE datatype into Exe SQL Task via Store Procedure

This is a really simple question:

There is one store procedure created on production COS US, now I want to call it via SSIS.

DECLARE @retailerID INT
DECLARE @date      DATE
DECLARE @column_empty  BIT
DECLARE @noData       BIT
DECLARE @cnt        INT
DECLARE @uploads    INT 
DECLARE @exists     BIT
DECLARE @sp_status     INT

SET @retailerid = 27
SET @date  = CONVERT(DATE,'20130903')

  
EXEC @sp_status = dbo.PopulateCostoUS_TransactionFee  @retailerID  ,  @date , @column_empty  OUTPUT, @cnt  OUTPUT, @uploads  OUTPUT,@noData     OUTPUT

SELECT    @retailerID as retailer    ,DATEADD(day,-1,@date)  as [date], @column_empty as column_empty, @cnt as cnt, @uploads as uploads ,@noData as NoDataFlag

 

HOW CAN I DO IT? It’s causing a lot of pains now…????

 

This BLOG saved my life

 

http://blog.hoegaerden.be/2011/06/11/passing-a-datetime-parameter-into-the-execute-sql-task/

 

 

Basically, if the package variable is using DATETIME type , then, in the Parameter mapping section, the data type should be DATE which  consists of year, month, day, hour, minute, seconds, and fractional seconds. The fractional seconds have a fixed scale of 7 digits.

if the SP is using INT and the pacakge variables that’s going to be passed to this parameter is INT32 , then parameter mapping should be LONG, instead of short

In expression, if you want to show the value of the variable, use double quote “, if you want to put the value under the single quote , use +’” @val +”’