Post reply

Execute SQL Task

  • February 8, 2014 at 2:13 am

    #282258

    Hi All ,

    I am getting error in Sql execute task with below error

    [Execute SQL Task] Error: Executing the query "DECLARE @returnvalue int

    EXEC @returnvalue =..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    i am try to execute SP with parameter as nvarchar(max) data type.

    In parameter mapping i used as parameter size=2097152

    below is the query i am trying to execute

    Declare @returnvalue int

    EXEC @returnvalue =sp_xyz ?

    select 'returnvalue' = @returnvalue

    Please help me out....

    Thanks in Advance...

    Cheer's
    Rajesh

  • February 11, 2014 at 6:37 am

    #1687876

    This may point you in the right direction:

    First the stored procedure I created for testing this which accepts @MyParameter as nvarchar(max) type.

    IF OBJECT_ID('MH_TEST') IS NOT NULL

    DROP PROCEDURE dbo.MH_TEST

    GO

    CREATE PROCEDURE MH_TEST

    @MyParameter NVARCHAR(MAX)

    AS

    BEGIN

    DECLARE @SomeValue BIGINT = 12345

    SELECT @SomeValue AS ThisWasReturned

    RETURN

    END

    GO

    The procedure runs and the final part simply selects @SomeValue

    Now for the package

    Set the ExecuteSqlTask to return a single value result set. (Package_3.jpg)

    Set up the ExecuteSqlTask the execute the stored procedure but use an expression to build the command to take into account the variable within the SSIS package you want to pass to the stored procedure (Package_1.jpg)

    Set the result set (Package_2.jpg)

    Run the package and you should see a message pop up showing what was returned from the SP (Package_4.jpg)

    Any help?

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

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