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 +”’
