Stored proc in Execute SQL Task not persisting changes to database

Question

I have an Execute SQL Task using an Update statement that I would like to change to a stored procedure.

My stored procedure works fine in SSMS, however when I try and use the stored proc in the Execute SQL Task, the component doesn't fail, however it doesn't persist the Updated changes to the Database.

The Connection managers are solid as the Update statement works and it wouldn't even be able to call the stored proc if the Connection manager was wrong anyway.

I have tried deleting and recreating the component but that did not help. It almost seems like the query is being executed, succeeds, and then the changes are rolled back.

Answer 1

Add a breakpoint before the task executes so that you can inspect the variables before it executes. Once your package stops at the breakpoint, use the Watch window to see what the current values are that will be used in your stored proc. I suspect that you will find they are different than expected. If you have never used Watch, this should get you going:

Watch variables during SSIS Debug

Answer 2

I would start by checking the pstream variable type. Your stored proc expects an INT and you are passing a NUMERIC. I am guessing that the parcing doesn't work as expected. Try to have your SSIS variable 'USER::Pstream' as Int32 and the Sql task parameter type as LONG

i have tried the following and works for me.

CREATE TABLE dbo.TestTable(
    [BucketName] NVARCHAR(250),
    [DBName] NVARCHAR(250),
    [Pstream] INT
)

GO 

CREATE PROCEDURE dbo.UpdateBucket
    @BucketName NVARCHAR(250),
    @DbName NVARCHAR(250),
    @PStream INT
AS
BEGIN

    UPDATE dbo.TestTable
    SET BucketName = @BucketName
    where DBName = @DbName and Pstream = @PStream

END

GO

INSERT INTO dbo.TestTable VALUES ('A bucket', 'A database', 100)