SQLTeam.com | Weblogs | Forums

SQL Server Stored Procedure execution from SSIS task

I am new to SSIS. I am calling a stored procedure from Execute SQL Task with the syntax:

EXEC usp_AddUpdate ?, ?, ? OUTPUT, ? OUTPUT

The stored procedure has 4 parameters, i.e. 2 input & 2 output parameters
@QuaterID bigint=null
,@UserName nvarchar(256)=null
,@Success bit output
,@OutMessage varchar(512) output

In SSIS, I created new variables in Parameter Mapping tab of the Execute SQL Task. It is as follows:

Variable name Direction Data Type Param. Name Param. Size

User::QuaterID Input NUMERIC 0 -1

User::UserName Input NVARCHAR 1 -1

User::Success Output BYTE 2 -1

User::OutMessage Output LONG 3 -1

a) I just need to know whether it is correct & will it work there?

b) Also, should I specify anywhere the Default value NULL of stored procedure Input Parameters.

Thanks in advance

Maybe this will help.

I want to pass the following query resultset value as input parameter value for @QuaterID.

SELECT QuaterID FROM Nodes WHERE NodeID = (SELECT MAX(NodeID) FROM Nodes)

For this, I have created a Execute SQL Task with the above query. This task will execute ahead of the task which execute the stored procedure.

How to set the value return by the query as the value of the input parameter @QuaterID.?