Executing stored procedure from SSIS

  • VSSGeorge

    SSCrazy Eights

    Points: 8104

    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

  • Phil Parkin

    SSC Guru

    Points: 243844

    Looks close. You should add the correct param size for the NVARCHAR column.

    Quarter has an 'r' in it.

    I do not understand your question (b). If you plan to call the proc without including certain params, you need specify the NULL defaults in the proc definition. If you will always include the params in your proc calls, don't specify defaults.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Phil Parkin

    SSC Guru

    Points: 243844

    Also, the 0, 1, 2 ... parameter naming is valid for OLEDB connections. If you are using an ADO connection, you should include the actual parameter names.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply